Not able to increase size of a particular column in table of mysql. Error Code: 1071. Specified key was too long; max key length is 3072 bytes

Question

My MySQL Version is 8.0.34 CE (64 bits)

And my table is as below

CREATE TABLE `t_report_subscriptions` (
  `subscr_id` int NOT NULL AUTO_INCREMENT,
  `report_header_id` varchar(700) NOT NULL,
  `user_id` varchar(45) NOT NULL,
  `update_type` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `source_system_id` int DEFAULT NULL,
  PRIMARY KEY (`subscr_id`),
  UNIQUE KEY `uq_reportSubsriptions` (`report_header_id`,`user_id`,`source_system_id`),
  KEY `report_id_fk_idx` (`report_header_id`,`source_system_id`),
  CONSTRAINT `fk_report_id` FOREIGN KEY (`report_header_id`, `source_system_id`) REFERENCES `l_report_header_data` (`REPORT_HEADER_ID`, `SOURCE_SYSTEM_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I am trying to increase the length of the 'user_id' column from 45 to 256 as follows

alter table t_report_subscriptions modify column user_id varchar(256) NOT NULL;

And getting the following error

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

I tried dropping all the constraints and index of the table then increase the size of the column and then re-creating the constraints and index, but still it does not work

I have dropped the constraints and index as below

ALTER TABLE t_report_subscriptions drop index `report_id_fk_idx`;
ALTER TABLE t_report_subscriptions drop index `uq_reportSubsriptions`;
ALTER TABLE t_report_subscriptions DROP FOREIGN KEY fk_report_id;

Then increased the length of the 'user_id' column as below

alter table t_report_subscriptions modify column user_id varchar(256) NOT NULL;

Then re-creating the constraints and index as below

ALTER TABLE t_report_subscriptions ADD CONSTRAINT `fk_report_id` FOREIGN KEY (`report_header_id`, `source_system_id`) REFERENCES `l_report_header_data` (`REPORT_HEADER_ID`, `SOURCE_SYSTEM_ID`);
ALTER TABLE t_report_subscriptions add index `report_id_fk_idx` (`report_header_id`,`source_system_id`);

But when i try to re-create the unique constraint as below i get the following error

ALTER TABLE t_report_subscriptions ADD CONSTRAINT `uq_reportSubsriptions` UNIQUE(`report_header_id`,`user_id`,`source_system_id`);

The error is

16:12:43    ALTER TABLE t_report_subscriptions ADD CONSTRAINT `uq_reportSubsriptions` UNIQUE(`report_header_id`,`user_id`,`source_system_id`)   Error Code: 1071. Specified key was too long; max key length is 3072 bytes  0.000 sec

I have referred multiple links

why this error occur #1071 - Specified key was too long; max key length is 1000 bytes?

Mysql ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

MySQL Error #1071 - Specified key was too long; max key length is 767 bytes

But not able to increase the size of the column. Any suggestions/help

What is the maximum length for report_header_id, can you reduce it to something smaller than varchar(700) ? — Lennart - Slava Ukraini, Aug 24 '23 at 10:59
@Lennart-SlavaUkraini The length of report_header_id cannot be reduced. Is there any other option — Avinash Reddy, Aug 24 '23 at 11:03
Then you need to replace one of the columns in the unique index. You may consider adding a generated column with some kind of hashed value for either user_id or report_header_id. There is of course always a risk of collision so you have to choose the hashed value visely — Lennart - Slava Ukraini, Aug 24 '23 at 11:09

score 0 · Answer 1 · answered Aug 24 '23 at 14:05

0

Not questioning if you actually need an id with 700 chars, you probably don't need to use unicode for it, as it reserves 4 bytes per character in the key.

If you can use e.g. latin1, you only need 1 byte per character, so you can have e.g. a varchar(2812), varchar(256) and int (4 bytes) for a total of 3072 bytes.

So try e.g.

CREATE TABLE `t_report_subscriptions` (
  `subscr_id` int NOT NULL AUTO_INCREMENT,
  `report_header_id` varchar(700) COLLATE latin1_swedish_ci NOT NULL,
  `user_id` varchar(256) COLLATE latin1_swedish_ci NOT NULL,
  `update_type` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `source_system_id` int DEFAULT NULL,
  PRIMARY KEY (`subscr_id`),
  UNIQUE KEY `uq_reportSubsriptions` (`report_header_id`,`user_id`,`source_system_id`),
  KEY `report_id_fk_idx` (`report_header_id`,`source_system_id`),
  CONSTRAINT `fk_report_id` FOREIGN KEY (`report_header_id`, `source_system_id`) REFERENCES `l_report_header_data` (`REPORT_HEADER_ID`, `SOURCE_SYSTEM_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

If it is used in a foreign key, you have to change it in the other table too.

If you indeed need unicode, utf8mb3_general_ci with 3 bytes per character will probably still be enough for what you want to store in an id.

answered Aug 24 '23 at 14:05

Solarflare

10,721
2
18
35

This is how the report_header_id looks like D872E5D5-4855-4350-A873-4594121D8E1A . We dont generate the report_header_id, we get it from a source system and we store it in our database. – Avinash Reddy Aug 24 '23 at 16:20
1

That looks like a standard uuid. You said in a comment that you need varchar(700) and cannot make is shorter. A uuid (or rather the string representation) will always have 36 characters, you don't need more (so just making it varchar(36) would be an even simpler solution than changing from unicode. Nevertheless, uuids don't require unicode columns, so e.g. latin1 would work too) – Solarflare Aug 24 '23 at 17:10
Just a question to understand. As per my table the charset is CHARSET=utf8mb4. Is that each character takes 4 bytes. Meaning my `report_header_id` is varchar(700) and it takes 700*4 = 2800 bytes. Please help me understand – Avinash Reddy Aug 25 '23 at 11:24
With a varchar, it *can* take up to 2800 bytes (if you put 700 bytes with full 4 byte unicodes in it). MySQL only stores the characters used (e.g. if the string has length 36, it only uses bytes for those 36), and also unicode is encoded in a way that the first 128 most common characters (e.g. english letters and numbers) use only 1 byte, so your uuid will (on disk and memory) not need 4 bytes per char. Still, the key needs to be able to store 4*700 bytes cause you *could* store such a maximum unicode string. And MySQL just has a limitation on that maximum key length. – Solarflare Aug 25 '23 at 12:17

Not able to increase size of a particular column in table of mysql. Error Code: 1071. Specified key was too long; max key length is 3072 bytes

1 Answers1