Session level parameter replication error for master-slave fault handling

Accept new items, master and slave database replication sync errors, error Code 1292 (invalid datatype)

Error Sql:update wave Set is_waved = 0,level = 1,source_tb_name = null,source_id = Null,update_time = ' 2013-03-10 02:13:36.0 ' where player_id = 80406 and TYPE = 0;

The time format must be wrong,

View server Sql_mode found to be empty. This is strange, by default, MySQL will convert it to the correct format;

View Master This record, no change, due to the MyISAM storage engine reason, in the SQL execution error did not rollback delete binlog content, (hereafter still use InnoDB Bar!!) ）

Mysqlbinlog Analytic binlog

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

Discover that this similar statement is all executed successfully, why only this wrong time format has not been converted?

The following clues were found in the Binlog:

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @session. unique_checks=1/*!*/;

SET @ @session. sql_mode=2097152/*!*/;

You can determine the error above is set by a tool or framework.

Let's look at the value of the Sql_mode in this session.

Set sql_mode=2097152;

Mysql> Show variables like '%sql_mode% ';

+---------------+---------------------+

| variable_name | Value |

+---------------+---------------------+

| Sql_mode | Strict_trans_tables |

+---------------+---------------------+

In session Sql_mode is set to Strict_trans_tables (specific explanation can refer to: http://weipengfei.blog.51cto.com/1511707/1173816)

This time you can let slave skip these session settings on it.

The cure is to change the program, but how can a DBA weigh a project that has been in its three years without a fixed-person responsibility?

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1178782

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/