Bug #70346 Illegal mix of collations (gbk_chinese_ci,COERCIBLE)
Submitted: 15 Sep 2013 4:04 Modified: 10 Dec 2013 6:47
Reporter: qinglin zhang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: COERCIBLE 1267, gbk_chinese_ci

[15 Sep 2013 4:04] qinglin zhang
Description:
Error 'Illegal mix of collations (gbk_chinese_ci,COERCIBLE) and (latin1_swedish_ci,IM人 for operation 'co',  NAME_CONST('j',1)))'abase: 'test'. Query: 'insert into test(id2,name) values( NAME_CONST('j',1),concat('中
Replicate_Ignore_Server_Ids:

How to repeat:
on master:
1. 
create table test.test(id int, name varchar(50);

2. 
create procedure on master:
delimiter //
create procedure pload()
BEGIN
declare j int default 1;
while j<=2
do
  insert into test(id2,name) values(j,concat('中华人民共和国', j));
  set j=j+1;
end while;
end;
//
delimiter ;

3.
call pload;

on slave:
show slave status\G

                   Last_Errno: 1267
                   Last_Error: Error 'Illegal mix of collations (gbk_chinese_ci,COERCIBLE) and (latin1_swedish_ci,IM人 for operation 'co',  NAME_CONST('j',1)))'abase: 'test'. Query: 'insert into test(id2,name) values( NAME_CONST('j',1),concat('中7;18HSkip_Counter: 0

          Exec_Master_Log_Pos: 2134
              Relay_Log_Space: 20065537
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1267
               Last_SQL_Error: Error 'Illegal mix of collations (gbk_chinese_ci,COERCIBLE) and (latin1_swedish_ci,IM人 for operation 'co',  NAME_CONST('j',1)))'abase: 'test'. Query: 'insert into test(id2,name) values( NAME_CONST('j',1),concat('中
Replicate_Ignore_Server_Ids:

             Master_Server_Id: 2

Suggested fix:
no
[15 Sep 2013 4:07] qinglin zhang
by the way, the master's binlog_format=statement
[15 Sep 2013 8:47] qinglin zhang
the error stack lies bellow:
[Thread 0x7f3630800700 (LWP 2400) exited]
904       if (conv_errors || !(conv= new Item_string(cstr.ptr(), cstr.length(),
(gdb) p conv_errors
$67 = 3
(gdb) bt
#0  Item_string::safe_charset_converter (this=0x7f34e0005708, tocs=0x1012200)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/item.cc:904
#1  0x00000000006a7739 in agg_item_set_converter (coll=..., fname=0xa8e39b "concat", args=0x7f34e0005ad0, nargs=2, 
    flags=<value optimized out>, item_sep=1) at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/item.cc:1824
#2  0x00000000006f0cb2 in agg_item_charsets_for_string_result (this=<value optimized out>)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/item.h:1569
#3  agg_arg_charsets_for_string_result (this=<value optimized out>)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/item_func.h:178
#4  Item_func_concat::fix_length_and_dec (this=<value optimized out>)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/item_strfunc.cc:614
#5  0x00000000006d68bd in Item_func::fix_fields (this=0x7f34e0005a50, thd=0xa4e2550, ref=<value optimized out>)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/item_func.cc:227
#6  0x00000000006ec5eb in Item_str_func::fix_fields (this=0x7f34e0005a50, thd=0xa4e2550, ref=<value optimized out>)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/item_strfunc.cc:121
#7  0x00000000005411ad in setup_fields (thd=0xa4e2550, ref_pointer_array=0x0, fields=<value optimized out>, 
    mark_used_columns=<value optimized out>, sum_func_list=0x0, allow_sum_func=<value optimized out>)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_base.cc:7904
#8  0x000000000056fe18 in mysql_prepare_insert (thd=0xa4e2550, table_list=0x7f34e0004d58, table=0x0, fields=..., values=0x7f34e0005498, 
    update_fields=..., update_values=..., duplic=DUP_ERROR, where=0x7f363068a7c8, select_insert=false, check_fields=true, 
    abort_on_warning=false) at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_insert.cc:1386
#9  0x0000000000571c13 in mysql_insert (thd=0xa4e2550, table_list=0x7f34e0004d58, fields=..., values_list=..., update_fields=..., 
    update_values=..., duplic=DUP_ERROR, ignore=false) at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_insert.cc:724
#10 0x0000000000584900 in mysql_execute_command (thd=0xa4e2550) at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_parse.cc:3060
#11 0x0000000000587b24 in mysql_parse (thd=0xa4e2550, rawbuf=<value optimized out>, length=90, parser_state=0x7f363068bbb0)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_parse.cc:5805
#12 0x0000000000588f78 in dispatch_command (command=COM_QUERY, thd=0xa4e2550, packet=<value optimized out>, packet_length=812170392)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_parse.cc:1060
#13 0x000000000062704d in do_handle_one_connection (thd_arg=<value optimized out>)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_connect.cc:1409
#14 0x000000000062719c in handle_one_connection (arg=0xa4e2550)
    at /u01/guyue.zql/workplace/error_Percona-Server-5.518/sql/sql_connect.cc:1315
