mysql - specified - Error: la clave especificada era demasiado larga; La longitud máxima de la clave es de 1000 bytes

phpmyadmin#1071-declaracion de clave demasiado larga. la maxima longitud de clave es 767 (9)

Doc oficial 5.6

Límites en las tablas InnoDB

La longitud de la clave máxima interna de InnoDB es 3500 bytes, pero MySQL restringe esto a 3072 bytes. Este límite se aplica a la longitud de la clave de índice combinada en un índice de varias columnas.

http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

El motor de almacenamiento MyISAM

La longitud máxima de la clave es de 1000 bytes. Esto también se puede cambiar cambiando la fuente y recompilando. Para el caso de una clave de más de 250 bytes, se utiliza un tamaño de bloque de clave mayor que el predeterminado de 1024 bytes.

http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

Sin embargo, su tabla se declara como InnoDB. Así que no sé qué pensar.

También hay una pista al final de este viejo error.

Si necesita esto, debería mirar MySQL 5.5 y la opción innodb_large_prefix que está disponible desde 5.5.14 (julio de 2011) en adelante porque probablemente hace lo que está buscando:
"Habilite esta opción para permitir prefijos de clave de índice de más de 767 bytes (hasta 3072 bytes), para tablas InnoDB que usan los formatos de fila DYNAMIC y COMPRESSED. (Crear dichas tablas también requiere los valores de opción innodb_file_format = barracuda and innodb_file_per_table = true.) Consulte la Sección 13.3.15, "Límites en las tablas de InnoDB" para conocer los máximos relevantes asociados con los prefijos de clave de índice en varias configuraciones.

Error:

1071 - La clave especificada era demasiado larga; La longitud máxima de la clave es de 1000 bytes

Sé que el error se produce debido a 255x2x3 (3 bytes por carácter)

Esto no sucede en todas las instalaciones. ¿Qué configuración puedo cambiar?

NO_ENGINE_SUBSTITUTION deshabilitado con INNODB no activo, una mala combinación

Hasta MySQL 5.5, el valor predeterminado de sqlmode era una cadena en blanco, lo que significa que NO_ENGINE_SUBSTITUTION sqlmode no se estableció de forma predeterminada

De acuerdo con los documentos de MySql (vea https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_engine_substitution ) este es el significado de sqlmode NO_ENGINE_SUBSTITUTION:

Controle la sustitución automática del motor de almacenamiento predeterminado cuando una declaración como CREATE TABLE o ALTER TABLE especifica un motor de almacenamiento que está deshabilitado o no está compilado.
Debido a que los motores de almacenamiento se pueden conectar en tiempo de ejecución, los motores no disponibles se tratan de la misma manera:
Con NO_ENGINE_SUBSTITUTION desactivado, para CREAR TABLA se usa el motor predeterminado y aparece una advertencia si el motor deseado no está disponible. Para ALTER TABLE, se produce una advertencia y la tabla no se altera.
Con NO_ENGINE_SUBSTITUTION habilitado, se produce un error y la tabla no se crea ni se modifica si el motor deseado no está disponible.

Entonces: si NO_ENGINE_SUBSTITUTION está deshabilitado Y INNODB está APAGADO, MySql cambiará a MYISAM también si especifica INNODB en su declaración CREATE TABLE.
Si la tabla que está creando está bien para MYISAM, solo recibirá una advertencia y se creará la tabla. Ese no es su caso, su declaración de creación incluye un índice que está más allá del límite de 1000 bytes de MYISAM, luego la creación falla con el error 1071 que informa el error de MYISAM. Eso es porque el motor en funcionamiento es MYISAM, no INNODB.

PRUEBA

MySQL versión 5.1.56 comunidad

Caso 1:

