DBUpdate-to-6.pl fails on step 7 of 42

[vai0l0]

I'm testing the upgrade and migratinon of a 5.0.16 to 6.016

I?ve installed a new ubuntu 18.04.02, installed a fresh 6.0.16 and tested fine the aplication running.

I've dumped the old DB ~5Gb and double checked the conversion to utf8
cat otrs.backup.sql | replace CHARSET=latin1 CHARSET=utf8 > otrs.utf.sql
deleted the fresh otrs database and created an empy one
recovered the otrs.utf.sql into the empty database

when I try to DBUpdate-to-6.pl I get a few orphaned records I promptly delete and then an error creating the new sysconfig tables

Step 6 of 42: Check if database has been backed up ...
Step 7 of 42: Upgrade database structure ...

- Add table for dynamic field object names and add an index to speed up searching dynamic field text values
- Add new sysconfig tables
[Fri Mar 8 15:32:53 2019] DBUpdate-to-6.pl: DBD::mysql::db do failed: Cannot add foreign key constraint at /opt/otrs/Kernel/System/DB.pm line 469.
ERROR: OTRS-otrs.Console.pl-Maint::Database::Check-50 Perl: 5.26.1 OS: linux Time: Fri Mar 8 15:32:54 2019

Message: Cannot add foreign key constraint, SQL: 'EXECUTE FKStatement'

Traceback (12324):
Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 394
Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBArray Line: 342
Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::NewSysconfigTables::Run Line: 208
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-Maint::Database::Check-50 Perl: 5.26.1 OS: linux Time: Fri Mar 8 15:32:54 2019

Message: Error during execution of 'EXECUTE FKStatement'!

Traceback (12324):
Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBString Line: 397
Module: scripts::DBUpdateTo6::Base::ExecuteXMLDBArray Line: 342
Module: scripts::DBUpdateTo6::UpgradeDatabaseStructure::NewSysconfigTables::Run Line: 208
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.



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

Any Idea?

[root]

Hi,

You're database tables does not support foreign keys, that happens if they are MyISAM and not InnoDB. Migrate the tables.

- Roy

[vai0l0]

Thanks Roy,
You're right, I've tried to migrate all tables but all fields "create_time" and "change_time" across multiple tables have invalid default values (00.00.00 etc).

I'm changing all defaults to CURRENT_TIMESTAMP

Is there any way to automate this process or I'll have to do it again once I migrate the db in production.

[root]

Hi,

How did you tried to convert the tables? There shouldn't be default value involved.

- Roy

[vai0l0]

ALTER TABLE standard_template_attachment ENGINE = InnoDB;
result is
ALTER TABLE standard_template_attachment ENGINE = InnoDB; failed : Invalid default value for 'change_time'

as you can see

errore.JPG

I need to change the default for change_time
then migration runs fine

[root]

Hi,

I recommend check the OTRS folder scripts/databasse/{otrs-schema.mysql.sql,otrs-schema-post.mysql.sql} of your current system to fix then database before upgrading. There you'll find the proper DDL.

- Roy

[crythias]

also mariadb may not let you have two fields with current time as datetimestamp
https://mariadb.com/kb/en/library/now/

Before MariaDB 10.0.1, it was only possible for a single TIMESTAMP column per table to contain the CURRENT_TIMESTAMP as its default.