Mariadb ERROR: ERROR 1709 (HY000): Index column size Too large. The Maximum column size is 767 bytes

Asked

Viewed 798 times

7

When I run this code on Mariadb, it gives me this error when trying to create the toy table:

ERROR 1709 (HY000): Index column size Too large. The Maximum column size is 767 bytes.

I don’t know how to fix it, Mariadb has already been installed on my system Parrot Sec (A distro based on Debian). Can anyone help me?

CREATE TABLE IF NOT EXISTS categoria (
  categoria_id INT(11) unsigned NOT NULL AUTO_INCREMENT,
  categoria_nome VARCHAR(80) NOT NULL,
  PRIMARY KEY (categoria_id),
  UNIQUE INDEX categoria_nome_UNIQUE (categoria_nome ASC));


CREATE TABLE IF NOT EXISTS brinquedo (
  brinquedo_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  brinquedo_descricao VARCHAR(200) NULL DEFAULT NULL,
  brinquedo_imagem_url VARCHAR(200) NOT NULL,
  brinquedo_preco DECIMAL(9,2) NOT NULL,
  brinquedo_detalhes VARCHAR(200) NULL DEFAULT NULL,
  brinquedo_categoria_id INT(11) NOT NULL,
  brinquedo_marca VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (brinquedo_id),
  UNIQUE INDEX brinquedo_imagem_url_UNIQUE (brinquedo_imagem_url ASC),
  CONSTRAINT fk_brinquedo_categoria
    FOREIGN KEY (brinquedo_categoria_id)
    REFERENCES categoria (categoria_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);
mysql sql mariadb

  • Field 'long' as varchar are not good candidates for indices. To be an Index it should not exceed a certain value (see the documentation for the number of Blocks) to be useful. The problem seems to be brinquedo_imagem_url. If I am not mistaken in Mysql the limit is 120. Suggestion remove the Dice from that column or if possible decrease the number of characters.

  • I did that, and now he has error in creating the Foreign: brinquedo_tag VARCHAR(45) NULL DEFAULT NULL, PRIMARY KEY (brinquedo_id), CONSTRAINT fk_brinquedo_category FOREIGN KEY (brinquedo_categoria_id) CATEGORY (categoria_id) ON DELETE CASCADE ON UPDATE CASCADE); ERROR 1005 (HY000): Can’t create table brinquedos.brinquedo (Rrno: 150 "Foreign key Constraint is incorrectly Formed")

2 answers

6


If you use utf8mb4, each character of a field CHAR or VARCHAR reserve 4 bytes, so your 200 characters may need up to 800 bytes.

If you really need this index, or you need to narrow it down a bit...

 brinquedo_imagem_url VARCHAR(180) NOT NULL
 -- 180 x 4 = 720 --

...or you can keep the 200, but define a charset different for him:

  brinquedo_imagem_url VARCHAR(200) CHARACTER SET binary ...

Leaving something like this:

CREATE TABLE IF NOT EXISTS brinquedo (
  brinquedo_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  brinquedo_descricao VARCHAR(200) NULL DEFAULT NULL,
  brinquedo_imagem_url VARCHAR(200) CHARACTER SET binary NOT NULL,
  brinquedo_preco DECIMAL(9,2) NOT NULL,
  brinquedo_detalhes VARCHAR(200) NULL DEFAULT NULL,
  brinquedo_categoria_id INT(11) NOT NULL,
  brinquedo_marca VARCHAR(45) NULL DEFAULT ...
  ...

Note that to decide the best charset you have to analyze for what else it is used besides the UNIQUE.

Follow the list of Mariadb and Mysql Charsets:

https://mariadb.com/kb/en/mariadb/supported-character-sets-and-collations/

https://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

  • beauty. I’m clearing my comments here, minus the manual link. When opening the new question, remember to mention which engine you are using in Mariadb (and remember, which one you used in Mysql)

  • Thank God, that’s right!

  • I’m glad it worked out, and that the person in the other question, the UNSIGNED one, was watching. It was a boring little detail to notice. It was a very accurate shot :) - Too bad someone gave -1 here too, but it is part. The important thing is that it solved for you and others also approved.

  • Yes, in Mysql it worked without unsigned, I think it assumed as default!

  • Beauty. Qq thing, leave a comment or question.

  • This SQL is part of a job that I wanted to remember, there’s something that I can’t even remember where I got from but, anyway, it worked! Veleu.

Show 1 more comment

2

Exchange the INT(11) and the INT(10) for SERIAL. SERIAL is an alias("nickname") for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

The problem in your code is the primary key type declaration, so this solves it. Remember not to duplicate the other attributes that the SERIAL already fills in.

Source for consultation

  • I made the changes and the error remains the same!

Browser other questions tagged mysql sql mariadb

You are not signed in. Login or sign up in order to post.