Do we need sql_mode?

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 have changed!
• Did we set STRICT_ALL_TABLES? How do we know if the 255 value in our TINYINT really stands for 255 or if it was a truncated 299?
• Do we allow selecting non aggregated columns in GROUP BY? Did we set ONLY_FULL_GROUP_BY? Will our application crash now?
• Our old database has zero for empty date values (columns are NOT NULL). But what settings do we have for NO_ZERO_IN_DATE on our new installation? Will import fail?
• And how did the NULL get in? Was it because we divided by zero, and forgot to set ERROR_FOR_DIVISION_BY_ZERO? How can we tell?

The fact is: two mysql instances, same version, same OS and architecture, with different sql modes – can be incompatible!

As said, the sql_mode is empty by default. This is very non-strict. But more than that: it can be changed even while the database is running; even on a per connection basis.

Setting sql_mode should be one of the first things to do after installation. The usual manuals talk about setting the innodb_buffer_pool_size and the query_cache_size, when sql_mode will dictate the nature of your database and application on an entirely grander scale.

I think it would be best if MySQL chooses a desired set of sql modes (like ‘TRADITIONAL’), then make it the default. I further believe it would be best if MySQL would not allow changes to sql_mode. Not globally and certainly not per session. Choosing the stricter mode is better, I believe: errors such as overflow values should be reported to the application, not just swiped under the carpet.

Backward compatibility? Indeed a problem (inherent to the very existence of sql_mode). Perhaps allow one setting per installation. Perhaps just go for it.