MySQL NULL values during INSERT…SELECT

I was surprised to find that when using an INSERT…SELECT… statement in MySQL that NULL values are converted to 0 when inserting into a float column.

I had a query running live that was working fine, but locally it was failing, I was getting Column ‘test’ cannot be null.

The code to me looked like it should be failing though, as I was selecting NULL values and using them in an INSERT

After some experimenting, with the code below, I worked out the SELECT converts the NULL, but stating NULL without a select does through the error.

I did some Googling and found this Server Fault post which explains the answer and also the setting I needed to set locally to get the DB to match the production server: STRICT_ALL_TABLES

https://serverfault.com/a/658645/65594

Removing STRICT_ALL_TABLES from the sql-mode setting in my.cnf got my dev setup to convert the NULL to 0, to match my production setup, although I’m not sure I really like this behaivior and will look to just write better code to handle the NULL values in the future.

This has all come about from updating the dev and production servers without checking that the “latest” version I pull in is the same on both environments.

There are quite a few differences between the default setups of MySQL 5.6 and 5.7

You can find out more about the differences from mysql.com:

https://dev.mysql.com/doc/refman/5.7/en/faqs-migration.html#faq-mysql-how-migration-mysql
https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html