Options in my.cnf sql-mode="" default-storage-engine=MYISAM skip-innodb uncommented (without#) Return on execution of your create statement: Error Code: 1071. Specified key was too long; max key length is 1000 bytes Explanation: INNODB is not active, the engine is automatically switched to MYISAM that returns this error as they key is longer than MYISAM 1000 bytes limit. The key length is: 2 fields x 255 char x 3 bytes utf8 encoding + 2 x 1 length byte = 1532 bytes

Caso 2:

Options in my.cnf sql-mode="NO_ENGINE_SUBSTITUTION" default-storage-engine=MYISAM skip-innodb uncommented (without#) Return on execution of your create statement: Error Code: 1286. Unknown table engine ''INNODB'' Explanation: INNODB is not active but the engine substitution is not permitted by sql mode therefore the DB returns an error about the attempt of using a disabled engine.

Caso 3:

Options in my.cnf sql-mode="NO_ENGINE_SUBSTITUTION" default-storage-engine=MYISAM skip-innodb commented (with#) Return on execution of your create statement: Table creation OK! Explanation: INNODB is active (skip-innodb commented) and it is used also if the default engine is MYISAM.

Para reproducir las pruebas, reinicie MySql después de cada cambio en my.cnf.

Ya que MySQL versión 5.6 sqlmode ya no está vacío por defecto y contiene NO_ENGINE_SUBSTITUTION, además, INNODB es el motor predeterminado, por lo que el error es difícil de cumplir.

Otras pruebas

No hay otra forma de reproducir el error:

Error Code: 1071. Specified key was too long; max key length is 1000 bytes

al intentar crear una tabla INNODB se ha encontrado.

En INNODB tienes dos tipos de ERROR 1071:

Error Code: 1071. Specified key was too long; max key length is 767 bytes

esto no tiene nada que ver con innodb_large_prefix ON u OFF, pero solo se relaciona con el tamaño de una sola columna VARCHAR que se usa como índice.

Mysql almacene varchar utf8 con 3 bytes más 1 byte para una longitud de hasta 255 caracteres y 2 después (consulte: http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html ), por lo que si intenta establecer una clave con VARCHAR (256) utf8 obtienes:

256 x 3 + 2 = 770 bytes

y obtiene el error anterior, ya que la longitud máxima de la clave para una sola columna es de 767 bytes para una tabla InnoDB. Un VARCHAR (255) está bien, porque:

255 x 3 + 1 = 766 bytes

Lo probé en cuatro instalaciones de Mysql, versión 5.1.56, 5.5.33, 5.6 y 5.7, y eso está confirmado. No hay problema con su consulta con VARCHAR (255), problema con VARCHAR (256):

Error Code: 1071. Specified key was too long; max key length is 767 bytes

¡Como puede ver, el mensaje es diferente, porque es un mensaje de INNODB y no de MYISAM!

El otro tipo de ERROR 1071 para tablas INNODB es:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

Esto está relacionado con las claves con múltiples columnas. Para que esas claves estén habilitadas, debe activar innodb_large_prefix. De todos modos, si intentas ejecutar algo como esto:

CREATE TABLE `phppos_modules_actions` ( `action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `action_name_key1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `action_name_key2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `sort` INT NOT NULL , PRIMARY KEY ( `action_id` , `module_id`, `action_name_key`, `action_name_key1`, `action_name_key2` ) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Con una clave de 5 columnas VARCHAR (255) utf8, es decir, 3830 bytes, se encontrará con:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

Hipótesis exótica

Durante la búsqueda de la causa, formulé y probé diferentes hipótesis bastante extrañas:

Formato de fila

Probado REDUNDANTE, COMPACTO, COMPRIMIDO, DINÁMICO: no tiene impacto en la creación de tablas con su declaración.

Formato de archivo

Antílope probado y Barracuda: no hay impacto en la creación de tablas con su declaración.

MySql construido

MySQL probado de 32 bits y 64 bits: no tiene impacto en la creación de tablas con su declaración.

Otros fallos similares

Aquí puedes encontrar el mismo error en la misma situación:

https://www.drupal.org/node/2466287

Probé esa declaración en las 3 situaciones de prueba enumeradas en PRUEBA y reproducía exactamente el mismo comportamiento que el suyo, por lo que puedo decir que el problema es el mismo. En ese caso, cambiaron a otra base de datos, pero el problema es la combinación de ajustes, no la versión de la base de datos.

Referencias

Un muy buen artículo de indexación con INNODB se da aquí:

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

ADVERTENCIA: la desactivación de INNODB mediante la desactivación de skip-innodb en my.cnf después de la creación de la tabla INNODB con un índice superior a 1000 no permitirá el inicio del servicio MySql

Saludos

Ahora no sé cuánta flexibilidad tiene, pero aquí hay algunas opciones:

Actualice la versión de MySQL a la última versión (probé el código en MySQL 5.5.25 y no obtuve errores).
Cambio de utf8 a latin1.
Reduzca el tamaño de los campos que conforman la clave principal. Cree un campo de clave principal de auto_increment separado para reemplazar la clave existente. Cree un índice separado en el primer campo action_id (pero no el segundo campo)

Aparte de esas opciones, está bastante atascado, ya que no hay ninguna configuración que pueda cambiar en MySQL que permita una clave de índice superior a 1000 bytes.

Como dice, la longitud total de su índice es demasiado larga.

La respuesta corta es que, de todos modos, no debería estar indexando columnas VARCHAR tan largas, porque el índice será muy voluminoso e ineficiente.

La mejor práctica es usar índices de prefijo para que solo se indexe una subcadena izquierda de los datos. La mayoría de sus datos serán mucho más cortos que 255 caracteres de todos modos.

767 bytes es la limitación de prefijo indicada para las tablas InnoDB , que tiene 1.000 bytes de longitud para las tablas MyISAM .

De acuerdo con la respuesta a este problema, puede obtener la clave para aplicar especificando un subconjunto de la columna en lugar de la cantidad total. Medios Puede declarar una longitud de prefijo por columna al definir el índice.

Por ejemplo:

KEY `key_name` (`action_id`(50),`module_id`(50))

Aquí está el ejemplo para explicarlo más claramente: creé una tabla e inserté algunos datos en ella.

Pero, ¿cuál es la mejor longitud de prefijo para una columna dada? Aquí hay un método para descubrir:

SELECT ROUND(SUM(LENGTH(`action_id`)<10)*100/COUNT(*),2) AS pct_length_10, ROUND(SUM(LENGTH(`action_id`)<20)*100/COUNT(*),2) AS pct_length_20, ROUND(SUM(LENGTH(`action_id`)<50)*100/COUNT(*),2) AS pct_length_50, ROUND(SUM(LENGTH(`action_id`)<100)*100/COUNT(*),2) AS pct_length_100 FROM `phppos_modules_actions`; +---------------+---------------+---------------+----------------+ | pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 | +---------------+---------------+---------------+----------------+ | 42.86 | 80.20 | 100 | 100 | +---------------+---------------+---------------+----------------+

Esto le indica que el 80% de sus cadenas tienen menos de 20 caracteres y todas sus cadenas tienen menos de 50 caracteres. Así que no hay necesidad de indexar más de un prefijo de 50, y ciertamente no es necesario indexar la longitud total de 255 caracteres.

Modifique los datos necesarios para obtener la clave, pero me pregunto si valdría la pena revisar su modelo de datos con respecto a esta entidad para ver si hay mejoras que le permitan implementar las reglas comerciales previstas sin llegar a la limitación de MySQL.

Puede encontrar alguna forma alternativa de configuración en innodb en http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

En MySQL 5.6.3+ con algunas limitaciones (necesita ROW_FORMAT=DYNAMIC , innodb_file_format=BARRACUDA e innodb_file_per_table=true ) puede habilitar innodb_large_prefix para un límite de longitud de clave de 3072 bytes.

En mi caso, MySQL se inició sin soporte de InnoDB. Y durante la importación de la copia de seguridad de la base de datos: se intentó crear tablas MyISAM.

Al mismo tiempo, no se mostraron errores en la consola durante el reinicio de MySQL.
Solo cuando se verificó el archivo de registro de MySQL - encontré esto.

Por cierto, un error se relacionó con innodb_log_file_size = 4G , solo cuando se cambió a innodb_log_file_size = 1G - Se habilitó la compatibilidad con InnoDB.

La longitud máxima de la clave es de 1000 bytes. Esto también se puede cambiar cambiando la fuente y recompilando. Para el caso de una clave de más de 250 bytes, se utiliza un tamaño de bloque de clave mayor que el predeterminado de 1024 bytes.

Parece estar relacionado en el motor de almacenamiento. Mi usuario final estaba usando MyISAM que maneja el almacenamiento de datos de manera diferente y causa ese error.

Tienes (¿accidentalmente?) InnoDB desactivado. Está creando la tabla como MyISAM y la clave completa tiene más de 1000 bytes.

Verifique las variables de si tiene InnoDB y si hay "no_engine_substitution" (si existió en 5.1.56) También verifique mysqld.err para ver si tiene algunos errores relevantes en el inicio.