MigrateFromOTRS: migrate database using the commands mysqldump and mysql

[bschmalhofer]

In #534 an migration strategy was implemented that relied on moving table between database schemas using "ALTER TABLiE ... RENAME TO ...". This seemed to work in the considered case. However the considered cased is the well behaved cased where there are just about no differences between the source and the target schema. In real migrations there are problems when some of the attributes types have diverged. This divergence causes that some foreign key relationships can't be recreated during the migration process.

See also #575.

An example:

In a regular OTRS 6 or OTOBO 10 Installation:

CREATE TABLE auto_response ( 
  id smallint(6) NOT NULL AUTO_INCREMENT,

At the customers site:

CREATE TABLE auto_response ( 
  id INTEGER NOT NULL AUTO_INCREMENT, 

Leads to the error:
errno: 150 "Foreign key constraint is incorrectly formed"

Probably depending on the order in which the tables are migrated these error can occur when renaming the table or when re-adding foreign key constraints.

The suspicion is that these kind of errors might also occur with other migration strategies.

This problem was discussed by @StefanRother-OTOBO and @bschmalhofer . The conclusion was to abolish the 'RENAME TO' solution and to provide support for a database migration strategy based on mysqldump, sed, and mysql.

TODO:

• Make the OTOBODatabaseMigrate step in migration.pl optional
• Provide instructions and enhance scripts/backup.pl for a direct database migration
• consider charset changed to utf8mb4
• avoid double replacements of the the charset
• rename the database to the DatabaseName from Kernel/Config.pm
• consider skipped tables, maybe checking foreign key relationship of these tables
• consider renamed tables
• consider InnodB max key size in MySQL 5.6 or earlier: 191 = int( 767 / 4 )
• don't consider DirectBlob conversion
• don't check for concurrent migrations
• don't limit to Docker based installations
• no thorough check of the migrated schema
• no support for restarting interrupted migrations
• do not reset auto increment fields, as this is not neccessary for MySQL
• allow to skip Database copy in migration.pl

[bschmalhofer]

@StefanRother-OTOBO
I have addrf the generation of the SQL scripts to scripts/backup.pl . The changes is available in the pull request #645.
Using backup.pl means that currently it is expected that an OTOBO installation is available and that the OTRS database is reachable from there. This is purely for convenience, as standalone script could do the job as well.

For testing I have called something like that:

scripts/backup.pl -t migratefromotrs --db-name homebernharddeve --db-host=127.0.0.1 --db-user homebernharddeve --db-password=homebernharddeve
docker exec -i otobo_db_1 mysql -u root -potobo_root < homebernharddeve_schema_varchar_191.sql
docker exec -i otobo_db_1 mysql -u root -potobo_root < homebernharddeve_data.sql
docker exec -it otobo_db_1 mysql -u root -potobo_root
docker exec -i otobo_db_1 mysql -u root -potobo_root < homebernharddeve_fixup.sql

More testing is required. I only did a sanity check, whether the SQL script can be executed.

Documentation is missing too.

[bschmalhofer]

It's not obvious whether the problem with foreign key checks are avoided with this approach. Testing is needed.
Perhaps all tables need to be dropped first, before they are recreated.

[bschmalhofer]

If I understand https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks correctly, then disabling foreign key check does not prevent the 'error 150' from existing foreign keys. A workaround might be to drop all relevant tables beforehand. For that i added yet another SQL script.

Not that in case of errors, show engine innodb status can give useful info.

[wollmers]

IMHO max-allowed-packet could default to 64M in backup.pl.

[bschmalhofer]

@StefanRother-OTOBO The '=' is not needed. I have adapted the installation docs accordingly.

I propose to merge the current state of affairs into the rel-10_0 branch and proceed in the issues #648 and #649.

[svenoe]

@bschmalhofer - is my assumption correct, that the skipped tables from your check list in principle are already handled with ecbc969 (or maybe even earlier; currently line 428 and 429 of backup.pl), and the point is just not checked, because of possible foreign keys stuff? I would ignore this then, for now. Those tables, from what I see, are standing pretty much on their own or only hold temporary data in the first place (logs, process ids,...). I think we can tackle this, if we start to encounter problems, and up until now I'm aware of none, concerning this.

[bschmalhofer]

@svenoe Exactly. The tables that should not be migrated are already taken into account by backup.pl --backup-type=migratefromotrs. But I didn't investigate table by table whether foreign key issues could arise. IMHO the best approach is to not worry about that now, but focus on doing some test migration with real live databases.

[svenoe]

Stefan will incorporate some instructions into the manual. Reading the db name out of the config would just result in instructions easier to copy paste, as I understand it. Nice to have, but this can be incorporated somewhen else, too.