CODEWITHSUNDEEP
phpmysqlsqllaravellaravel-4
Chris Farrugia
Chris Farrugia

Reputation: 1046

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

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.

Upvotes: 7

Views: 24229

Answers (3)

DBC Media Services
DBC Media Services

Reputation: 1

If the database column already contains a value of different data type not the same with the one you are changing to can throw error 1366, for example if a string value is already in one column of the table you are changing to tinyint. I once ran into this.

Upvotes: 0

Chris Farrugia
Chris Farrugia

Reputation: 1046

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.

Upvotes: 9

albertdiones
albertdiones

Reputation: 733

why not try type casting it to integer before inserting?

array( 'baths_full' => (int) $baths_full, ... )

like

"VALUES(".((int) $baths_full).")"

Upvotes: -4

Related Questions