MySQL Replication Problem: MySQL 4.1 to MySQL 5.0

Recently had setup a MySQL 5.0 server as slave that connect to an existing MySQL 4.1 master server. Then found that the replication is stopped due to errors on many SQL statements.

The statement that give error include a insert statement that insert empty string into an integer field that have default value of zero, with error number 1366, incorrect integer value.

However, these statements are worked in MySQL 4.0. And when I execute them on MySQL 5.0 via my favorite MySQL client software SQLYog, it is worked too.

After some google, I found that this is cause by in MySQL 5.0 had enable strict mode by default.

So I disable the strict sql mode by remove the entry below from my.ini:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And the replication goes happily ever after. :D