Percona MySQL 5.6 databases ignoring key length at constrait creation We have a 3 servers running Percona MySQL 5.6 in a Master/Master configuration that are all reporting errors on key length, either too long or not specified. The structure of the databases is all generated from a mysqldump on existing standalone MySQL 5.6 databases that do not report these errors.

Key too long error:

2015-04-13 11:00:29 26692 [ERROR] Slave SQL: Error 'Specified key was too long; max key length is 767 bytes' on query. Default database: 'dataBaseName'. Query: 'alter table UserGroup add constraint UK_f6x7vpmw83jptft2toy0wsfk6 unique (groupName)', Error_code: 1071


Table structure:

CREATE TABLE `UserGroup` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `groupName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `groupType` int(11) NOT NULL,
 `clientAccountSettings_id` bigint(20) DEFAULT NULL,
 `timestamp4support` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `groupName` (`groupName`(191)),
 KEY `FK8A5BE1545E3F3DC` (`clientAccountSettings_id`),
 CONSTRAINT `FK8A5BE1545E3F3DC` FOREIGN KEY (`clientAccountSettings_id`) REFERENCES `ClientAccountSettings` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Although the column used in the unique constraint reported in the error is 255 chars long using utf8mb4 (1020 bytes) we are only trying to use 191 chars (764 bytes) which works on the standalone system but not on the Percona system.

Unspecified key length error:

2015-04-13 10:56:25 26692 [ERROR] Slave SQL: Error 'BLOB/TEXT column 'name' used in key specification without a key length' on query. Default database: 'dataBaseName'. Query: 'alter table ISP add constraint UK_tfe1hfqbgydnfe2siovm1i80k unique (name)', Error_code: 1170


Table structure:

CREATE TABLE `ISP` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `abuseEmail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
 `timestamp4support` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=465 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Again, as you can see we are trying to specify the index length (working in standalone MySQL) but it is getting ignored. 

In order to try and get around the key too long error, I have turned on innodb_large_prefix but restarting the first instance caused MySQL to remove all data and resync all 2.7TB again, this is due to all the replication errors, of which there are quite a few, all the same as above. I still have to apply this to the other 2 servers.

Is there anything that could change the behaviour so it utilises the specified key length? At the moment this is only a test service so I can make changes but with the way things stand it will take days to get the changes across the cluster.

Does anybody know why the service ignores the key length parameter? Is this a bug?
By default, an index key for a single-column index can be up to 767
  bytes


–MySQL Reference Manual

The size of your index is:

191 (characters) * 4 (utf8mb4 max size per character) + 4 (length) + 1 (NULL) = 769 


The calculation may not be 100% exact, I am not fully sure about the length requirements, but I will assume it is not a bug.

The reason why it may be working initially could be because you may be using utf8, not utf8mb4 on the original system (which is 3 bytes in size per character instead of 4) or you had enabled the variable innodb_large_prefix.

In any case, I would recommend you to fix it enabling that very same variable, which will allow you prefixes of up to 3072 bytes (it will be the default in newer versions).

Edit: ALTER TABLEs on a production PXC are usually done in RSU mode.