Incorrect integer value '' for a MySQL column that's integer and allow null?

Question

I'm working on inserting a CSV into a table. I don't have any control over what's in the CSV and a lot of the fields are blank. In my very first record for instance, the field "baths_full" is just empty (two commas back to back).

On my production server running MySQL 5.5.37, it inserts the record with the baths_full as an empty field. On my local machine running MySQL 5.6.19, it gives me the error:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'baths_full' at row 1 (SQL: insert into `listings_queue` (`

The weird thing is that the schema for the tables is identical. In fact, I used the export of the production machine to create the local database.

The field baths_full is set to TinyInt, unsigned, allow null, default null. One thing to add is that it looks like in the SQL insert statement Laravel is creating, it is treating null values as spaces. Regardless, my production machine runs the script without trouble but locally it won't run.

Solution

I found my problem. My local MySQL is running in strict mode. The answer from this thread (General error: 1366 Incorrect integer value with Doctrine 2.1 and Zend Form update) fixed it.

Comments

[Chris Farrugia]

Well, I'm using Laravel and it handles the insert automatically. I'm not doing raw SQL.

[albertdiones]

Then use the first one, before using the insert function (whatever it is)

[Wampie Driessen]

MVP: Answering his own question when he found the answer