Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Laravel insert fails with 'Incorrect integer value' for a column (MySQL 1366)

ErrorSQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'column_name' for column 'id' at row 1

Short 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.

  • Applies to Laravel 11, Laravel 12, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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_db
  2. 2Match what you see, then jump to its fix

    1. 1The quoted value in the error is text, a column name or a JSON string, not a numberA string or array is sent where the column expects an integerFix
    2. 2The same code works on one environment (warning only) and throws 1366 on anotherStrict SQL mode differs between your machinesFix

What does the error look like?

  • SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'column_name' for column 'id' at row 1
  • Incorrect 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_db

2Fix

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_db

Confirmed by dev.mysql.com

Did this fix it?

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 MySQL

3Verify

Both environments report strict mode and reject the same bad value.

mysql -e "SELECT @@SESSION.sql_mode"

Confirmed by dev.mysql.com

Did this fix it?

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:

Related fixes

Back 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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.