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

What it means

An INSERT fills a list of columns with a list of values, one for one. Error 1136 means the two lists have different lengths, so the server can’t tell which value goes where and inserts nothing.

If the INSERT names no columns, the list is every column of the table, in table order, including the AUTO_INCREMENT id and any column added since the statement was written.

“At row 1” is the position in a multi-row VALUES list: at row 2 means the second (…) group is the odd one out. For INSERT … SELECT, it’s the SELECT’s column count that doesn’t match.

Common causes

1. No column list, and the table has more columns than the values: usually the id, or a column someone added in a migration. The statement worked until the table changed.
2. A missing comma between two strings. VALUES ('bob@example.com' 'Bob') is one value: MySQL joins string literals written next to each other, so it reads 'bob@example.comBob'.
3. One row in a multi-row insert with a value too many or too few, often from generated SQL or a CSV line with an extra comma.
4. INSERT … SELECT whose SELECT * returns a different number of columns from the target table.

How to fix it

Name the columns

Always list the columns you fill. The statement then keeps working when columns are added, and it’s easy to count:

INSERT INTO customers (email, name) VALUES ('bob@example.com', 'Bob');

Columns you leave out get their default, or NULL if they allow it; one that’s NOT NULL with no default gives “Field doesn’t have a default value”.

Check the table’s columns

SHOW COLUMNS FROM customers;

If you really want to insert without a column list, give a value for every column in that order, with NULL or DEFAULT for the AUTO_INCREMENT id:

INSERT INTO customers VALUES (NULL, 'bob@example.com', 'Bob');

Or use the SET form

MySQL and MariaDB accept an INSERT that pairs each column with its value, which can’t get out of step:

INSERT INTO customers SET email = 'cy@example.com', name = 'Cy';

INSERT … SELECT

List the columns on both sides instead of SELECT *:

INSERT INTO customers_archive (id, email, name)
SELECT id, email, name FROM customers WHERE created_at < '2025-01-01';

Reproduce it

On MySQL 8.4.11, with customers (id int AUTO_INCREMENT PRIMARY KEY, email varchar(100) NOT NULL, name varchar(100)):

INSERT INTO customers VALUES ('bob@example.com', 'Bob');
INSERT INTO customers (email, name) VALUES ('bob@example.com');
INSERT INTO customers (email, name) VALUES ('bob@example.com', 'Bob'), ('cy@example.com');
INSERT INTO customers (email) VALUES ('bob@example.com', 'Bob');
INSERT INTO customers (email, name) SELECT 'x@example.com';
INSERT INTO customers (email, name) VALUES ('bob@example.com' 'Bob');

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

SELECT 'bob@example.com' 'Bob' returned bob@example.comBob, which is why the last one counts as a single value. VALUES (NULL, 'bob@example.com', 'Bob') without a column list, and the SET form, both inserted their row.

MariaDB 11.4.13 gave the same errors at the same rows.

In Inlet

Inlet’s query editor completes column names from the live schema, which helps when writing out a column list. When a statement fails, Inlet shows the error and links to this page; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the statement from the table’s schema.