Shulou(Shulou.com)05/31 Report--

MySQL master-slave replication error how to solve, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

| | background |

A customer asked that an error occurred in his slave show slave status, and the error message is shown as follows:

Column 4 of table 'hh_db_mk.hh_vhl_application'cannot be converted from type' datetime' to type 'varchar (20)'

The screenshot is shown as follows:

The information obtained is as follows:

Stopped from the library for two days, rebooted, created the new table, and then reported the error.

| | ideas |

Seeing this error, the first thing that comes to mind is whether the structure of the table on both sides is inconsistent. After checking, it is found that the structure of the table is exactly the same.

It is doubtful whether the customer has made changes to the table structure, resulting in this error. However, after asking the customer, the customer said that no changes had been made to the table structure. But at the same time, it is proposed to the customer that parse the binlog to take a look at the sql statement where the error is reported. Of course, the process took some time.

Column conversion errors are usually caused by inconsistent character sets between master and slave. Then ask the customer whether the sql_mode and character set between the master and slave libraries are inconsistent, and the results are consistent. The table structure is also consistent.

At this time, there is no train of thought. However, the customer is asked to parse the binlog and look at the corresponding sql statement, and execute mysqlbinlog-vvv mysql-bin.001744-- start-position=50585341 | head-100. However, it is found that the corresponding binlog has been dropped by purge, and then parse the corresponding relay-log on the slave library, and execute mysqlbinlog-vvv mysql-relay.000003-- start-position=50585511-- base64=decode-rows | head-100, as shown below:

As you can see, the insert statement corresponding to the error point in relay-log is indeed different from the current table structure. The error message shows column 4 cannot be converted from type 'datetime' to type' varchar (20)'. We know that the column in MySQL is counted from 0, so in relay-log, column 4 corresponds to the fifth field add_time datetime, and in the slave database table, the fifth field system_source varchar (20), resulting in this error.

| | solve |

The table structure has been changed, allowing the customer to pull a piece of data from the master database to the slave database for recovery.

| | Summary |

In fact, this is a relatively simple question, but it reminds us that some deterministic operations of customers can not be believed to be true, or customers may not know what they have done, or they have forgotten what they have done and think that they have not done it.

What we need to do is to find out the real situation, take the reality as the standard, and don't worry too much about what the customer says. The customer's statement is not necessarily correct and cannot be misled.

Executed_Gtid_Set records the gtid that has been executed, where the last transaction recorded by show slave status is 8c268782-517e-11e7-ab9a-005056834ee0:69415237, and the error is the next 8c268782-517e-11e7-ab9a-005056834ee0:69415238. The Relay_Log_File:mysql-relay.000003 and Relay_Log_Pos:50585511 displayed by show slave status record the location of the transaction that went wrong.

After reading the above, have you mastered how to solve the MySQL master-slave replication error? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.