MySQL Update Error when moving from 4.0.18 to 5.0.12

[ln4r4raxvnn]

Last week I upgraded the OTRS from 3.3.15 to 4.0.18 according to the tutorial on github.io. This went smoothly.
Now I am trying to move from 4.018 to 5.0.12 on the CentOS server using the tarball to update.
Moving was smooth until the step to update the database.
First Errors like so:

Code: Select all

[root@localhost otrs]# cat scripts/DBUpdate-to-5.mysql.sql | mysql -p -f -u root otrs
Enter password: 
ERROR 1050 (42S01) at line 9: Table 'scheduler_task' already exists
ERROR 1050 (42S01) at line 28: Table 'scheduler_future_task' already exists
ERROR 1050 (42S01) at line 47: Table 'scheduler_recurrent_task' already exists
ERROR 1050 (42S01) at line 72: Table 'notification_event_message' already exists
ERROR 1050 (42S01) at line 87: Table 'cloud_service_config' already exists
ERROR 1005 (HY000) at line 101: Can't create table 'otrs.#sql-74d_60' (errno: 121)
ERROR 1005 (HY000) at line 102: Can't create table 'otrs.#sql-74d_60' (errno: 121)
ERROR 1005 (HY000) at line 103: Can't create table 'otrs.#sql-74d_60' (errno: 121)
ERROR 1005 (HY000) at line 104: Can't create table 'otrs.#sql-74d_60' (errno: 121)
[root@localhost otrs]#  

Considering the tables exist, I continued to encounter the next error on step seven.

Code: Select all

[root@localhost otrs]#  su -c "bin/otrs.Console.pl Maint::Database::Check" -s /bin/bash otrs
Trying to connect to database 'DBI:mysql:database=otrs;host=localhost;' with user 'otrs'...
Connection successful.

[root@localhost otrs]#  su -c "scripts/DBUpdate-to-5.pl" -s /bin/bash otrs
....
Step 7 of 17: Migrate notifications to event based notifications...[Mon Aug 29 08:53:53 2016] DBUpdate-to-5.pl: DBD::mysql::db do failed: Duplicate entry '1-de' for key 'notification_event_message_notification_id_language' at /opt/otrs/Kernel/System/DB.pm line 449.
ERROR: OTRS-DBUpdate-to-5.pl-10 Perl: 5.16.3 OS: linux Time: Mon Aug 29 08:53:53 2016

 Message: Duplicate entry '1-de' for key 'notification_event_message_notification_id_language', SQL: '
                INSERT INTO notification_event_message
                    (notification_id, subject, text, content_type, language)
                VALUES (?, ?, ?, ?, ?)'

 Traceback (5018): 
   Module: Kernel::System::NotificationEvent::NotificationAdd Line: 366
   Module: main::_MigrateNotifications Line: 1109
   Module: scripts/DBUpdate-to-5.pl Line: 162

error.

[Mon Aug 29 08:53:53 2016] DBUpdate-to-5.pl: Died at scripts/DBUpdate-to-5.pl line 167.

I look at the db table and find the de language existing. Though we only use the english language.

Can these errors be related? Can we make a workaround to fulfill the database update to 5?

[ln4r4raxvnn]

I hacked a way to complete the update.
It seems to me that the problem was SQL trying to update the notifications tables by making a temporary record using "Old " on the name as a key. But for some reason that one for the _de was getting flagged as existing.

The hack I did to the update script was logs the message, but does not exiting on existing key, and uses update on insert if existing key. Below are the functions in the script that I altered to complete the upgrade.

Note: This whole problem may or may not be reproducible, but I had to keep going to finish the upgrade no matter what.

Code: Select all

nano ./Kernel/System/NotifictionEvent.pm

.....

    if (%Check) {
        $Kernel::OM->Get('Kernel::System::Log')->Log(
            Priority => 'error',
            Message  => "Can't add notification '$Param{Name}', notification already exists!",
        );
#  Line 263 comment out        return;
    }


    # insert data into db
    return if !$DBObject->Do(
        SQL => '
            INSERT INTO notification_event
                (name, valid_id, comments, create_time, create_by, change_time, change_by)
            VALUES (?, ?, ?, current_timestamp, ?, current_timestamp, ?)
# Add line 315 to SQL updating duplicate record
            ON DUPLICATE KEY UPDATE name = VALUES(name), valid_id = VALUES(valid_id), comments = VALUES(comments), change_by = VALUES(change_by)',
        Bind => [

.....

    # insert notification event message data
    for my $Language ( sort keys %{ $Param{Message} } ) {

        my %Message = %{ $Param{Message}->{$Language} };

        return if !$DBObject->Do(
            SQL => '
                INSERT INTO notification_event_message
                    (notification_id, subject, text, content_type, language)
                VALUES (?, ?, ?, ?, ?)  
# Add line 371 to update on existing key
                ON DUPLICATE KEY UPDATE subject = VALUES(subject), text = VALUES(text), content_type = VALUES(content_type), language = VALUES(language)',
            Bind => [
                \$ID,
                \$Message{Subject},
                \$Message{Body},
                \$Message{ContentType},
                \$Language,