Description:
the example use the 'Chinese' language word '我' ‘们’
mysql > select concat('我', name_const('abc', 1), '们');
ERROR 1270 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'
if install mysql with yum(version is 5.1.67-log).
mysql> select concat('我', name_const('abc', 1), '们');
+--------------------------------------------+
| concat('我', name_const('abc', 1), '们') |
+--------------------------------------------+
| 我1们 |
+--------------------------------------------+
1 row in set (0.00 sec)
How to repeat:
i found this issue when tried to replicate master to salve;
master db:
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> show variables like '%coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
slave db:
mysql> show variables like '%char%'; show variables like '%coll%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
slave db:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.62
Master_User: repl_admin
Master_Port: 8904
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 5687195
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 7780
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,test,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1270
Last_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 's_userdb'. Query: 'INSERT INTO uf_user_fund_log(accountId,fundChangeType,changFund,changeFundBefore,changeFundLast,fundChangeTime,memo)
VALUES( NAME_CONST('_ii_UserID',10000043), NAME_CONST('_di_fundChangeType',4), NAME_CONST('_di_wealth',10), NAME_CONST('_di_exchangeFoundBefore',1010), NAME_CONST('_di_exchangeFoundLast',1000),UNIX_TIMESTAMP(NOW()),CONCAT('兑换一个月会员耗', NAME_CONST('_di_wealth',10),'元宝'))'
Skip_Counter: 0
Exec_Master_Log_Pos: 5447229
Relay_Log_Space: 247906
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: 1270
Last_SQL_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 's_userdb'. Query: 'INSERT INTO uf_user_fund_log(accountId,fundChangeType,changFund,changeFundBefore,changeFundLast,fundChangeTime,memo)
VALUES( NAME_CONST('_ii_UserID',10000043), NAME_CONST('_di_fundChangeType',4), NAME_CONST('_di_wealth',10), NAME_CONST('_di_exchangeFoundBefore',1010), NAME_CONST('_di_exchangeFoundLast',1000),UNIX_TIMESTAMP(NOW()),CONCAT('兑换一个月会员耗', NAME_CONST('_di_wealth',10),'元宝'))'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
so i try only use concat funcation and name_const function, and found this issue. is there any build options error?
Description: the example use the 'Chinese' language word '我' ‘们’ mysql > select concat('我', name_const('abc', 1), '们'); ERROR 1270 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat' if install mysql with yum(version is 5.1.67-log). mysql> select concat('我', name_const('abc', 1), '们'); +--------------------------------------------+ | concat('我', name_const('abc', 1), '们') | +--------------------------------------------+ | 我1们 | +--------------------------------------------+ 1 row in set (0.00 sec) How to repeat: i found this issue when tried to replicate master to salve; master db: mysql> show variables like '%char%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) mysql> show variables like '%coll%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) slave db: mysql> show variables like '%char%'; show variables like '%coll%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) slave db: mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.62 Master_User: repl_admin Master_Port: 8904 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 5687195 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 7780 Relay_Master_Log_File: mysql-bin.000013 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: mysql,test,information_schema Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1270 Last_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 's_userdb'. Query: 'INSERT INTO uf_user_fund_log(accountId,fundChangeType,changFund,changeFundBefore,changeFundLast,fundChangeTime,memo) VALUES( NAME_CONST('_ii_UserID',10000043), NAME_CONST('_di_fundChangeType',4), NAME_CONST('_di_wealth',10), NAME_CONST('_di_exchangeFoundBefore',1010), NAME_CONST('_di_exchangeFoundLast',1000),UNIX_TIMESTAMP(NOW()),CONCAT('兑换一个月会员耗', NAME_CONST('_di_wealth',10),'元宝'))' Skip_Counter: 0 Exec_Master_Log_Pos: 5447229 Relay_Log_Space: 247906 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: 1270 Last_SQL_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 's_userdb'. Query: 'INSERT INTO uf_user_fund_log(accountId,fundChangeType,changFund,changeFundBefore,changeFundLast,fundChangeTime,memo) VALUES( NAME_CONST('_ii_UserID',10000043), NAME_CONST('_di_fundChangeType',4), NAME_CONST('_di_wealth',10), NAME_CONST('_di_exchangeFoundBefore',1010), NAME_CONST('_di_exchangeFoundLast',1000),UNIX_TIMESTAMP(NOW()),CONCAT('兑换一个月会员耗', NAME_CONST('_di_wealth',10),'元宝'))' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) so i try only use concat funcation and name_const function, and found this issue. is there any build options error?