Myujth

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.

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.