Upgrade from 3.0 to 3.1 - DB Migration Fails

[scottbean]

I am attempting to run the DBUpgrade-to-3.1.pl script and get as far as step 5. After it finishes going through our rather large database (100000+) everything after step 5 fails. I saw another post where someone had the same error as me, and they mentioned increasing their mysql timeout. Anyone know what and where exactly needs to be changed so I can finish the upgrade?

Here is the error I get:

Migrated ticket 102000 of 102282 (with Free fields data).
Migrated ticket 102100 of 102282 (with Free fields data).
Migrated ticket 102200 of 102282 (with Free fields data).

Migrated 102282 tickets of 102282 (with Free fields data).
done.

Step 6 of 24: Migrate article free fields to dynamic fields...
DBD::mysql::db do failed: MySQL server has gone away at /opt/otrs-3.1.11/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-90 Perl: 5.8.8 OS: linux Time: Wed Jan 30 10:42:53 2013

Message: MySQL server has gone away, SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (30197):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 109

DBD::mysql::db do failed: MySQL server has gone away at /opt/otrs-3.1.11/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-90 Perl: 5.8.8 OS: linux Time: Wed Jan 30 10:42:53 2013

Message: MySQL server has gone away, SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (30197):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 109

Free fields were deleted, migration is already done!
done.

Step 7 of 24: Verify if ticket data was successfully migrated...
DBD::mysql::db do failed: MySQL server has gone away at /opt/otrs-3.1.11/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-90 Perl: 5.8.8 OS: linux Time: Wed Jan 30 10:42:53 2013

Message: MySQL server has gone away, SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (30197):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 117

DBD::mysql::db do failed: MySQL server has gone away at /opt/otrs-3.1.11/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-90 Perl: 5.8.8 OS: linux Time: Wed Jan 30 10:42:53 2013

Message: MySQL server has gone away, SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (30197):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 117

Free fields were deleted, migration is already done!
done.

DBD::mysql::db do failed: MySQL server has gone away at /opt/otrs-3.1.11/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-90 Perl: 5.8.8 OS: linux Time: Wed Jan 30 10:42:53 2013

Message: MySQL server has gone away, SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (30197):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 125

DBD::mysql::db do failed: MySQL server has gone away at /opt/otrs-3.1.11/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-90 Perl: 5.8.8 OS: linux Time: Wed Jan 30 10:42:53 2013

Message: MySQL server has gone away, SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (30197):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 125

Step 8 of 24: Verify if article data was successfully migrated... Free fields were deleted, migration is already done!
done.

DBD::mysql::st execute failed: MySQL server has gone away at /opt/otrs-3.1.11/Kernel/System/DB.pm line 618.
ERROR: OTRS-DBUpdate-to-3.1-90 Perl: 5.8.8 OS: linux Time: Wed Jan 30 10:42:53 2013

Message: MySQL server has gone away, SQL: 'SELECT id, name, field_order FROM dynamic_field ORDER BY field_order, id'

Traceback (30197):
Module: Kernel::System::DynamicField::DynamicFieldList (v1.56.2.2) Line: 695
Module: main::_MigrateFreeFieldsConfiguration (v1.85) Line: 1095
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 139

Can't use an undefined value as a HASH reference at ./DBUpdate-to-3.1.pl line 1100.
Step 9 of 24: Migrate free fields configuration...

[scottbean]

**UPDATE**

I was able to get our test machine upgraded by commenting out steps 1 through 5 in the DBUpgrade-to-3.1.pl script after it times out. Then I had to comment out lines 6 through 8 after the script timed out again (after step 8 ). Then the script would finish. I don't think anything was corrupted, but when I migrate production I really don't want to comment out code. I have tried increasing every timeout value I can think of both in Apache on the OTRS box and in MySQL on the DB machine. Does anyone have an idea for what timeout is causing the error in the post?

[CSL]

I'm going to have to go through this myself soon, so it's good to hear from someone migrating a large DB from 3.0 to 3.1. That error 'MySQL has gone away' is annoyingly vague, but usually results from an insufficient number of connections. However, in my experience, MySQL statements with incorrect syntax can sometimes result in this error as well (like when an agent puts a syntax error in an email address field, the 'gone away' message can result).

I think this could be a syntax error issue, possibly if you have customized something to do with your freetext fields, or if you store data within them that could cause issues for the migration commands that will include them.

I would enable the mysql general log (if you haven't already) and run this migration again. When you start getting errors, Ctrl-C the script after a few seconds, and have a look at what commands it is running in the general log. Hopefully these should show you what is going wrong. If they look OK, try copy-pasting them into MySQL workbench or another tool (or running them manually on the command line) and see if you get the same error message.

[ferrosti]

Why not running mysql Workbench simultaneously and having a look at the process list?
I´d say your mysql timeout is too low. What happens when you fire up the mentioned SQL straight from mysql workspace?

[scottbean]

The process completes on the DB server. The application server seems to get tired of waiting for a response though, so not sure what exactly is going on.

[ferrosti]

There is a parameter for Client Timeouts in mySQL.
http://dev.mysql.com/doc/refman/5.0/en/ ... it_timeout