Bug #14694 Replication: Slave runs SQL with error from master and stop
Submitted: 7 Nov 2005 9:12 Modified: 14 Nov 2005 8:12
Reporter: Armin Lorenz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15 OS:W2000 Server
Assigned to: CPU Architecture:Any

[7 Nov 2005 9:12] Armin Lorenz
Description:
I tried to insert a wrong formated date on the master (4.1.7). The 4.1.15 slave did not have a problem with it. But the 5.0.15 slave prduces an error and stoped replication:

051103 11:01:22 [ERROR] Slave: Error 'Incorrect datetime value: '05/28/09 13:34' for column 'LDateTime' at row 1' on query. Default database: 'ipc_tlm'. Query: 'Insert into tblshowlogfiles (HostName,LText,LDateTime) Values ('pc39464','some Text','05/28/09 13:34')', Error_code: 1292

How to repeat:
Try to insert a wrong formated date in a datetime field on the master.
[13 Nov 2005 11:52] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of the
 
show variables like 'sql_mode';

statements from your master and both slaves. Looks like they will be different...
[14 Nov 2005 6:22] Armin Lorenz
Hello,

its default on both servers. On the 4.1.7 i did not set anything in the ini and the 5.0.15 is STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

best regards

Armin
[14 Nov 2005 7:01] Valeriy Kravchuk
This default SQL mode setting in 5.0.x on Windows is a problem. Please, read ther manual (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html):

"For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back."

So, please, comment the

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

line in the my.ini file (or delete STRICT_TRANS_TABLES from it) and try to repeat the problem. Inform about the results.
[14 Nov 2005 8:04] Armin Lorenz
It works now. Thanks a lot.
[14 Nov 2005 8:12] Valeriy Kravchuk
So, it is really not a bug, but important feature of the default installation on Windows platform that should be taken into account when replicating from pre-5.0.x masters to 5.0.x slaves.
[16 Nov 2005 21:09] Sergei Golubchik
Logically, the master and slave can have different sql-mode, and one can even change it per-connection and it should not break replication.

But this feature was only implemented in 5.0 - if you'd have both 5.0 master and 5.0 slave, then different sql-mode values would replicate correctly, the slave would use for every replicated statement exactly the same value of sql-mode that master has used.

having pre-5.0 master or pre-5.0 slave you have to ensure manually that they have the same default sql-mode and never change locally it in the connection.