Solution for the database migration problem.

[M:ke]

Hello.

Every time I tried to migrate a domain or customer I got the error message.

Failed deployment of database ...

Symptom: If you try to insert the transfered dump manually, you will get the message.

ERROR 1071 (42000) at line 295: Specified key was too long; max key length is 1000 bytes

Reason 1: Source- and target servers using different database charsets. Older Plesk databases (7.x, mysql 4) using latin1 by default, newer Plesk versions (8.x, mysql 5) using utf8. But utf8 needs three bytes per character, instead one.
Reason 2: Plesk creates databases in utf8 and ignores the settings in /etc/my.cnf
Solution 1: Clear the database, set the charset to the original and insert the dump again.

ALTER DATABASE `database name` CHARACTER SET latin1 COLLATE latin1_general_ci;

Solution 2: Use my perl script for migration after failed deployment. It helps you to dump, send and insert more than one databases.

Example output:

./migrate_db.pl targethost.tyclipso.net /etc/ssh/ssh_host_dsa_key test foobar foobar_old test-example
Starting migration process for 4 databases.
Password for SQL user@localhost#
Password for SQL [email protected]#
Creating dumpfile of test@localhost
Cleaning up test.sql
Zipping test.sql
Sending test.sql.gz to targethost.tyclipso.net
Changing charset of [email protected] to latin1
Inserting data into [email protected]
Creating dumpfile of foobar@localhost
Cleaning up foobar.sql
Zipping foobar.sql
Sending foobar.sql.gz to targethost.tyclipso.net
Changing charset of [email protected] to latin1
Inserting data into [email protected]
Creating dumpfile of foobar_old@localhost
Cleaning up foobar_old.sql
Zipping foobar_old.sql
Sending foobar_old.sql.gz to targethost.tyclipso.net
Changing charset of [email protected] to latin1
Inserting data into [email protected]
Creating dumpfile of test-example@localhost
Cleaning up test-example.sql
Zipping test-example.sql
Sending test-example.sql.gz to targethost.tyclipso.net
Changing charset of [email protected] to latin1
Inserting data into [email protected]

Notice:
- You have to setup ssh key authentication on the target server first.
- You have to change the charset in the source code (variable $target_charset)
- You need enought disc space on the target server's user home (~/incoming_databases)

References:
http://bugs.mysql.com/bug.php?id=4541
http://kb.swsoft.com/en/2112
http://forum.swsoft.com/showthread.php?p=201104
http://forum.swsoft.com/showthread.php?t=50006

Download: