Error with OTRS after upgrading Ubuntu from 18 to 20 (Admin access lost)

[msksharma]

We are using OTRS 6.0.2x running on Ubuntu 18. 2 weeks back we upgraded Ubuntu 18 to 20 since it was reaching EOL and the upgrade went on well. After 2 weeks we realize that users who had admin access have lost access to Admin part in GUI.

Upon research we found that Ubuntu 20 uses MySQL8 and there is an issue with groups which is a reserved word which overlaps with table groups in OTRS.

Could you please suggest on how to resolve this issue? Can we migrate our DB from MySQL to MariaDB?

If it is possible, what is the script we need to use?

Any inputs/support would be apperciated.

Regards,
Krishna M S

[wurzel]

Hi,

restore your backup of your database in mariadb.

Flo

[msksharma]

Hi,
Does that mean i can use mysqldump to take backup of DB and then restore on MariaDB?

Regards,
Krishna M S

[wurzel]

Hi,

I am no database expert but with upgrading to mysql 8.0 you might have destroyed your database or made inconsistency data.
You should restore your initial backup and restore this in a compatible database version.

regards
Flo

[msksharma]

Hi,

Unfortunately the upgrade was done 2 weeks and we have a backup of the DB. However we would lose 2 weeks of ticket data.

Is there any other alternative to resolve this?

According to this,

https://github.com/znuny/Znuny/issues/41

There are 2 alrernatives

Is any of them possible?

[root]

Hi,

As far as I can see, your only options are changing the database or upgrading to a version that is not end-of-life and supports MySQL 8. Since upgrading, Ubuntu was done because it's end-of-life, I strongly consider the last option. And regarding your data, I recommend consulting a pro to check if nothing else is broken besides the table 'groups'.

- Roy

[msksharma]

Whom should we contact for Professional support?

[root]

Whom should we contact for Professional support?

There are plenty of, maybe one of companies listed at https://www.otter-alliance.de/en/mitglieder.html

[msksharma]

I am getting this error after migrating DB to mariadb

otrs@otrs:/opt/otrs/bin$ ./otrs.Console.pl Admin::User::Add --user-name otrsadmin --first-name OTRS --last-name Admin --email-address it@toradex.com --password abcd123 --group "admin"
Error: Group 'admin' does not exist.

But i can query admin group by issuing

select * from groups works

However i still do not have admin access

Please help us out

[root]

Hi,

Replace your database with Mysql < 8.0 or MariaDB.

- Roy

[msksharma]

I replaced my Db with Maria DB, But the problem still persists.

Do i have to run some scripts after the DB is migrated?

[root]

Hi,

Delete the cache with the otrs user:

bin/otrs.Console.pl Maint::Cache::Delete

- Roy