What is causing MySQL error 1071 (key too long) in this case?

Question

I have a following table encoded in utf8mb4:

CREATE TABLE IF NOT EXISTS `account` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `username` VARCHAR(254) NOT NULL,
  `password` CHAR(60) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_table1_customer_idx` (`customer_id` ASC),
  UNIQUE INDEX `unique_account` (`customer_id` ASC, `username` ASC),
  CONSTRAINT `fk_table1_customer`
    FOREIGN KEY (`customer_id`)
    REFERENCES `customer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
ROW_FORMAT = DYNAMIC;

I need to add a boolean column to it, so here's what I do:

ALTER TABLE `account` 
    ADD COLUMN `is_customer_admin`
        INT(4) NOT NULL DEFAULT 0
        AFTER `customer_id`;

I also tried to add specifically a BOOLEAN column instead of INT(4)

However, I get the error:

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

It's the first time I encounter an error like that. I did find some questions about that specific error, however, I could not apply it to my situation myself.

From this question I understand that username might be too long, but then I don't understand how did they create that table in the first place. My query does not touch that field.

It's not my database, so pardon the lack of detail... I'll replace the table structure with the query they used to create it — JaffParker, Nov 15 '18 at 19:21
Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) — DanB, Nov 15 '18 at 19:32
@DanB it possibly is a duplicate, but I'm in desperate need of an answer to this specific issue. I did research that question, but I still don't understand it — JaffParker, Nov 15 '18 at 19:32
`VARCHAR(254)` - do you really expect 254 characters for `username` ? — Madhur Bhaiya, Nov 15 '18 at 19:35
@MadhurBhaiya unfortunate I didn't create that DB, I'm not sure that people who did actually knew what they're doing — JaffParker, Nov 15 '18 at 19:37
@MadhurBhaiya yes. I just tried to alter `username`, reduce size to 120, but the issue still persists — JaffParker, Nov 15 '18 at 19:39
This unique index seems suspicious: `(`customer_id` ASC, `username` ASC)`. This means you can have duplicate customer ID with different usernames, or duplicate username with different customer ID, you just can't have a duplicate combination of the two. — Barmar, Nov 15 '18 at 19:39
Here are _five_ solutions to the 767 problem: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes . The `INT` is irrelevant. But you need to change the 254 _or_ the version _or_ the `CHARSET` _or_ ... — Rick James, Nov 15 '18 at 21:56

score 0 · Accepted Answer · answered Nov 15 '18 at 19:42

In older MySQL / MariaDB versions, maximum key (index) length allowed is 767 bytes only. From Docs:

By default, the index key prefix length limit is 767 bytes. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

Now, the problem in your case is increased further, as you are using utf8mb4 character set instead. This means 4 bytes (not 3 bytes) per character.

`username` VARCHAR(254)

This would mean 254 * 4 = 1016 bytes, which is definitely going above the limit. You will need to reduce the maximum characters specified for username column.

Also, the following constraint does not make much sense, as customer_id is already a Primary key.

UNIQUE INDEX `unique_account` (`customer_id` ASC, `username` ASC)

You can get rid of it as well.

Thanks. I didn't think that a column that was already there would block further alterations... — JaffParker, Nov 15 '18 at 19:47

What is causing MySQL error 1071 (key too long) in this case?

1 Answers1