MySQL INSERT fails: column count does not match value count at row 1
ERROR 1136 (21S01): Column count doesn't match value count at row 1Short answer
MySQL error 1136 means one row in your INSERT has a different number of values than the columns it targets. Count the names in the column list, count the values in each row, and make them equal. Without a column list, every column of the table needs a value, in table order.
Fix it fast
1Find out which cause you have
Print the table's columns, then count them against your statement. The 'at row N' part tells you which row of a multi-row insert is wrong.
DESCRIBE tbl_name;2Match what you see, then jump to its fix
- 1
The INSERT has a column list and the numbers differ, often after a column was added or a value pasted inMore or fewer values than the named columnsFix - 2
INSERT INTO tbl VALUES (...) stopped working after someone altered the tableNo column list, and the table has more columns than you gaveFix - 3
A single parenthesis holds the values of several rowsA multi-row insert grouped into one long listFix
- 1
What does the error look like?
ERROR 1136 (21S01): Column count doesn't match value count at row 1SQLSTATE 21S01
Cause 1 of 3
More or fewer values than the named columns
1Check
With a column list, MySQL requires a value for each named column from the VALUES list, VALUES ROW() list or a SELECT. Each values list must contain exactly as many values as are inserted per row.
-- 3 names, 4 values: invalid
INSERT INTO tbl_name (a, b, c) VALUES (1, 2, 3, 4);2Fix
Make the counts equal: drop the extra value, or add the missing column name.
INSERT INTO tbl_name (a, b, c) VALUES (1, 2, 3);3Verify
The statement runs and reports the number of rows added.
SELECT * FROM tbl_name ORDER BY a DESC LIMIT 1;Confirmed by dev.mysql.com
Cause 2 of 3
No column list, and the table has more columns than you gave
1Check
Without a column list, MySQL needs values for every column in the table. DESCRIBE shows the order.
DESCRIBE tbl_name;
SHOW CREATE TABLE tbl_name\G2Fix
Name the columns you fill, and let the rest use their defaults. Use DEFAULT for a single column if you need it in the list.
INSERT INTO tbl_name (a, b, c) VALUES (1, 2, DEFAULT);3Verify
Run the insert again.
SELECT COUNT(*) FROM tbl_name;Confirmed by dev.mysql.com
Cause 3 of 3
A multi-row insert grouped into one long list
1Check
The manual's example of an invalid statement is one list of nine values where three lists of three values were meant. Look for a missing closing and opening parenthesis between rows.
-- wrong: one list of six values
INSERT INTO tbl_name (a, b, c) VALUES (1, 2, 3, 4, 5, 6);2Fix
Wrap each row in its own parentheses, separated by commas.
INSERT INTO tbl_name (a, b, c) VALUES (1, 2, 3), (4, 5, 6);3Verify
Both rows appear.
SELECT * FROM tbl_name WHERE a IN (1, 4);Confirmed by dev.mysql.com
How do I stop it coming back?
- Always name the columns in an INSERT; positional inserts break when the table changes.
- In JDBC, build the placeholder list from the same array as the column list so the two cannot drift.
- Run new migrations against a copy of the schema first.
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
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix