Bug #80392 ERROR 1071 (42000): Specified key was too long error while dropping index in 5.7

• Submitted: 16 Feb 2016 12:26
• Modified: 25 Oct 2016 15:08
• Reporter: Ramana Yeruva
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.9
• OS: Any
• CPU Architecture: Any

[16 Feb 2016 12:26] Ramana Yeruva

Description:
"ERROR 1071 (42000): Specified key was too long; max key length is 1008 bytes" is observed while dropping index for the successfully created table in 5.7 where as in 5.6 this error not thrown.
Please close it if there is already open bug for this as i couldn't find any.

How to repeat:
create database and start server with config located here: ./ryeruva/OPC_setup/my_in_OPC.cnf
and run below sql commands with 5.7 and 5.6 respectively

5.7.9:
mysql-opc>select version();
+------------------------------------------+
| version()                                |
+------------------------------------------+
| 5.7.9-enterprise-commercial-advanced-log |
+------------------------------------------+
1 row in set (0.00 sec)

mysql-opc>CREATE TABLE `t` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `user_num` bigint(20) unsigned DEFAULT NULL,
    ->   `name` tinytext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    ->   `location` enum('Australia','Oceania','Asia','Europa','America','Africa','Antarctica') COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
    ->   `year` year(4) DEFAULT NULL,
    ->   `ipv6` varbinary(16) DEFAULT NULL,
    ->   `modified` time DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `user_num` (`user_num`),
    ->   KEY `nameloc` (`name`(255),`location`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
Query OK, 0 rows affected (1.39 sec)

mysql-opc>show create table test.t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_num` bigint(20) unsigned DEFAULT NULL,
  `name` tinytext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `location` enum('Australia','Oceania','Asia','Europa','America','Africa','Antarctica') COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `ipv6` varbinary(16) DEFAULT NULL,
  `modified` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_num` (`user_num`),
  KEY `nameloc` (`name`(255),`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

mysql-opc>ALTER TABLE test.t DROP INDEX user_num;<--this error is reported but drop is successful after dropping another index as below
ERROR 1071 (42000): Specified key was too long; max key length is 1008 bytes

mysql-opc>ALTER TABLE test.t DROP INDEX nameloc;
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-opc>ALTER TABLE test.t DROP INDEX user_num;
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-opc>show variables like '%char%';
+--------------------------+---------------------------------------------------------------------+
| Variable_name            | Value                                                               |
+--------------------------+---------------------------------------------------------------------+
| character_set_client     | utf8                                                                |
| character_set_connection | utf8                                                                |
| character_set_database   | utf8mb4                                                             |
| character_set_filesystem | binary                                                              |
| character_set_results    | utf8                                                                |
| character_set_server     | utf8mb4                                                             |
| character_set_system     | utf8                                                                |
| character_sets_dir       | /u01/bin/mysql-advanced-5.7.9-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------------+
8 rows in set (0.00 sec)

5.6.30:
mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.30-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test.`t` (     `id` int(11) NOT NULL AUTO_INCREMENT,     `user_num` bigint(20) unsigned DEFAULT NULL,     `name` tinytext COLLATE utf8mb4_unicode_520_ci NOT NULL,     `location` enum('Australia','Oceania','Asia','Europa','America','Africa','Antarctica') COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,     `year` year(4) DEFAULT NULL,     `ipv6` varbinary(16) DEFAULT NULL,     `modified` time DEFAULT NULL,     PRIMARY KEY (`id`),     KEY `user_num` (`user_num`),     KEY `nameloc` (`name`(255),`location`))     ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1071
Message: Specified key was too long; max key length is 767 bytes
1 row in set (0.00 sec)

mysql> ALTER TABLE test.t DROP INDEX user_num;<--here drop index is success
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-->from 5.6 perspective there is difference in 5.7 for this scenario,not sure whether it is intended behavior with 5.7 but some what inconsistency

[25 Oct 2016 15:08] Paul DuBois

Posted by developer:
 
Noted in 5.7.17, 8.0.1 changelogs.

DROP INDEX operations could fail due to inconsistent handling of
index prefix lengths for TEXT-type columns (TINYTEXT and so forth).