I'm attempting to set up a new remote website for production, and push my development database to it. I've already rsync'ed the files, and set up a new settings.php for the remote site. However, when running drush sql-sync @local @remote, I'm getting a query failed error. Manually importing the dump on the remote yields the following error:

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

Manually changing the dump to use CHARSET utf8 on the column where the key is too long fixes the issue. However, this happens in a lot of tables. Besides, not having the same charset on both sites is probably asking for issues.

According to this answer, this is the issue with an InnoDB table. However, this is the show create table for one of the tables affected, on the source site:

cache_entity_registration_type | CREATE TABLE `cache_entity_registration_type` (
  `cid` varchar(255) NOT NULL DEFAULT '' COMMENT 'Primary Key: Unique cache ID.',
  `data` longblob COMMENT 'A collection of data to cache.',
  `expire` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry should expire, or 0 fo
r never.',
  `created` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry was created.',
  `serialized` smallint(6) NOT NULL DEFAULT '0' COMMENT 'A flag to indicate whether content is serialized (1) or not (0)
  PRIMARY KEY (`cid`),
  KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='Cache table used to store registration_type entity r

As you can see, it's also an InnoDB table, with a primary key that's 255 characters long, with an utf8mb4 encoding. That's also longer than the maximum key length of 767 bytes of InnoDB.

What can I change on either the server or client to allow me to use sql-sync to sync between the two sites?

1 Answer 1


I remembered I changed my existing Drupal installation to support utf8mb4 on my tables, as described here: https://www.drupal.org/project/utf8mb4_convert.

Basically, the answer is that I have to change the settings on my target server as follows:

In order to allow for large indexes, MySQL must be set up with the following my.cnf settings:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.