OTRS 6.0.28 Database Upgrade fails

[jakecovert]

Hello,

I recently tried to update OTRS, and the database update step failed.

I was wondering If I could ask for advice?

Code: Select all

root@server:/opt/otrs > su - otrs
$ cd /opt/otrs
$ scripts/DBUpdate-to-6.pl

 Migration started ...

 Checking requirements ...

    Requirement check for: Check framework version ...
    Requirement check for: Check required Perl version ...
    Requirement check for: Check required database version ...
    Requirement check for: Check database charset ...
    Requirement check for: Check required Perl modules ...
    Requirement check for: Check if database has been backed up ...

        Did you backup the database? [Y]es/[N]o: y

    Requirement check for: Upgrade database structure ...
    Requirement check for: Migrating time zone configuration ...
    Requirement check for: Update calendar appointment future tasks ...
    Requirement check for: Migrate GenericAgent jobs configuration ...
    Requirement check for: Migrate TicketAppointment rules configuration ...
    Requirement check for: Create entries in new article table ...
    Requirement check for: Migrate ArticleType in ProcessManagement Data ...
    Requirement check for: Migrate ArticleType in PostMaster filters ...

 Executing tasks ...

    Step 1 of 44: Check framework version ...
    Step 2 of 44: Check required Perl version ...
    Step 3 of 44: Check required database version ...
    Step 4 of 44: Check database charset ...
    Step 5 of 44: Check required Perl modules ...
    Step 6 of 44: Check installed CPAN modules for known vulnerabilities ...
    Step 7 of 44: Check if database has been backed up ...
    Step 8 of 44: Upgrade database structure ...
[Fri May  1 13:14:04 2020] DBUpdate-to-6.pl: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups (id)' at line 1 at /opt/otrs/Kernel/System/DB.pm line 470.
ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-10 Perl: 5.30.0 OS: linux Time: Fri May 1 17:14:04 2020

 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups (id)' at line 1, SQL: 'PREPARE FKStatement FROM @FKSQLStatement'

 Traceback (3158783):
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 394
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBArray Line: 342
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::TicketIndexUpdate::Run Line: 64
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::Run Line: 142
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: scripts/DBUpdate-to-6.pl Line: 87

ERROR: OTRS-otrs.Console.pl-Dev::Code::CPANAudit-10 Perl: 5.30.0 OS: linux Time: Fri May 1 17:14:04 2020

 Message: Error during execution of 'PREPARE FKStatement FROM @FKSQLStatement'!

 Traceback (3158783):
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 397
   Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBArray Line: 342
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::TicketIndexUpdate::Run Line: 64
   Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::Run Line: 142
   Module: scripts::DBUpdateTo6::_ExecuteComponent Line: 157
   Module: scripts::DBUpdateTo6::Run Line: 69
   Module: scripts/DBUpdate-to-6.pl Line: 87

 Not possible to complete migration, check previous messages for more information.

$

OTRS: 6.0.28
Linux: Ubuntu 20.04
Mysql: 8.0.19-0ubuntu5 (Ubuntu)
Perl: v5.30.0

[root]

Hi Jake,

Do not use MySQL 8, it's not supported.

- Roy

[jakecovert]

Roy,

Thanks for the quick reply.

While I google how to spin up mysql 5.7 in a container, can I ask what the mysql plans are?

Have a safe day.

[root]

can I ask what the mysql plans are?

Hi Jake,

I have no idea. The MySQL 8 issue was reported https://bugs.otrs.org/show_bug.cgi?id=13866 Don't ask me why is not fixed for version 6.

- Roy

[jakecovert]

No worries. Upgrade complete.

I did get a nice opportunity to learn dockers.

Thanks.

[bschmalhofer]

Hi,

I also ran into all those MySQL 8 issues when trying to install the Beta 4 of Otobo following the instruction at https://doc.otobo.org/manual/installati ... ation.html. I plan to provide patches for these issues in the Otobo repository. I have no idea whether these patches would be applied to ((OTRS)) Community Edition too.

For the authentication plugin I passed in the atrribute 'mysql_ssl => 1' which did the trick.
The table `groups` should be renamed to `groups_table` in order to be compatible with OTRS 8.

Cheers,
Bernhard