Laravel insert fails with 'Incorrect integer value' for a column (MySQL 1366)
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'column_name' for column 'id' at row 1Short answer
MySQL error 1366 means a value does not fit the column type, such as a string going into an integer column. In strict SQL mode MySQL raises it as an error; without strict mode it only warns and stores an adjusted value. Find the bad value in the failing query, send the right type, and compare sql_mode across servers.
Fix it fast
1Find out which cause you have
Read the full error. It names the bad value and the column. Then look at the type of that column in the table.
mysql -e "SHOW CREATE TABLE role_user\G" my_db2Match what you see, then jump to its fix
What does the error look like?
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'column_name' for column 'id' at row 1Incorrect integer value: 'abc' for column 'i' at row 1
Cause 1 of 2
A string or array is sent where the column expects an integer
1Check
The value in the message is what reached MySQL. Find the code that writes to that table and log the values it passes, for example an id taken from the wrong variable or a column name passed instead of a value.
grep -rn "attach(\|sync(\|create(" app | head
mysql -e "SHOW CREATE TABLE role_user\G" my_db2Fix
Pass the value the column holds. For a foreign key, that is the numeric id of the related record, not a name or a whole array.
// wrong: a column name or array in place of an id
$user->roles()->attach('role_id');
// right: the numeric id
$user->roles()->attach($role->id);3Verify
Run the request again; the insert succeeds and the row has the id.
mysql -e "SELECT * FROM role_user ORDER BY 1 DESC LIMIT 3" my_dbConfirmed by dev.mysql.com
Cause 2 of 2
Strict SQL mode differs between your machines
1Check
Without strict mode, MySQL inserts an adjusted value and produces a warning. With STRICT_TRANS_TABLES or STRICT_ALL_TABLES the same statement is an error, and the default sql_mode in 8.4 includes STRICT_TRANS_TABLES.
mysql -e "SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode"2Fix
Fix the data first. A strict server is the correct one: a non-strict mode only hides bad values by storing adjusted ones. Make the other environment match, with the SQL below or in its MySQL option file.
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';
-- or set sql-mode in my.cnf and restart MySQL3Verify
Both environments report strict mode and reject the same bad value.
mysql -e "SELECT @@SESSION.sql_mode"Confirmed by dev.mysql.com
How do I stop it coming back?
- Cast and validate request input before it reaches the database.
- Run local, staging and production with the same sql_mode.
- Do not turn strict mode off to silence the error; it hides wrong data.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Back end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fixBack end
Laravel migration cannot drop a column that has a foreign key
Error on rename of '%s' to '%s' (errno: %d - %s)
MySQL refuses to drop a column while a foreign key constraint still uses it.
2 causes, each with a fixBack end
Laravel: distinct() and count() give the total instead of the unique count
select('city')->distinct()->count() ignores the select
Laravel builds the count from the column you pass to count(), not from select().
2 causes, each with a fixBack end
Laravel Eloquent query for a column that is X or NULL
where('column', null) returns no rows
Wrap the pair in a closure: where(function ($q) { $q->where('col', $x)->orWhereNull('col'); }).
3 causes, each with a fixBack end
Laravel insert fails with Invalid datetime format 1292 Incorrect datetime value
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value
MySQL ran in strict mode and refused a date or time value that it cannot store.
3 causes, each with a fixBack end
php artisan migrate stops with could not find driver
could not find driver (Connection: mysql, SQL: select * from information_schema.tables ...)
Laravel talks to the database through a PDO driver, and this error means the PHP that runs artisan has none for your DB_CONNECTION.
3 causes, each with a fix