Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL INSERT fails: column count does not match value count at row 1

ErrorERROR 1136 (21S01): Column count doesn't match value count at row 1

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

  • Applies to MySQL 8.4, MySQL 8.0, MariaDB 11, Connector/J 8
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

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

    1. 1The 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. 2INSERT INTO tbl VALUES (...) stopped working after someone altered the tableNo column list, and the table has more columns than you gaveFix
    3. 3A single parenthesis holds the values of several rowsA multi-row insert grouped into one long listFix

What does the error look like?

  • ERROR 1136 (21S01): Column count doesn't match value count at row 1
  • SQLSTATE 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

Did this fix it?

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\G

2Fix

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

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