| 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: | |
| 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: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?
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