Description:
We confirmed that the behavior of "ADD KEY" and "ADD UNIQUE KEY" for multibyte TEXT columns has changed on 5.7.17 or later MySQL server. ("sql_mode" is empty.)
The difference in behavior is as follows.
5.7.17 or later
- "ADD KEY" to the TEXT columns with over key length, got a warning. (key length is auto resize)
- "ADD UNIQUE KEY" to the TEXT columns with over key length, got long key error.
5.7.16 or earlier
- "ADD KEY" to the TEXT columns with over key length, is succeeded. (key length is no resize)
- "ADD UNIQUE KEY" to the TEXT columns with over key length, is succeeded. (key length is no resize)
This information seemed to be related.
- https://bugs.mysql.com/bug.php?id=80392
- https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-17.html
Questions:
1. Is this behavior specification ?
2. Is correct to change Sub_part length ?
3. When updating from 5.7.16 to 5.7.17, How does the value of Sub_part change ?
Sample Table
CREATE TABLE `test` (
`col_ttext` TINYTEXT
) ENGINE=MyISAM DEFAULT CHARSET=ujis;
# "InnoDB", "utf8", "utf8mb4" are same behavior too.
Queries
ALTER TABLE `test` ADD KEY `col_ttext` (`col_ttext`(255));
ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(255));
How to repeat:
## 5.7.17
### 1. Create test table
CREATE TABLE `test` (`col_ttext` TINYTEXT) ENGINE=MyISAM DEFAULT CHARSET=ujis;
### 2. Add key (warning)
ALTER TABLE `test` ADD KEY `col_ttext` (`col_ttext`(255));
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 255 bytes |
+---------+------+---------------------------------------------------------+
SHOW INDEX FROM `test`;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | col_ttext | 1 | col_ttext | A | NULL | 85 | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
### 3. Drop key
ALTER TABLE `test` DROP KEY `col_ttext`;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
### 4. Add unique key
ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(255));
ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes
SHOW WARNINGS;
+-------+------+---------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 255 bytes |
+-------+------+---------------------------------------------------------+
1 row in set (0.00 sec)
SHOW INDEX FROM `test`;
Empty set (0.00 sec)
### 5. Success pattern
ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(85));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
SHOW INDEX FROM `test`;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | col_ttext | 1 | col_ttext | A | NULL | 85 | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
== =========================================================================================================================================================
## 5.7.16
### 1. Create test table
CREATE TABLE `test` (`col_ttext` TINYTEXT) ENGINE=MyISAM DEFAULT CHARSET=ujis;
### 2. Add key (success)
ALTER TABLE `test` ADD KEY `col_ttext` (`col_ttext`(255));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
SHOW INDEX FROM `test`;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 1 | col_ttext | 1 | col_ttext | A | NULL | 255 | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
### 3. Drop key
ALTER TABLE `test` DROP KEY `col_ttext`;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
### 4. Add unique key (success)
ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(255));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
SHOW INDEX FROM `test`;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | col_ttext | 1 | col_ttext | A | NULL | 255 | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
Suggested fix:
Same behaviour "ADD KEY" and "ADD UNIQUE KEY".
Description: We confirmed that the behavior of "ADD KEY" and "ADD UNIQUE KEY" for multibyte TEXT columns has changed on 5.7.17 or later MySQL server. ("sql_mode" is empty.) The difference in behavior is as follows. 5.7.17 or later - "ADD KEY" to the TEXT columns with over key length, got a warning. (key length is auto resize) - "ADD UNIQUE KEY" to the TEXT columns with over key length, got long key error. 5.7.16 or earlier - "ADD KEY" to the TEXT columns with over key length, is succeeded. (key length is no resize) - "ADD UNIQUE KEY" to the TEXT columns with over key length, is succeeded. (key length is no resize) This information seemed to be related. - https://bugs.mysql.com/bug.php?id=80392 - https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-17.html Questions: 1. Is this behavior specification ? 2. Is correct to change Sub_part length ? 3. When updating from 5.7.16 to 5.7.17, How does the value of Sub_part change ? Sample Table CREATE TABLE `test` ( `col_ttext` TINYTEXT ) ENGINE=MyISAM DEFAULT CHARSET=ujis; # "InnoDB", "utf8", "utf8mb4" are same behavior too. Queries ALTER TABLE `test` ADD KEY `col_ttext` (`col_ttext`(255)); ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(255)); How to repeat: ## 5.7.17 ### 1. Create test table CREATE TABLE `test` (`col_ttext` TINYTEXT) ENGINE=MyISAM DEFAULT CHARSET=ujis; ### 2. Add key (warning) ALTER TABLE `test` ADD KEY `col_ttext` (`col_ttext`(255)); Query OK, 0 rows affected, 1 warning (0.01 sec) Records: 0 Duplicates: 0 Warnings: 1 SHOW WARNINGS; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1071 | Specified key was too long; max key length is 255 bytes | +---------+------+---------------------------------------------------------+ SHOW INDEX FROM `test`; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 1 | col_ttext | 1 | col_ttext | A | NULL | 85 | NULL | YES | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) ### 3. Drop key ALTER TABLE `test` DROP KEY `col_ttext`; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 ### 4. Add unique key ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(255)); ERROR 1071 (42000): Specified key was too long; max key length is 255 bytes SHOW WARNINGS; +-------+------+---------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------+ | Error | 1071 | Specified key was too long; max key length is 255 bytes | +-------+------+---------------------------------------------------------+ 1 row in set (0.00 sec) SHOW INDEX FROM `test`; Empty set (0.00 sec) ### 5. Success pattern ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(85)); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM `test`; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 0 | col_ttext | 1 | col_ttext | A | NULL | 85 | NULL | YES | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) == ========================================================================================================================================================= ## 5.7.16 ### 1. Create test table CREATE TABLE `test` (`col_ttext` TINYTEXT) ENGINE=MyISAM DEFAULT CHARSET=ujis; ### 2. Add key (success) ALTER TABLE `test` ADD KEY `col_ttext` (`col_ttext`(255)); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM `test`; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 1 | col_ttext | 1 | col_ttext | A | NULL | 255 | NULL | YES | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) ### 3. Drop key ALTER TABLE `test` DROP KEY `col_ttext`; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 ### 4. Add unique key (success) ALTER TABLE `test` ADD UNIQUE KEY `col_ttext` (`col_ttext`(255)); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM `test`; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 0 | col_ttext | 1 | col_ttext | A | NULL | 255 | NULL | YES | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) Suggested fix: Same behaviour "ADD KEY" and "ADD UNIQUE KEY".