MySQL error: specified key was too long

Question

I am using a MySQL database and I am trying to execute a database creation sctipt but I get the following error without indication of which line is concerned:

SQL Error (1071): Specified key was too long; max key length is 3072 bytes

This is my script:

/*==============================================================*/
/* Table: MDEV_ADDRESS                                          */
/*==============================================================*/
create table MDEV_ADDRESS
(
   id_address           bigint not null auto_increment,
   street_1             varchar(2500),
   street_2             varchar(2500),
   zip_code             int,
   city                 varchar(255),
   region               varchar(255),
   country              varchar(255),
   is_delivery_address  bool,
   primary key (id_address)
);

/*==============================================================*/
/* Table: MDEV_BANK_CARD                                        */
/*==============================================================*/
create table MDEV_BANK_CARD
(
   id_bank_card         bigint not null auto_increment,
   id_user              bigint not null,
   card_type            varchar(150) not null,
   card_number          int not null,
   expiration_date      timestamp not null,
   security_code        int not null,
   holder_name          varchar(255),
   primary key (id_bank_card)
);

/*==============================================================*/
/* Table: MDEV_CATEGORY                                         */
/*==============================================================*/
create table MDEV_CATEGORY
(
   id_category          bigint not null auto_increment,
   id_parent_category   bigint,
   name                 varchar(1500) not null,
   description          varchar(2500),
   primary key (id_category)
);

/*==============================================================*/
/* Table: MDEV_CUSTOMER_HAS_ADDRESSES                           */
/*==============================================================*/
create table MDEV_CUSTOMER_HAS_ADDRESSES
(
   id_user              bigint not null,
   id_address           bigint not null,
   primary key (id_user, id_address)
);

/*==============================================================*/
/* Table: MDEV_LOGIN_ATTEMPT                                    */
/*==============================================================*/
create table MDEV_LOGIN_ATTEMPT
(
   id_login_attempt     bigint not null auto_increment,
   id_user              bigint not null,
   attempt_date         timestamp not null,
   ip_address           varchar(150) not null,
   has_logged_in        bool not null,
   log_out_date         timestamp,
   os                   varchar(255),
   browser              varchar(255),
   primary key (id_login_attempt)
);

/*==============================================================*/
/* Table: MDEV_NOTIFICATION                                     */
/*==============================================================*/
create table MDEV_NOTIFICATION
(
   id_notification      bigint not null auto_increment,
   id_user              bigint not null,
   creation_date        timestamp not null,
   message              varchar(2500) not null,
   link_url             varchar(2500),
   is_viewed            bool not null,
   primary key (id_notification)
);

/*==============================================================*/
/* Table: MDEV_ORDER                                            */
/*==============================================================*/
create table MDEV_ORDER
(
   id_order             bigint not null auto_increment,
   id_user              bigint not null,
   id_status            bigint not null,
   reference_number     varchar(255) not null unique,
   order_date           timestamp not null,
   payment_date         timestamp,
   payment_method       varchar(255),
   primary key (id_order)
);

/*==============================================================*/
/* Table: MDEV_ORDER_LINE                                       */
/*==============================================================*/
create table MDEV_ORDER_LINE
(
   id_order_line        bigint not null auto_increment,
   id_product           bigint not null,
   id_order             bigint not null,
   quantity             int not null,
   primary key (id_order_line)
);

/*==============================================================*/
/* Table: MDEV_PRODUCT                                          */
/*==============================================================*/
create table MDEV_PRODUCT
(
   id_product           bigint not null auto_increment,
   id_category          bigint,
   reference_number     varchar(255) not null unique,
   name                 varchar(1500),
   description          varchar(2500),
   image_path           varchar(2500),
   price                float,
   primary key (id_product)
);

/*==============================================================*/
/* Table: MDEV_PRODUCT_HAS_TAGS                                 */
/*==============================================================*/
create table MDEV_PRODUCT_HAS_TAGS
(
   id_product           bigint not null,
   id_tag               bigint not null,
   primary key (id_product, id_tag)
);

/*==============================================================*/
/* Table: MDEV_ROLE                                             */
/*==============================================================*/
create table MDEV_ROLE
(
   id_role              bigint not null auto_increment,
   name                 varchar(1500) not null unique,
   description          varchar(2500),
   primary key (id_role)
);

/*==============================================================*/
/* Table: MDEV_SITE_PREFERENCES                                 */
/*==============================================================*/
create table MDEV_SITE_PREFERENCES
(
   id_site_preferences  bigint not null auto_increment,
   name                 varchar(1500) unique,
   root_images_folder_path varchar(2500),
   image_max_size       int,
   image_max_size_unit  varchar(2),
   image_min_size       int,
   image_min_size_unit  varchar(2),
   root_videos_folder_path varchar(2500),
   video_max_size       int,
   video_max_size_unit  varchar(2),
   video_min_size       int,
   video_min_size_unit  varchar(2),
   primary key (id_site_preferences)
);

/*==============================================================*/
/* Table: MDEV_STATUS                                           */
/*==============================================================*/
create table MDEV_STATUS
(
   id_status            bigint not null auto_increment,
   name                 varchar(1500) not null unique,
   primary key (id_status)
);

/*==============================================================*/
/* Table: MDEV_TAG                                              */
/*==============================================================*/
create table MDEV_TAG
(
   id_tag               bigint not null auto_increment,
   name                 varchar(1500) not null unique,
   primary key (id_tag)
);

