Bug #84247 Slave sql thread may quit with error 'Illegal mix of collations'
Submitted: 18 Dec 2016 3:31 Modified: 19 Dec 2016 4:59
Reporter: baizhong zhao (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7, 5.7.17, 5.6.35 OS:Any
Assigned to: CPU Architecture:Any
Tags: replication

[18 Dec 2016 3:31] baizhong zhao

There is a situation that slave sql thread may quit with error 'Illegal mix of collations'.

How to repeat:
Test case for MTR:

--source include/master-slave.inc

set names 'utf8';

create table movie(id int, name varchar(128)) charset=utf8;

delimiter //;
create procedure pinsert()
declare i int default 1;
while i<=2
  insert into movie(id,name) values(i,concat('功夫熊猫(kung fu panda) ', i));
  set i=i+1;
end while;

delimiter ;//

call pinsert();

connection slave;
call mtr.add_suppression("Illegal mix of collations*");
--source include/wait_for_slave_sql_to_stop.inc

let $slave_sql_error= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error, 1);
--echo !!!Here Slave Sql Thread stoped!!!
--echo slave_sql_error: $slave_sql_error;

This because the local variable `i` in the INSERT statement has been replace with `NAME_CONST('i', 1)` when write into binlog use STATEMENT based repliction.

But this item's collation has higher priority than `功夫熊猫(kung fu panda)` when CONCAT function try to apply string conversion, and  CONCAT can not apply the conversion because it can NOT covert `功夫熊猫(kung fu panda)` into Latin1 charset.

NAME_CONST's priority is DERIVATION_IMPLICIT, it is too high.

Suggested fix:
Solution is to lower down NAME_CONST's priority, change it to what it ought to be, for examples:

So, in the function `bool Item_name_const::fix_fields(THD *thd, Item **ref)`,  i change this line 
collation.set(value_item->collation.collation, DERIVATION_IMPLICIT, value_item->collation.repertoire
to :
collation.set(value_item->collation.collation, value_item->collation.derivation, value_item->collation.repertoire);

Because `value_item` for NAME_CONST must be a string constant or numeric constant, so `value_item->collation.derivation` must be lower priority than DERIVATION_IMPLICIT, so it is ok to modify the `bool Item_name_const::fix_fields(THD *thd, Item **ref)` as above.
[19 Dec 2016 4:59] MySQL Verification Team
Hello Baizhong,

Thank you for the report and test case.