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

Source: Internet
Author: User

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;

The status of this record on the master node has not changed. 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. 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?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.