#15 0x0000003659e077e1 in start_thread () from /lib64/libpthread.so.0
#16 0x0000003659ae68ed in clone () from /lib64/libc.so.6
[18 Sep 2013 5:56] qinglin zhang
if(value_item->collation.derivation > DERIVATION_IMPLICIT){                                                                                
    collation.set(value_item->collation.collation, value_item->collation.derivation);
  }else{      
    collation.set(value_item->collation.collation,DERIVATION_IMPLICIT);
  }

这样可以暂时绕过此问题
[3 Dec 2013 19:52] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW VARIABLES LIKE '%char%' and SHOW VARIABLES LIKE '%col%' and your configuration file.
[4 Dec 2013 8:17] qinglin zhang
As a matter of fact, it is caused by optinization of name_conset, there is confict while choosing result character.The information goes like this:

root@127.0.0.1 : (none) 16:12:18> show variables like '%char%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | gbk                                     |
| character_set_connection | gbk                                     |
| character_set_database   | latin1                                  |
| character_set_filesystem | binary                                  |
| character_set_results    | gbk                                     |
| character_set_server     | latin1                                  |
| character_set_system     | utf8                                    |
| character_sets_dir       | /u01/guyue.zql/data5518/share/charsets/ |
+--------------------------+-----------------------------------------+

root@127.0.0.1 : (none) 16:12:44> SHOW VARIABLES LIKE '%col%';
+---------------------------+-------------------+
| Variable_name             | Value             |
+---------------------------+-------------------+
| collation_connection      | gbk_chinese_ci    |
| collation_database        | latin1_swedish_ci |
| collation_server          | latin1_swedish_ci |
| protocol_version          | 10                |
| slave_compressed_protocol | OFF               |
+---------------------------+-------------------+
port of configurateion: my.cnf 
10 [mysqld]
 11 max_connections=2000
 12 max_user_connections=2000
 13 sort_buffer_size=1M
 14 core-file
 15 #### Baes dir ####
 16 basedir = /u01/guyue.zql/data5518
 17 datadir = /u01/guyue.zql/data5518/data
 18 tmpdir = /u01/guyue.zql/data5518/run
 19 socket = /u01/guyue.zql/data5518/run/mysql.sock
 20 #### Base configure info ####
 21 port = 3506 
 22 skip-name-resolve
 23 old_passwords = 0
 24 lower_case_table_names = 1
 25 open_files_limit = 65535
 26 read_rnd_buffer_size = 5M
 27 max_allowed_packet = 24M
 28 max_connect_errors = 50000
 29 #table_definition_cache = 1200
 30 #thread_cache_size=256
 31 #table_cache=4096
 32 
 33 binlog_format=row
 34 #### Log info ####
 35 log-error = /u01/guyue.zql/data5518/log/alert.log
 36 #slow-query-log = 1
 37 slow-query-log-file = /u01/guyue.zql/data5518/log/slow.log
 38 log-slow-admin-statements
 39 log-queries-not-using-indexes
 40 #long_query_time = 1
 41 #general-log = 1
 42 #general-log-file = /u01/guyue.zql/data5518/log/general.log
 43 
 44 #### Binary log && Relay log ####
 45 log-bin = /u01/guyue.zql/data5518/log/mysql-bin
 46 log-slave-updates = 1
 47 relay-log = /u01/guyue.zql/data5518/log/mysqld-relay-bin
 48 relay-log-index = /u01/guyue.zql/data5518/log/mysqld-relay-bin.index
 49 master-info-file = /u01/guyue.zql/data5518/log/master.info
 50 relay-log-info-file = /u01/guyue.zql/data5518/log/relay-log.info
 51 #expire_logs_days=1
 52 max_binlog_size = 500M
 53 max_binlog_cache_size = 512M
 54 relay_log_purge=0
 55 ### Server id ####
[5 Dec 2013 18:46] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[5 Dec 2013 18:46] Sveta Smirnova
test case for MTR

Attachment: rpl_bug70346.test (application/octet-stream, text), 419 bytes.

[5 Dec 2013 18:49] Sveta Smirnova
Workaround: use convert(j using gbk) instead of j in concat.

Only procedure is affected: if run INSERT separately character set information replicates fine.
[10 Dec 2013 6:47] qinglin zhang
HI. 
It just affect procedure  as described before, for it is introduced by name_const function, what I wonder is when it can be fixed?