/*==============================================================*/
/* Table: MDEV_USER                                             */
/*==============================================================*/
create table MDEV_USER
(
   id_user              bigint not null auto_increment,
   id_status            bigint not null,
   discriminator        varchar(50) not null,
   u_email              varchar(255) not null unique,
   u_password           varchar(255) not null,
   u_username           varchar(255) unique,
   u_first_name         varchar(255) not null,
   u_last_name          varchar(255) not null,
   u_birth_date         timestamp,
   u_gender             varchar(150),
   u_registration_date  timestamp not null,
   u_confirmation_date  timestamp,
   u_expiration_date    timestamp,
   u_locking_date       timestamp,
   u_disabling_date     timestamp,
   u_credentials_expiration_date timestamp,
   u_deletion_date      timestamp,
   u_locking_reason     varchar(5000),
   u_disabling_reason   varchar(5000),
   u_deletion_reason    varchar(5000),
   u_used_language      varchar(150),
   u_security_question  varchar(255),
   u_security_answer    varchar(255),
   u_code               varchar(255) not null unique,
   c_phone              int,
   e_job                varchar(255),
   e_cnss_number        varchar(255),
   primary key (id_user)
);

/*==============================================================*/
/* Table: MDEV_USER_HAS_ROLES                                   */
/*==============================================================*/
create table MDEV_USER_HAS_ROLES
(
   id_role              bigint not null,
   id_user              bigint not null,
   primary key (id_role, id_user)
);

alter table MDEV_BANK_CARD add constraint FK_MDEV_CUSTOMER_HAS_BANK_CARDS         foreign key (id_user)
  references MDEV_USER (id_user) on delete cascade on update cascade;

alter table MDEV_CATEGORY add constraint     FK_MDEV_CATEGORY_HAS_SUB_CATEGORIES foreign key (id_parent_category)
  references MDEV_CATEGORY (id_category) on delete cascade on update cascade;

alter table MDEV_CUSTOMER_HAS_ADDRESSES add constraint FK_MDEV_CUSTOMER_HAS_ADDRESSES foreign key (id_user)
  references MDEV_USER (id_user) on delete cascade on update cascade;

alter table MDEV_CUSTOMER_HAS_ADDRESSES add constraint FK_MDEV_CUSTOMER_HAS_ADDRESSES2 foreign key (id_address)
  references MDEV_ADDRESS (id_address) on delete cascade on update cascade;

alter table MDEV_LOGIN_ATTEMPT add constraint FK_MDEV_USER_HAS_LOGIN_ATTEMPTS foreign key (id_user)
  references MDEV_USER (id_user) on delete cascade on update cascade;

alter table MDEV_NOTIFICATION add constraint FK_MDEV_CUSTOMER_HAS_NOTIFICATIONS foreign key (id_user)
  references MDEV_USER (id_user) on delete cascade on update cascade;

alter table MDEV_ORDER add constraint FK_MDEV_CUSTOMER_HAS_ORDERS foreign key (id_user)
  references MDEV_USER (id_user) on delete cascade on update cascade;

alter table MDEV_ORDER add constraint FK_MDEV_ORDER_HAS_STATUS foreign key (id_status)
  references MDEV_STATUS (id_status) on delete cascade on update cascade;

alter table MDEV_ORDER_LINE add constraint FK_MDEV_ORDER_HAS_ORDER_LINES foreign key (id_order)
  references MDEV_ORDER (id_order) on delete cascade on update cascade;

alter table MDEV_ORDER_LINE add constraint FK_MDEV_ORDER_LINE_HAS_PRODUCT foreign key (id_product)
  references MDEV_PRODUCT (id_product) on delete cascade on update cascade;

alter table MDEV_PRODUCT add constraint FK_MDEV_CATEGORY_HAS_PRODUCTS foreign key (id_category)
  references MDEV_CATEGORY (id_category) on delete cascade on update cascade;

alter table MDEV_PRODUCT_HAS_TAGS add constraint FK_MDEV_PRODUCT_HAS_TAGS foreign key (id_product)
  references MDEV_PRODUCT (id_product) on delete cascade on update cascade;

alter table MDEV_PRODUCT_HAS_TAGS add constraint FK_MDEV_PRODUCT_HAS_TAGS2 foreign key (id_tag)
  references MDEV_TAG (id_tag) on delete cascade on update cascade;

alter table MDEV_USER add constraint FK_MDEV_USER_HAS_STATUS foreign key (id_status)
  references MDEV_STATUS (id_status) on delete cascade on update cascade;

alter table MDEV_USER_HAS_ROLES add constraint FK_MDEV_USER_HAS_ROLES foreign key (id_role)
  references MDEV_ROLE (id_role) on delete cascade on update cascade;

alter table MDEV_USER_HAS_ROLES add constraint FK_MDEV_USER_HAS_ROLES2 foreign key (id_user)
  references MDEV_USER (id_user) on delete cascade on update cascade;

I am executing this script using HeidiSQL and apparently the executions throws the error when it reaches the creation of the table:

    /*==============================================================*/
/* Table: MDEV_ROLE                                             */
/*==============================================================*/
create table MDEV_ROLE
(
   id_role              bigint not null auto_increment,
   name                 varchar(1500) not null unique,
   description          varchar(2500),
   primary key (id_role)
);

As far as I know the only indexes that I am creating are the primary keys and they are bigint of only 8 bytes long.

If somebody can help me here it would be great? Thanks in advance.

Answer 1

You didn't mention version of MySQL. I'm presuming you are using InnoDB and MySQL 5.7.

You can find this snippet:

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. 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.

It happens because you have unique name which will create an index which is subject of the aforementioned constraints.

More here: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Answer 2

Reduce the length of unique key that you are using in your table creation to 767 or 766, it will work well

Answer 3

name varchar(1500) not null unique,

You have a unique key on a varchar which is too long.

As you have multibyte characters, it takes more bytes here.