How to disable max key length error in mysql - Stack Overflow

I use a Google Cloud SQL (MySQL) as my production database. To replicate this database for testing in docker i use the mysql:8 image.

I noticed that one of my migration scripts succedes on the cloud db but failes during tests. The following script causes an error:

CREATE TABLE testTable
(
    name varchar(1000)
);
CREATE INDEX idx_device_designs_name ON testTable (name);

The error: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071)...

I understand the reason for the error, but as our standard production db does not produce it I'm looking for the setting that disables this check.

EDIT1:

I compared create queries on production and in the docker container

production

CREATE TABLE `testTable` (
  `name` varchar(1000) COLLATE utf8mb3_unicode_ci NOT NULL,
KEY `idx_device_designs_name` (`name`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

docker

CREATE TABLE `testTable` (
  `name` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

production had the index applied in the past (before a mysql update, so 5.7). beyond that it looks fundamentally the same to me.

I use a Google Cloud SQL (MySQL) as my production database. To replicate this database for testing in docker i use the mysql:8 image.

I noticed that one of my migration scripts succedes on the cloud db but failes during tests. The following script causes an error:

CREATE TABLE testTable
(
    name varchar(1000)
);
CREATE INDEX idx_device_designs_name ON testTable (name);

The error: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071)...

I understand the reason for the error, but as our standard production db does not produce it I'm looking for the setting that disables this check.

EDIT1:

I compared create queries on production and in the docker container

production

CREATE TABLE `testTable` (
  `name` varchar(1000) COLLATE utf8mb3_unicode_ci NOT NULL,
KEY `idx_device_designs_name` (`name`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

docker

CREATE TABLE `testTable` (
  `name` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

production had the index applied in the past (before a mysql update, so 5.7). beyond that it looks fundamentally the same to me.

• mysql

Share Follow edited Jan 30 at 21:24 Barmar 784k5757 gold badges548548 silver badges659659 bronze badges asked Jan 30 at 15:14 LauresLaures 5,4891111 gold badges5656 silver badges8484 bronze badges 8

• any idea why cloud-sql is ok with this query then? This is an old migration script so i would like to keep it as is – Laures Commented Jan 30 at 15:47
• what does select version(); show in production? if your production db uses the same version, this is likely a difference in default character set. what does show create table yourtablename for a table with an index like this in production show? – ysth Commented Jan 30 at 15:48
• i added the result of show create table to the question. – Laures Commented Jan 30 at 16:14
• 2 did you try doing the create in docker with the CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci and the index? that should work, as long as you don't let it use your docker default utf8mb4 instead – ysth Commented Jan 30 at 16:39
• 1 The script that causes the error does not specify character set for the name column, so default database character set is applied. If that character set is utf8mb4, then you get the error. Specify ut8mb3 as the character set for that column and it will be fine. – Shadow Commented Jan 30 at 21:20

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

To give this Question a valid answer:

There is a difference in the Column definition between the mysql:8 docker container and the mysql 8 google cloud database. Because the production DB was created back wenn it was mysql 5.7 the charset/collation of the database is utf8mb3. In mysql 8 the default charset/collation is utf8mb4.

As a result the varchar(1000) results in different key sizes for its index between these environments and the mb4 size is too large.

Solution: for our tests we start the container with utf8mb3 as its default, thereby emulating the state of production and avoiding the error.