MariaDB ERROR: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes

Question

MariaDB ERROR: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes

Navigation

#1 by (6 votes)
#2 by (1 votes)

6

When I run this code in 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 do not know how to fix, MariaDB has already installed Parrot Sec (a Debian-based distro) on my system. Can someone 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

asked by anonymous 30.12.2016 / 14:14

2 answers

1

Change INT(11) and INT(10) by SERIAL . SERIAL is an alias ("alias") for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

The problem with your code is the declaration of the type of the primary key, so this resolves. Remember not to duplicate the other attributes that SERIAL already populates.

Source for consultation

30.12.2016 / 15:15

Does Java accuse wrong memory usage values or is there something wrong with the algorithm? Class C ++ Doubt

score 6 · Accepted Answer

If you use utf8mb4 , each character in a CHAR or VARCHAR field reserves 4 bytes, so its 200 characters may need up to 800 bytes.

If you really need this index, or need to narrow the field a bit ...

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

... or you can keep the 200, but set a different charset for it:

  brinquedo_imagem_url VARCHAR(200) CHARACTER SET binary ...

Getting 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 what else it is used in addition to UNIQUE .

Here's the list of MariaDB and MySQL charsets:

link