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.
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.