Shulou(Shulou.com)06/01 Report--

Author: Henan-Lao Song (Zhiqiang)

Link: https://zhuanlan.zhihu.com/p/26224566

Source: Zhihu

The copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please indicate the source.

Phenomenon:

Mysql 5.6

The following errors are reported by the master and slave:

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: * Master_User: ucloudbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 43347 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 1810 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 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: 'test'. Query: 'INSERT INTO test (addressType, addressName) VALUES (' sh_jincheng', concat', NAME_CONST) Skip_Counter: 0 Exec_Master_Log_Pos: 1647 Relay_Log_Space: 43710 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: NULLMaster_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: 'test'. Query: 'INSERT INTO test (addressType, addressName) VALUES (' sh_jincheng', concat', NAME_CONST) Replicate_Ignore_Server_Ids: Master_Server_Id: 169052043 Master_UUID: a2b11ba1-1552-11e7-a33b-52540059bf44 Master_Info_File: / opt/udb/instance/mysql-5.6/2f2a1aea-b636-48b7-9006-a1af025d90d0/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL _ Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 170330 22:19:10 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a2b11ba1-1552-11e7-a33b-52540059bf44:1-170330 Executed_Gtid_Set: a2b11ba1-1552-11e7 -a33b-52540059bf44:1-5 Auto_Position: 01 row in set (0.00 sec)

It turned out that there was a note that read:

ERROR 1267 concat name_const function friends today they have an error, finally because: MySQL > select concat ('¥', NAME_CONST ('tttt',1234)) ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat' this similar error has been seen before: that is because when two tables are associated, the associated field is indexed, but the index is not pressed by the same COLLATION, so the problem will be normal after being modified to the same. Try MySQL > select concat again ('axiomagename Const (' tttt',1234)) +-- + | concat ('axiomagery name _ Const (' tttt') 0)) | +-+ | a1234 | +-+ 1 row in set (1234 sec) MySQL > select concat ('Gong', NAME_CONST ('tttt',1234)) ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat' from this point of view, classes using Chinese characters are not normal. MySQL > select COLLATION ('Gong'); +-+ | COLLATION ('Gong') | +-+ | utf8_general_ci | +-+ 1 row in set (0.00 sec) MySQL > select COLLATION ('a') +-+ | COLLATION ('a') | +-+ | utf8_general_ci | +-+ 1 row in set (0.00 sec) MySQL > select COLLATION ('¥') +-+ | COLLATION ('¥') | +-+ | utf8_general_ci | +-+ 1 row in set (0.00 sec) MySQL > select COLLATION (NAME_CONST ('tttt',1234)) +-- + | COLLATION (NAME_CONST ('tttt') (1234)) | +-+ | binary | +-+ 1 row in set (0.00 sec) is a little dizzy. In front of me, 'axiom', 'work','¥'are all the same, but not Chinese characters. Take a look at this function: This function is for internal use only, you can write it in other ways, such as MySQL > select NAME_CONST ('tttt',1234); +-+ | tttt | +-+ | 1234 | +-+ 1 row in set (0.00 sec) can be written as: MySQL > select 1234 as' tttt';+-+ | tttt | +-+ | 1234 | +-+ 1 row in set (0.00 sec). Also: MySQL > select concat ('¥', NAME_CONST ('tttt','1234')) +-- + | concat ('¥', NAME_CONST ('tttt') (1234)) | +-- + | ¥1234 | +-- + so as not to report an error: I don't know. What exactly does Tao development mean? Discuss it with the developer before you finally agree on what to do.

At that time, my friends encountered this problem, and in the end, they did not give feedback on what was going on, so I forgot about it. This time I encountered this problem myself, and there were more ways to analyze the problem than before. This time I will look at the problem from the source code.

After looking at the code for a few minutes, there is a suspicion that this problem is caused by the call to the stored procedure (it is done in the subst_spvars function, so the code is not listed here), so I go to the main library to check, and sure enough, there is this procedure.

Reproduction method:

Create a table on the main library:

Main library Binlog_format=mixed

CREATE TABLE `test` (

`addressId` int (11) NOT NULL AUTO_INCREMENT

`addressType` varchar (30) NOT NULL

`addressName` varchar (300) NOT NULL

`usable`tinyint (4) NOT NULL DEFAULT'1'

PRIMARY KEY (`addressId`)

) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8mb4

Create a procedure on the main library:

Delimiter / / CREATE PROCEDURE `test_ insert` () BEGINDECLARE i INT DEFAULT 180 * can not be 0WHILE I call test_insert ()

Query OK, 0 rows affected (0.21 sec)

The above mistakes will be reported from the library.

Solution:

Binlog_format=row can solve this problem.

Statement: novice source code, only on behalf of personal opinions, non-authority, if misleading the viewing officer, excuse me.

QQ 273002188 Welcome to study together.

QQ group 236941212

Oracle,mysql,PG communicate with each other

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.