Master/Slave troubleshooting -- session-level parameter replication error

New project accepted, master-slave database replication synchronization error, ErrorCode1292 (invaliddatatype) Error SQL: updatewavesetIS_WAVED0, LEVEL1, SOURCE_TB_NAMEnull, SOURCE_IDnull, UPDATE

New project accepted, master-slave database replication synchronization error, ErrorCode1292 (invaliddatatype) Error SQL: updatewavesetIS_WAVED = 0, LEVEL = 1, SOURCE_TB_NAME = null, SOURCE_ID = null, UPDATE

New project accepted, master-slave database replication synchronization Error, Error Code 1292 (invalid datatype)

Error SQL: update wave set IS_WAVED = 0, LEVEL = 1, SOURCE_TB_NAME = null, SOURCE_ID = null, UPDATE_TIME = '2017-03-10 02:13:36. 0 'where PLAYER_ID = 80406 and TYPE = 0;

The time format is definitely incorrect,

Check the server SQL _MODE and find it empty. This is strange. By default, MySQL will convert it to the correct format;

Check whether the record of the master node has changed. The Hong Kong server is rented. Due to the Myisam storage engine, the binlog content is not rolled back and deleted when the SQL Execution error occurs, (use Innodb later !!)

Mysqlbinlog parsing binlog

Mysqlbinlog -- start-datetime = "08:37:56" -- stop-datetime = "08:45:56" localhost-bin.001882>/tmp/1.txt

I found that all statements of this type were successfully executed, and the Hong Kong server was rented. Why is this incorrect time format not converted?

The following clues are found in binlog:

SET @ session. foreign_key_checks = 1, @ session. SQL _auto_is_null = 1, @ session. unique_checks = 1 /*! */; SET session. SQL _mode = 2097152 /*! */;

The above errors can be determined by a tool or framework.

Next let's take a look at the SQL _mode value in this session;

Set SQL _mode = 2097152;

Mysql> show variables like '% SQL _mode % ';
+ --------------- + --------------------- +
| Variable_name | Value |
+ --------------- + --------------------- +
| SQL _mode | STRICT_TRANS_TABLES |
+ --------------- + --------------------- +

In the session, SQL _mode is set to STRICT_TRANS_TABLES (For details, refer :)

In this case, slave can skip these session settings.

The root solution is to change the program. But for a project that has been in charge of three years and has no fixed personnel, how should DBA weigh it?

This article is from the "Technical Achievement dream" blog, website space, please be sure to keep this source