🚨 MySQL Error: 'ERROR 1136 (21S01): Column count doesn't match value count at row 1' — Cause, Fix, and Troubleshooting Guide

Overview

ERROR 1136 (21S01) means an INSERT (or a row in a multi-row insert) supplies a different number of values than the number of target columns:

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

The “at row 1” points to the first offending tuple in the VALUES list. It is almost always a hand-written or generated INSERT where the column list and the value list drifted out of sync, or an implicit insert (no column list) that omits or adds a value after a schema change.

Symptoms

• A bulk INSERT ... VALUES (...),(...) fails on a specific row number.
• Inserts that worked before break after an ALTER TABLE ADD COLUMN.
• Generated SQL from an ORM or export tool fails on import.
• A LOAD DATA/CSV import complains about mismatched counts.

Common Root Causes

1. Explicit column list length differs from values

INSERT INTO users (id, name, email)
VALUES (1, 'Ada');

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

Three columns, two values.

2. Implicit insert after a schema change

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com');

If someone added a created_at column, the table now expects four values and this fails. Implicit inserts are brittle for exactly this reason.

3. A stray or missing comma in a multi-row insert

One tuple in a long VALUES (...),(...),(...) list has an extra/missing value; the “row N” tells you which.

4. CSV column mismatch

LOAD DATA with a file whose column count differs from the table (or the (col_list) given).

How to diagnose

Compare the table’s real column list against the statement:

SELECT ORDINAL_POSITION, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users'
ORDER BY ORDINAL_POSITION;

Count the columns in your INSERT (...) list and the values in the failing tuple — the “at row N” indicates which one to inspect.

Fixes

Always name columns explicitly and match value count exactly:

INSERT INTO users (id, name, email)
VALUES (1, 'Ada', 'ada@example.com');

Let defaults fill new columns by simply not listing them:

INSERT INTO users (id, name, email)
VALUES (1, 'Ada', 'ada@example.com');   -- created_at uses its DEFAULT

For LOAD DATA, state the columns so file width is explicit:

LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(id, name, email);

What to watch out for

• Prefer explicit column lists in all application inserts; implicit INSERT INTO t VALUES (...) silently breaks the day someone adds a column.
• In multi-row inserts, the reported row number is 1-based within the VALUES list — jump straight to that tuple.
• Watch for values containing commas inside strings; an unescaped comma looks like an extra value.
• Regenerate ORM/migration SQL after schema changes rather than reusing cached statements.

Related

• ERROR 1064: SQL syntax error — a stray comma often surfaces as 1064 instead of 1136.
• ERROR 1054: Unknown column — the sibling error when a named column does not exist.
• ERROR 1406: Data too long for column — a value-level failure once the counts line up.