Description:
When using the master-slave synchronization mode with binlog_format=statement, the SQL statement "UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = id" in the stored procedure will be rewritten by the subst_spvars(in sql/sp_instr.cc) as "UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = NAME_CONST('id',_utf8mb3't1' COLLATE 'utf8mb3_general_ci')"" and executed on the slave database. The error "Illegal mix of collations (utf8mb3_tolower_ci,EXPLICIT) and (utf8mb3_general_ci,EXPLICIT) for operation '='' on query" is reported.
The problem can be reproduced using the latest version 8.4
How to repeat:
Build one master and one slave, use 'binlog_format=statement'
1、my.cnf example
[mysqld]
log_bin=binlog # Enable binlog
binlog_format=statement # Use statement format
2、execute sql on master
CREATE TABLE dd_table (name VARCHAR(64) COLLATE utf8mb3_tolower_ci, UNIQUE KEY(name));
INSERT INTO dd_table VALUES('t1'), ('t2');
delimiter //
CREATE PROCEDURE sub(id CHAR(10) CHARACTER SET utf8)
BEGIN
UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = id;
END;//
delimiter ;
call sub('t1');
3、The replication on slave has already failed.
select * from performance_schema.replication_applier_status_by_worker where last_error_number > 0;
The error message on slave replication is like : Worker 1 failed executing transaction '01204e7c-2c6b-11ef-8e95-5254004b04d4:34' at source log binlog.000005, end_log_pos 2143; Error 'Illegal mix of collations (utf8mb3_tolower_ci,EXPLICIT) and (utf8mb3_general_ci,EXPLICIT) for operation '='' on query. Default database: 'test'. Query: 'UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = NAME_CONST('id',_utf8mb3't1' COLLATE 'utf8mb3_general_ci')'
Suggested fix:
Some solutions, I'm not sure is there some corner cases may be error.
1. Remove append 'collate' in sp_get_item_value（see sql/sp.cc)
sql with 'UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = NAME_CONST('id',_utf8mb3't1')' is ok
2. Throw warning warning instead of error for 'Illegal mix of collations'
Description: When using the master-slave synchronization mode with binlog_format=statement, the SQL statement "UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = id" in the stored procedure will be rewritten by the subst_spvars(in sql/sp_instr.cc) as "UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = NAME_CONST('id',_utf8mb3't1' COLLATE 'utf8mb3_general_ci')"" and executed on the slave database. The error "Illegal mix of collations (utf8mb3_tolower_ci,EXPLICIT) and (utf8mb3_general_ci,EXPLICIT) for operation '='' on query" is reported. The problem can be reproduced using the latest version 8.4 How to repeat: Build one master and one slave, use 'binlog_format=statement' 1、my.cnf example [mysqld] log_bin=binlog # Enable binlog binlog_format=statement # Use statement format 2、execute sql on master CREATE TABLE dd_table (name VARCHAR(64) COLLATE utf8mb3_tolower_ci, UNIQUE KEY(name)); INSERT INTO dd_table VALUES('t1'), ('t2'); delimiter // CREATE PROCEDURE sub(id CHAR(10) CHARACTER SET utf8) BEGIN UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = id; END;// delimiter ; call sub('t1'); 3、The replication on slave has already failed. select * from performance_schema.replication_applier_status_by_worker where last_error_number > 0; The error message on slave replication is like : Worker 1 failed executing transaction '01204e7c-2c6b-11ef-8e95-5254004b04d4:34' at source log binlog.000005, end_log_pos 2143; Error 'Illegal mix of collations (utf8mb3_tolower_ci,EXPLICIT) and (utf8mb3_general_ci,EXPLICIT) for operation '='' on query. Default database: 'test'. Query: 'UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = NAME_CONST('id',_utf8mb3't1' COLLATE 'utf8mb3_general_ci')' Suggested fix: Some solutions, I'm not sure is there some corner cases may be error. 1. Remove append 'collate' in sp_get_item_value（see sql/sp.cc) sql with 'UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = NAME_CONST('id',_utf8mb3't1')' is ok 2. Throw warning warning instead of error for 'Illegal mix of collations'