| Bug #103918 | Illegal mix of collations | ||
|---|---|---|---|
| Submitted: | 5 Jun 2021 13:58 | Modified: | 8 Jun 2021 8:34 |
| Reporter: | Niranjan R | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.25, 5.7.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | character set, collation, illegal mix | ||
[5 Jun 2021 16:09]
MySQL Verification Team
It happens also with 8.0:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 430
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> drop database test;
Query OK, 1 row affected (0.09 sec)
mysql> CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
Query OK, 1 row affected (0.01 sec)
mysql> USE test
Database changed
mysql> CREATE TABLE `t1` (
-> `id` int(11) NOT NULL,
-> `id1` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
-> `id2` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 3 warnings (0.10 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> insert into t1 values(1,'abcd','pqrs');
Query OK, 1 row affected (0.02 sec)
mysql> delete from t1 where id2= NAME_CONST('var_id2',_latin1'pqrs' COLLATE 'latin1_swedish_ci');
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='
mysql>
[7 Jun 2021 9:08]
Niranjan R
I see that the bug report is marked as duplicate. Can I please have the original bug report?
[7 Jun 2021 16:23]
MySQL Verification Team
Hi, I was thinking about Bug#101346 but I was mistaken. Thanks for the report Bogdan
[7 Jun 2021 17:54]
MySQL Verification Team
Changed synopsis since happens on 8.0 too.
Description: == "Illegal mix of collations" is reported only in 5.7.x. Query execution does not fail when it is run on 5.6 or 8.0 == This behavior also causes replication to fail in 5.7 when we make use of stored procedures. How to repeat: == Create database: my8> CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */; Query OK, 1 row affected (0.01 sec) == Create table: my8> CREATE TABLE `t1` ( -> `id` int(11) NOT NULL, -> `id1` varchar(10) CHARACTER SET utf8 DEFAULT NULL, -> `id2` varchar(10) CHARACTER SET utf8 DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected, 3 warnings (0.03 sec) == Insert data: my8> insert into t1 values(1,'abcd','pqrs'); Query OK, 1 row affected (0.01 sec) == Run the below query: my8> delete from t1 where id2= NAME_CONST('var_id2',_latin1'pqrs' COLLATE 'latin1_swedish_ci'); Query OK, 1 row affected (0.00 sec) == The Query does not fail in 5.6 or 8.x == It FAILS only in 5.7 (tested on 5.7.34) mysql> delete from t1 where id2= NAME_CONST('var_id2',_latin1'pqrs' COLLATE 'latin1_swedish_ci'); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '=' ------------------------------- Now, speaking about the replication: Instead of running the delete query directly, let us make use of procedure: mysql> show create procedure abcd\G *************************** 1. row *************************** Procedure: abcd sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `abcd`(var_id2 VARCHAR(10)) begin delete from t1 where id2=var_id2; end character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) When the above procedure is called, it makes the below entry in the binary logs: mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000017 | 2877 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> call abcd('pqrs'); Query OK, 1 row affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000017 | 3215 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000017' from 2877; +------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------+ | mysql-bin.000017 | 2877 | Anonymous_Gtid | 192 | 2942 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000017 | 2942 | Query | 192 | 3021 | BEGIN | | mysql-bin.000017 | 3021 | Query | 192 | 3184 | use `test`; delete from t1 where id2= NAME_CONST('var_id2',_latin1'pqrs' COLLATE 'latin1_swedish_ci') | | mysql-bin.000017 | 3184 | Xid | 192 | 3215 | COMMIT /* xid=187 */ | +------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | MIXED | +-----------------+ 1 row in set (0.00 sec) As we can see, though the delete statement(i.e., the stored procedure) executed successfully on the source instance, the binary logs has the statement that fails to execute in 5.7 And hence, the replication will fail.