How to Fix MySQL Error 1136: Column Count Doesn't Match Value Count at Row N

ERROR 1136 (21S01): Column count doesn't match value count at row N is raised when the number of values supplied in a VALUES clause row does not match the number of columns targeted by the INSERT statement. The error symbol is ER_WRONG_VALUE_COUNT_ON_ROW.

Impact

The entire INSERT statement is rejected — no rows are written, including any rows that preceded the offending row in a multi-row insert. The row number N in the message is 1-based and directly identifies which value list is mismatched, making it straightforward to locate the problem in large bulk inserts.

This error surfaces most often during bulk data loading, migrations, or when application code dynamically builds INSERT statements. ORMs that generate raw SQL (e.g., Sequelize, SQLAlchemy, Hibernate with native queries) will propagate the database exception to the application layer, typically as a driver-level SQL exception with SQLSTATE 21S01.

Common Causes

Explicit column list and VALUES row have different lengths. The column list names four columns but a VALUES row supplies three (or five) values.
Multi-row INSERT with inconsistent row sizes. One or more rows in a multi-row VALUES list has a different number of values than the others, often due to a missing or extra comma when the SQL is assembled programmatically.
Column list omitted and VALUES row doesn't match the full table definition. When no column list is given, MySQL expects exactly as many values as the table has columns. Adding or dropping a column without updating the insert query triggers this error.
INSERT ... SELECT where the SELECT returns a different number of columns than targeted. A wildcard SELECT * from a source table whose schema has drifted, or a manually written column list that is out of sync with the SELECT list.
Copy-paste error in SQL migration scripts. A row value tuple is accidentally duplicated or truncated in a long INSERT block inside a migration file.

Troubleshooting and Resolution Steps

Check the column and value counts for the reported row. The error message tells you exactly which row is wrong. Count the columns in your column list and the values in row N:

-- Correct: 3 columns, 3 values per row
INSERT INTO orders (customer_id, product_id, quantity)
VALUES
  (1, 101, 2),
  (2, 102, 1),   -- row 2 is fine
  (3, 103);      -- ERROR at row 3: only 2 values

Inspect the target table's column count. When no explicit column list is provided, verify how many columns the table actually has:

DESCRIBE orders;
-- or
SELECT COUNT(*) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders';

Always use an explicit column list. Omitting the column list makes INSERT brittle against schema changes. Explicitly listing columns makes mismatch errors much easier to diagnose:

-- Fragile: breaks when columns are added or reordered
INSERT INTO orders VALUES (1, 101, 2, NOW());

-- Robust: column list makes intent clear
INSERT INTO orders (customer_id, product_id, quantity, created_at)
VALUES (1, 101, 2, NOW());

For INSERT ... SELECT, verify the column counts match. Align the target column list with the SELECT list explicitly rather than relying on SELECT *:

-- Risky: SELECT * breaks if source schema changes
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2024-01-01';

-- Safe: explicit column mapping
INSERT INTO orders_archive (customer_id, product_id, quantity, created_at)
SELECT customer_id, product_id, quantity, created_at
FROM orders
WHERE created_at < '2024-01-01';

Debug dynamically generated SQL. If application code assembles the INSERT, log the final SQL string before execution. Most MySQL drivers also expose the full query in the error object. Verifying the raw SQL directly in a MySQL client confirms whether the problem is in query generation.
Validate bulk-load scripts before running. For migration files with large INSERT blocks, a quick sanity check is counting commas or using a linter. The mysqlcheck utility and tools like pt-query-digest can also help catch structural issues.

Additional Information

Related error 1058 (ER_WRONG_VALUE_COUNT): Error 1058 is the single-row variant — it fires when the column count is omitted entirely and the value count simply doesn't match. Error 1136 includes the row number specifically to handle the multi-row case.
SQLSTATE 21S01 is the standard SQL cardinality violation state and maps to this error across MySQL and MariaDB.
MariaDB compatibility: MariaDB raises the same error 1136 with identical SQLSTATE 21S01 and the same message format.
ORM behavior: Most ORMs parameterize inserts and therefore never produce this error through their standard APIs. It typically appears only when native/raw SQL queries are used or when SQL is assembled as strings.
Strict SQL mode has no effect on this error — it is a hard structural error regardless of sql_mode settings.

Frequently Asked Questions

Why does the error say "row 3" when I only inserted one row? You are likely using a multi-row INSERT with multiple VALUES tuples separated by commas. MySQL counts each tuple as a separate row. The number in the error identifies the specific tuple (1-based) whose value count doesn't match.

Can I insert the valid rows and skip the bad one? No. MySQL rejects the entire INSERT statement when any row fails with 1136. You must fix the mismatched tuple before any rows are written. Unlike some constraint errors, there is no INSERT IGNORE workaround for a structural mismatch like this.

The error appeared after I added a column to the table — why? If your INSERT omits the column list (e.g., INSERT INTO t VALUES (...)), MySQL expects a value for every column in the table. Adding a new column increases the expected count, breaking any bare VALUES inserts. Adding an explicit column list to your query fixes this immediately without requiring any schema rollback.

Does this error occur with LOAD DATA INFILE? No. LOAD DATA INFILE uses a different code path and reports field count mismatches differently (typically as warnings or a distinct error). Error 1136 is specific to INSERT statements using VALUES or SELECT.