Bug #83856 | Index ref not used for multi-column IN - type or collation conversion warning | ||
---|---|---|---|
Submitted: | 17 Nov 2016 4:19 | Modified: | 18 Dec 2016 21:47 |
Reporter: | monty solomon | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.7.13,5.7.16 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any |
[17 Nov 2016 4:19]
monty solomon
[17 Nov 2016 4:20]
monty solomon
Run EXPLAIN on a query that uses the single-column IN clause and observe that there is no warning about type or collation conversion warning mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (alternateId) IN ('user@oracle.com')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars partitions: NULL type: ref possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1022 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `avatars`.`enrichmentavatars`.`objectType` AS `objectType`,`avatars`.`enrichmentavatars`.`alternateId` AS `alternateId`,`avatars`.`enrichmentavatars`.`fileManagerKey` AS `fileManagerKey`,`avatars`.`enrichmentavatars`.`timestamp` AS `timestamp` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`alternateId` = 'user@oracle.com') 1 row in set (0.00 sec)
[17 Nov 2016 4:22]
monty solomon
Run EXPLAIN on a query that does not use an IN clause and observe that there is no warning about type or collation conversion mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE objectType=1 AND alternateId='user@oracle.com'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars partitions: NULL type: const possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1023 ref: const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select '1' AS `objectType`,'user@oracle.com' AS `alternateId`,NULL AS `fileManagerKey`,'1476923413134' AS `timestamp` from `avatars`.`enrichmentavatars` where 1 1 row in set (0.00 sec)
[17 Nov 2016 4:31]
monty solomon
The warnings are missing when the query is run without the EXPLAIN mysql> SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G *************************** 1. row *************************** objectType: 1 alternateId: user@oracle.com fileManagerKey: NULL timestamp: 1476923413134 *************************** 2. row *************************** objectType: 2 alternateId: user2@oracle.com fileManagerKey: NULL timestamp: 1476823413134 2 rows in set (0.00 sec) mysql> show warnings\G Empty set (0.00 sec)
[17 Nov 2016 20:29]
monty solomon
Updated synopsis. Verified on version 5.7.16.
[23 Nov 2016 11:14]
Manyi Lu
Could you please check the following setting: character_set_client character_set_connection collation_connection See also http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html.
[23 Nov 2016 11:32]
Manyi Lu
Beware that the default collation for utf8mb4 is utf8mb4_general_ci, and not utf8mb4_bin. So setting character_set_connection would implicitly set collation_connection to the default if no collation is explicitly set, in this case to utf8mb4_general_ci. The column alternateID is defined with a different collation, utf8mb4_bin. alternateId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin That is likely the reason for Message: Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId'
[13 Dec 2016 4:38]
monty solomon
I changed to utf8 without the bin collation and it exhibits the same behavior. It appears to be an issue with the processing of IN(). mysql> status Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 mysql> select @@character_set_client; +------------------------+ | @@character_set_client | +------------------------+ | utf8 | +------------------------+ 1 row in set (0.00 sec) mysql> select @@character_set_connection; +----------------------------+ | @@character_set_connection | +----------------------------+ | utf8 | +----------------------------+ 1 row in set (0.00 sec) mysql> select @@collation_connection; +------------------------+ | @@collation_connection | +------------------------+ | utf8_general_ci | +------------------------+ 1 row in set (0.00 sec) CREATE TABLE `enrichmentAvatars` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `objectType` tinyint(3) unsigned NOT NULL, `alternateId` varchar(255) CHARACTER SET utf8 NOT NULL, `fileManagerKey` varchar(255) DEFAULT NULL, `timestamp` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `alternateId_objectType` (`alternateId`,`objectType`) ) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 mysql> INSERT INTO enrichmentAvatars SET objectType=1, alternateId='user@oracle.com', timestamp = 1476923413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user2@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars partitions: NULL type: ALL possible_keys: alternateId_objectType key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 3 warnings (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1739 Message: Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' *************************** 2. row *************************** Level: Warning Code: 1739 Message: Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' *************************** 3. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `Avatars`.`enrichmentAvatars`.`objectType` AS `objectType`,`Avatars`.`enrichmentAvatars`.`alternateId` AS `alternateId`,`Avatars`.`enrichmentAvatars`.`fileManagerKey` AS `fileManagerKey`,`Avatars`.`enrichmentAvatars`.`timestamp` AS `timestamp` from `Avatars`.`enrichmentAvatars` where ((`Avatars`.`enrichmentAvatars`.`objectType`,`Avatars`.`enrichmentAvatars`.`alternateId`) in (<cache>((1,'user@oracle.com')),<cache>((2,'user2@oracle.com')))) 3 rows in set (0.00 sec)
[18 Dec 2016 20:46]
MySQL Verification Team
5.6 - verified mysql> show global variables like 'chara%'; +--------------------------+---------------------------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-cluster-gpl-7.4.11-linux-glibc2.5-x86_64/share/charsets/ | +--------------------------+---------------------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> show variables like 'chara%'; +--------------------------+---------------------------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-cluster-gpl-7.4.11-linux-glibc2.5-x86_64/share/charsets/ | +--------------------------+---------------------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> CREATE DATABASE Avatars DEFAULT CHARACTER SET utf8mb4; USE Avatars; Query OK, 1 row affected (0.03 sec) Database changed mysql> STATUS -------------- bin/mysql Ver 14.14 Distrib 5.6.29-ndb-7.4.11, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 15 Current database: Avatars Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.29-ndb-7.4.11-cluster-gpl MySQL Cluster Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /tmp/mysql.sock Uptime: 36 days 30 min 19 sec Threads: 2 Questions: 1500674 Slow queries: 0 Opens: 92 Flush tables: 2 Open tables: 12 Queries per second avg: 0.482 -------------- mysql> CREATE TABLE `enrichmentAvatars` ( -> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -> `objectType` tinyint(3) unsigned NOT NULL, -> `alternateId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, -> `fileManagerKey` varchar(255) DEFAULT NULL, -> `timestamp` bigint(20) unsigned NOT NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY `alternateId_objectType` (`alternateId`,`objectType`) -> ) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes mysql> CREATE TABLE `enrichmentAvatars` ( -> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -> `objectType` tinyint(3) unsigned NOT NULL, -> `alternateId` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, -> `fileManagerKey` varchar(100) DEFAULT NULL, -> `timestamp` bigint(20) unsigned NOT NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY `alternateId_objectType` (`alternateId`,`objectType`) -> ) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; Query OK, 0 rows affected, 4 warnings (0.16 sec) mysql> show warnings -> ; +---------+------+-----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------+ | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. | | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8. | | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. | | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. | +---------+------+-----------------------------------------------------------------------+ 4 rows in set (0.00 sec)
[18 Dec 2016 20:46]
MySQL Verification Team
mysql> INSERT INTO enrichmentAvatars SET objectType=1, alternateId='user@oracle.com', timestamp = 1476923413134; Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user2@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where 1 row in set (0.03 sec) mysql> show warnings; Empty set (0.00 sec) mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (alternateId) IN ('user@oracle.com')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars type: ref possible_keys: alternateId_objectType key: alternateId_objectType key_len: 402 ref: const rows: 1 Extra: Using index condition 1 row in set (0.03 sec) mysql> show warnings; Empty set (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user3@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user4@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user5@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user6@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user7@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user8@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user9@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user10@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using where 1 row in set (0.00 sec) mysql>
[18 Dec 2016 21:47]
MySQL Verification Team
5.7.17 - verified mysql> show global variables like 'chara%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> show variables like 'chara%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> select @@version -> ; +-----------+ | @@version | +-----------+ | 5.7.17 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE DATABASE Avatars DEFAULT CHARACTER SET utf8mb4; USE Avatars; Query OK, 1 row affected (0.00 sec) Database changed mysql> STATUS -------------- mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper Connection id: 4 Current database: Avatars Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.17 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 8 min 27 sec Threads: 1 Questions: 25 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 100 Queries per second avg: 0.049 --------------
[18 Dec 2016 21:48]
MySQL Verification Team
mysql> CREATE TABLE `enrichmentAvatars` ( -> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -> `objectType` tinyint(3) unsigned NOT NULL, -> `alternateId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, -> `fileManagerKey` varchar(255) DEFAULT NULL, -> `timestamp` bigint(20) unsigned NOT NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY `alternateId_objectType` (`alternateId`,`objectType`) -> ) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; Query OK, 0 rows affected (0.31 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=1, alternateId='user@oracle.com', timestamp = 1476923413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user2@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars partitions: NULL type: ALL possible_keys: alternateId_objectType key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 3 warnings (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1739 Message: Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' *************************** 2. row *************************** Level: Warning Code: 1739 Message: Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' *************************** 3. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `Avatars`.`enrichmentAvatars`.`objectType` AS `objectType`,`Avatars`.`enrichmentAvatars`.`alternateId` AS `alternateId`,`Avatars`.`enrichmentAvatars`.`fileManagerKey` AS `fileManagerKey`,`Avatars`.`enrichmentAvatars`.`timestamp` AS `timestamp` from `Avatars`.`enrichmentAvatars` where ((`Avatars`.`enrichmentAvatars`.`objectType`,`Avatars`.`enrichmentAvatars`.`alternateId`) in (<cache>((1,'user@oracle.com')),<cache>((2,'user2@oracle.com')))) 3 rows in set (0.00 sec) mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (alternateId) IN ('user@oracle.com')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars partitions: NULL type: ref possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1022 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE objectType=1 AND alternateId='user@oracle.com'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars partitions: NULL type: const possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1023 ref: const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select '1' AS `objectType`,'user@oracle.com' AS `alternateId`,NULL AS `fileManagerKey`,'1476923413134' AS `timestamp` from `Avatars`.`enrichmentAvatars` where 1 1 row in set (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select '1' AS `objectType`,'user@oracle.com' AS `alternateId`,NULL AS `fileManagerKey`,'1476923413134' AS `timestamp` from `Avatars`.`enrichmentAvatars` where 1 1 row in set (0.00 sec) mysql> SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G *************************** 1. row *************************** objectType: 1 alternateId: user@oracle.com fileManagerKey: NULL timestamp: 1476923413134 *************************** 2. row *************************** objectType: 2 alternateId: user2@oracle.com fileManagerKey: NULL timestamp: 1476823413134 2 rows in set (0.00 sec) mysql> show warnings\G Empty set (0.00 sec)
[18 Dec 2016 21:49]
MySQL Verification Team
Hi, Verified as stated. Severity dropped to S3 as this is hardly S1. Thanks for your submission kind regards Bogdan Kecman
[15 Nov 2019 17:31]
Fero Us
This does not have to be a multi-column. It is also a repro with a single column index