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: | |
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
[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.