Migration fails with error "Specified key was too long; max key length is 1000 bytes"

Refers to:

  • Plesk 11.5 for Linux
  • Plesk 12.0 for Linux
  • Plesk 9.x and below for Linux

Created:

2016-11-16 13:20:45 UTC

Modified:

2016-12-21 20:30:29 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Migration fails with error "Specified key was too long; max key length is 1000 bytes"

Symptoms

Migration fails with the following error shown in the Plesk interface:

 Failed deployment of database Mambo-1676625571 (domain example.com)

Execution of /usr/local/psa/admin/sbin/cid_unpacker /usr/local/psa/PMM/var/2009-01-29-22.55.48.294135 failed with return code 1.
Stdin is
db

Can not deploy site application Mambo-4.5.2.3-4 on domain example.com

And the following error is shown in the migration log:

 [06:41:04|INFO:27701:p.log] ++ user1676625571 database user
[06:41:04|INFO:27701:p.log] Executing utility: /usr/local/psa/admin/sbin/cid_unpacker /usr/local/psa/PMM/var/2009-01-29-22.55.48.294135
[06:41:04|INFO:27701:p.log] ++ Mambo-1676625571 database
[06:41:04|INFO:27701:p.log] FailedDatabaseDeployment: Failed deployment of database Mambo-1676625571 (domain example.com) [./databases.cpp:187]
Inner exception:
ExecCmd::ExFailed: Execution of /usr/local/psa/admin/sbin/cid_unpacker /usr/local/psa/PMM/var/2009-01-29-22.55.48.294135 failed with return code 1.
Stdin is
db^@Mambo-1676625571.mysql:gzipped^@Mambo-1676625571^@mysql^@admin^@set12^@localhost^@3306
Stderr is
Runtime error during db: (1, '', 'ERROR 1071 (42000) at line 323: Specified key was too long; max key length is 1000 bytes\

') One more attempt Runtime error during db: (1, '', 'ERROR 1071 (42000) at line 323: Specified key was too long; max key length is 1000 bytes\') One more attempt Runtime error during db: (1, '', 'ERROR 1071 (42000) at line 323: Specified key was too long; max key length is 1000 bytes\') One more attempt Runtime error during db: (1, '', 'ERROR 1071 (42000) at line 323: Specified key was too long; max key length is 1000 bytes\')

Cause

The problem is caused by the error

Specified key was too long; max key length is 1000 bytes.

There is a table in the database Mambo-1676625571 that defines the keys from long values. According to this bug report, MySQL does not allow unique keys longer than 1000 bytes: http://bugs.mysql.com/bug.php?id=4541

By design, Plesk CLI utilities specify the utf8 charset on creation of the Mambo database Mambo-1676625571 , even if the server's charset is latin1 .

Resolution

To restore databases that fail to restore dbname, change CHARACTER SET to "latin1" in the database structure.

There are two ways to restore the database:

  1. Import the database manually.

  2. Log in to MySQL and drop the failed database (it will be there with partially restored tables).

  3. Recreate the database with the following:
    mysql> CREATE DATABASE dbname CHARACTER SET = latin1;

Reupload the dump of the database:

    ~# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin dbname < dbname.sql
  1. Run the migration in import mode and change CHARACTER in the migration dump before restoration.

Add the line {DEFAULT CHARACTER SET latin1;} to the tables creation script in the dump file.

Have more questions? Submit a request
Please sign in to leave a comment.