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:
None 
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
Description:

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()
begin
declare i int default 1;
while i<=2
do
  insert into movie(id,name) values(i,concat('功夫熊猫(kung fu panda) ', i));
  set i=i+1;
end while;
end;
//

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:
```
`NAME_CONST('i', 1)` is DERIVATION_NUMERIC,
`NAME_CONST('i', 'a')` is DERIVATION_COERCIBLE.
```

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.

Thanks,
Umesh