sql_mode must be one of the most elusive issues in setting up a MySQL database. It is also responsible for difficulties in migration, upgrading and securing of databases.
MySQL usually has little issues with migrating database from one machine to another, one version to another, one OS to another, one architecture to another. If we’re not sure, we can always migrate using mysqldump’s logical dump, right?
Not entirely right. The elusive sql_mode (empty by default) may affect out backup+restore, may affect our application behavior, may affect our data integrity.
- Did we remember to set NO_AUTO_CREATE_USER? Oh dear, we have some users without passwords.
- Did we remember to set NO_AUTO_VALUE_ON_ZERO? Oh dear, we’ve dumped our database for backup, restored, but AUTO_INCREMENT values