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 ecords.'
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?