MySQL 5.5 to 5.6 - Type Strictness

If you're upgrading MySQL from 5.5 to 5.6, you may encounter some issues when trying to insert and update data. For instance, trying to insert an empty string into a decimal value may have worked for you in 5.5, but now throws this in 5.6:

Incorrect decimal value: '' for column 'my_decimal_column' at row 1

MySQL 5.6 changed some of its default settings, and one of those changes is how type strictness is handled by default. If you're encountering this issue, the most permanent solution is to change the sql_mode in your MySQL configuration.

This is the default configuration for MySQL 5.6

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Removing STRICT_TRANS_TABLES from the configuration file and restarting MySQL will force it to behave like 5.5, and your strictness issues will be resolved.

Finding your configuration

Where the configuration file lives is dependent on your OS and where you've installed MySQL. For instance, on my Mac OS X machine I used a utility called Homebrew to install MySQL and my settings are located at /usr/local/opt/mysql/my.cnf. Googling for your MySQL configuration location should be enough to find where your respective configuration will live.

Note

Whether you should be removing this setting is a different question :). Once you've resolved your issues, it's a good idea to re-enable STRICT_TRANS_TABLES so that you don't encounter this again moving forward, or when upgrading MySQL in the future.