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
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.
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.
This won’t affect every project, but some of my clients have older applications which are a little looser with their types – this helps until you can make things better!