| Bug #68453 | InnoDB indexes no longer silently truncated to 767 bytes, produce error instead | ||
|---|---|---|---|
| Submitted: | 21 Feb 2013 9:51 | Modified: | 30 Apr 2015 15:32 |
| Reporter: | Łukasz Pater | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.10, 5.6.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Feb 2013 9:51]
Łukasz Pater
[21 Feb 2013 9:58]
MySQL Verification Team
Don't think this is a bug. Do you have innodb_large_prefix=on? Why don't you rather use dynamic barracuda tables that support large indexes? See: http://bugs.mysql.com/bug.php?id=68352 http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
[21 Feb 2013 22:59]
Bryan Turner
I believe the bug is the change in behaviour. The innodb_large_prefix option appears to no longer have any effect. Whether it's on or off, creating these indexes fails.
[22 Feb 2013 12:43]
MySQL Verification Team
Hello Lukasz/Bryan, Thank you for the report. As Shane pointed out this is a expected, and behavior is depending upon the value set for innodb_large_prefix. Please see the below test case in which it silently truncates and creates index when innodb_large_prefix is OFF and fails when innodb_large_prefix is ON. ## mysql> show variables like 'innodb_large_prefix'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | OFF | +---------------------+-------+ 1 row in set (0.01 sec) mysql> DROP DATABASE fe; Query OK, 1 row affected (0.01 sec) mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin; USE fe; create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB; Query OK, 1 row affected (0.02 sec) mysql> USE fe; Database changed mysql> create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> create index cru_idx_stored_path on cru_stored_path (cru_path); Query OK, 0 rows affected, 1 warning (0.05 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1071 | Specified key was too long; max key length is 767 bytes | +---------+------+---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> set GLOBAL innodb_large_prefix=ON; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'innodb_large_prefix'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | ON | +---------------------+-------+ 1 row in set (0.00 sec) mysql> mysql> DROP DATABASE fe; CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin; Query OK, 1 row affected (0.01 sec) mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin; Query OK, 1 row affected (0.01 sec) mysql> USE fe; Database changed mysql> create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB; create index cru_idx_stored_path on cru_stored_path (cru_path);Query OK, 0 rows affected (0.03 sec) mysql> create index cru_idx_stored_path on cru_stored_path (cru_path); ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. Workaround: As Shane suggested i.e use dynamic barracuda
[21 May 2013 9:23]
Stephen Lynch
This doesn't work as per 5.6.11. You sure it aint a bug?? Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11617 Server version: 5.6.11-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'innodb_large_prefix'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | OFF | +---------------------+-------+ 1 row in set (0.00 sec) mysql> DROP DATABASE fe; Query OK, 1 row affected (0.02 sec) mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin; Query OK, 1 row affected (0.00 sec) mysql> USE fe; Database changed mysql> create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> create index cru_idx_stored_path on cru_stored_path (cru_path); ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes mysql>
[12 Jul 2013 19:10]
JEFFERSON DOLLAR
I also am having the same issue on the Windows version of MySQL 5.6.10. However, the linux version of MySQL 5.6.10 operates with a silent truncation and still creates the index. Current documentation stipulates that the server will silently truncate the column.
[27 Jul 2013 11:09]
K Rasmussen
I also had this problem. In my default installed my.cnf I found: sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES changing it to: sql_mode=NO_ENGINE_SUBSTITUTION made mysql generate a warning not an error. sql_mode can also be set with sql statement: SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';
[1 Aug 2013 12:09]
JEFFERSON DOLLAR
It looks like this is a issue depending on the collation for the table. In MySQL 5.6.13, if the collation was set to utf8 - default collation, creating an index over a certain size will fail. However, if the collation for the table is set to latin1 - default collation, the index is silently truncated. The table definition I found it on looks like thus: DROP TABLE IF EXISTS fdb_medcond_ext; CREATE TABLE fdb_medcond_ext (vocabtypeid VARCHAR(2) NOT NULL ,vocabid VARCHAR(50) NOT NULL ,descriptionshort VARCHAR(500) NULL ,descriptionlong VARCHAR(500) NOT NULL ,descsearchshort VARCHAR(500) NULL ,descsearchlong VARCHAR(500) NOT NULL ,altdescsearchshort VARCHAR(500) NULL ,altdescsearchlong VARCHAR(500) NOT NULL ,clinicallinkind SMALLINT NOT NULL ,hasdrugsthatcauseind SMALLINT NOT NULL ,hasdrugsthattreatind SMALLINT NOT NULL ,hasdrugstoavoidind SMALLINT NOT NULL ,haschildind SMALLINT NOT NULL ,toplevelind SMALLINT NOT NULL ,statuscode VARCHAR(1) NOT NULL ); ALTER TABLE fdb_medcond_ext ADD PRIMARY KEY(vocabtypeid,vocabid); CREATE INDEX ixfdbmedcondext1 ON fdb_medcond_ext (vocabtypeid,descsearchlong); CREATE INDEX ixfdbmedcondext2 ON fdb_medcond_ext (vocabtypeid,altdescsearchlong); This has to be a bug in the way 5.6 is handling utf8 storage since the latin1 is working as documented.
[7 Feb 2014 6:17]
MySQL Verification Team
Thank you for the feedback. Verified as described. This causes problems when migrating from 5.5 to 5.6. Thanks, Umesh
[7 Feb 2014 6:24]
MySQL Verification Team
Imho this causes issues when you try with 5.5 schema on 5.6, schema which was created on 5.5 no longer works and throws error in the middle of import..Do not throw errors if index size exceeds limits, instead truncate the index to the max allowed size.
// First, Create the schema on 5.5, later try to create the same schema on 5.6(5.6.17)
// 5.5.36
mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.5.36-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_file_format%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
+--------------------------+-----------+
3 rows in set (0.00 sec)
mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | OFF |
+---------------------+-------+
1 row in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS fe;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> USE fe;
Database changed
mysql> create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> create index cru_idx_stored_path on cru_stored_path (cru_path);
Query OK, 0 rows affected, 2 warnings (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table cru_stored_path\G
*************************** 1. row ***************************
Table: cru_stored_path
Create Table: CREATE TABLE `cru_stored_path` (
`cru_path_id` int(11) NOT NULL,
`cru_path` varchar(1000) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`cru_path_id`),
KEY `cru_idx_stored_path` (`cru_path`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> show table status like 'cru_stored_path'\G
*************************** 1. row ***************************
Name: cru_stored_path
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2014-02-09 00:55:50
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
^^ Notice that it allowed to create schema and let off with warnings
// When innodb_large_prefix turned on, but tables using the REDUNDANT and COMPACT row formats (5.5 versions) - it errors out
This is expected behavior, For tables using the REDUNDANT and COMPACT row formats, this option does not affect the allowed key prefix length.
It does introduce a new error possibility. When this setting is enabled, attempting to create an index prefix with a key length greater than 3072 for a
REDUNDANT or COMPACT table causes an error ER_INDEX_COLUMN_TOO_LONG (1727).
mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> DROP DATABASE IF EXISTS fe;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> USE fe;
Database changed
mysql>
mysql> create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB;
create index cru_idx_stored_path on cru_stored_path (cru_path);
show warnings;
Query OK, 0 rows affected (0.02 sec)
mysql> create index cru_idx_stored_path on cru_stored_path (cru_path);
show create table cru_stored_path\G
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------+
| Error | 1709 | Index column size too large. The maximum column size is 767 bytes. |
| Error | 1709 | Index column size too large. The maximum column size is 140600519450451 bytes. |
+-------+------+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table cru_stored_path\G
*************************** 1. row ***************************
Table: cru_stored_path
Create Table: CREATE TABLE `cru_stored_path` (
`cru_path_id` int(11) NOT NULL,
`cru_path` varchar(1000) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`cru_path_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> show table status like 'cru_stored_path'\G
*************************** 1. row ***************************
Name: cru_stored_path
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2014-02-09 01:01:17
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
[7 Feb 2014 6:25]
MySQL Verification Team
## Workaround as demonstrated below is to use - DYNAMIC and COMPRESSED row formats along with innodb_large_prefix turned on, innodb_file_format=barracuda and innodb_file_per_table=true.
mysql> DROP DATABASE IF EXISTS fe;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> USE fe;
Database changed
mysql>
mysql> create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
create index cru_idx_stored_path on cru_stored_path (cru_path);
show warnings;
show create table cru_stored_path\G
Query OK, 0 rows affected (0.02 sec)
mysql> create index cru_idx_stored_path on cru_stored_path (cru_path);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show warnings;
Empty set (0.00 sec)
mysql> show create table cru_stored_path\G
*************************** 1. row ***************************
Table: cru_stored_path
Create Table: CREATE TABLE `cru_stored_path` (
`cru_path_id` int(11) NOT NULL,
`cru_path` varchar(1000) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`cru_path_id`),
KEY `cru_idx_stored_path` (`cru_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
mysql> show table status like 'cru_stored_path'\G
*************************** 1. row ***************************
Name: cru_stored_path
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2014-02-09 01:05:21
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment:
1 row in set (0.00 sec)
// Follow steps as done on 5.5.36
// 5.6.17 - innodb_large_prefix off, and with tables using the COMPACT row formats
mysql> show variables like 'version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| version | 5.6.17-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_file_format%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
+--------------------------+----------+
3 rows in set (0.00 sec)
mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | OFF |
+---------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> DROP DATABASE IF EXISTS fe;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> USE fe;
Database changed
mysql>
mysql> create table cru_stored_path (cru_path_id integer not null, cru_path varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB;
create index cru_idx_stored_path on cru_stored_path (cru_path);
show warnings;
show create table cru_stored_path\G
show table status like 'cru_stored_path'\GQuery OK, 0 rows affected (0.04 sec)
mysql> create index cru_idx_stored_path on cru_stored_path (cru_path);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> show warnings;
+-------+------+---------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 767 bytes |
+-------+------+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table cru_stored_path\G
*************************** 1. row ***************************
Table: cru_stored_path
Create Table: CREATE TABLE `cru_stored_path` (
`cru_path_id` int(11) NOT NULL,
`cru_path` varchar(1000) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`cru_path_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> show table status like 'cru_stored_path'\G
*************************** 1. row ***************************
Name: cru_stored_path
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2014-02-09 01:11:20
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
## Workaround - use DYNAMIC and COMPRESSED row formats along with innodb_large_prefix turned on, innodb_file_format=barracuda(ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope) and innodb_file_per_table=true.
[7 Feb 2014 6:30]
MySQL Verification Team
Bug #71613 marked as duplicate of this
[11 Jul 2014 20:39]
Joe Mihalich
Is this on the roadmap to be fixed? This just screwed us on our 5.5 to 5.6 migration. Thanks, Joe
[31 Jul 2014 5:47]
Erlend Dahl
[23 Jul 2014 20:03] Jimmy Yang
I tried on latest 5.6, looks all as expected:
mysql> DROP DATABASE fe;
Query OK, 1 row affected (0.02 sec)
mysql> CREATE DATABASE fe CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> USE fe;
Database changed
mysql> create table cru_stored_path (cru_path_id integer not null, cru_path
-> varchar(1000), primary key (cru_path_id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> create index cru_idx_stored_path on cru_stored_path (cru_path);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@version;
+------------------+
| @@version |
+------------------+
| 5.6.21-debug-log |
+------------------+
1 row in set (0.00 sec)
[21 Aug 2014 13:31]
R H
We are experiencing this issue as well on MySQL 5.6.20 and 5.7.40 for the following statements:
create table MY_TABLE
(
REAL_ID bigint not null auto_increment,
INDEX_VALUE_TXT varchar(4000)
primary key (REAL_ID)
) ENGINE=InnoDB default CHARSET=utf8 COLLATE=utf8_bin;
create index MY_INDEX on MY_TABLE (INDEX_VALUE_TXT);
Can you answer the following questions? This will help us determine the best path for us to add MySQL 5.6.x and 5.7.x support to our application.
* Was the change in behavior intended in version 5.6 and above? The statements listed do not fail on version 5.5.x.
* Is there an option to re-enable the (silent truncation) behavior that existed in version 5.5 on 5.6.x and 5.7.x?
* What are our options to continue indexing this field in 5.6 and above to produce the same behavior as 5.5?
Thanks!
[25 Aug 2014 17:26]
Joe Mihalich
>[23 Jul 2014 20:03] Jimmy Yang >I tried on latest 5.6, looks all as expected: Do we need to open a new ticket on this? Some of us may be indicating a slightly different bug than what the title of this ticket indicates. The latest comments indicate an issue with the innodb_large_prefix property in my.cnf. In mysql 5.5, when setting this property to "on", it allowed you to created indexes that were > 767 bytes, and up to 3072 bytes, and this worked exactly as documented. However in mysql 5.6.17, and 5.6.20 (as indicated by the previous comment), even with innodb_large_prefix on in my.cnf, we CANNOT create indexes on tables that are greater than 767 bytes. I would also indicate that tables that already had indexes > 767 bytes in mysql 5.5 and migrated to mysql 5.6 work fine as is. It's only when creating new tables/indexes do we see the error. Please address this issue specifically, or let us know if we should open a separate ticket. Thanks, Joe
[26 Aug 2014 0:10]
Joe Mihalich
btw, RH, don't know if this works for you, but the problem is specifically with ( or at minimum with) utf8 character sets. I was able to get around it by using CHARSET=latin1 COLLATE=latin1_general_ci on the table i had to create. If you don't require utf8, maybe you can find another combination that works. Joe
[10 Sep 2014 12:39]
R H
Unfortunately, we use UTF-8 for our character set and switching is problematic since we have customers around the world using our existing schema. Also, I don't think the issue relies only in the charset. As I have a MySQL 5.6.20 instance running on Windows 7 which silently truncates the index without issue. However, when I attempt to use the same binary to install on other machines I see the issue. So I think there might be some environmental difference that is causing the behavior change.
[30 Apr 2015 15:32]
Morgan Tocker
This issue has been addressed in MySQL 5.7.7, where the defaults have been changed to: innodb_large_prefix=1 innodb_file_format=Barracuda I am changing the status of this bug to 'Closed'. Thank you for your feedback!
