MySQL Error 1136

Error number

1136

SQLSTATE

21S01

Symbol

ER_WRONG_VALUE_COUNT_ON_ROW

Product

MySQL Server

Versions

All MySQL versions (checked on MySQL 8.0.46)

Category

Syntax & Names

Last reviewed

24 October 2026

What it means

Error 1136 means an INSERT or REPLACE supplies a different number of values than the number of columns it is filling.

For each row, MySQL pairs the values with the target columns by position: the first value goes into the first column, and so on. The target columns are the list in brackets after the table name or, when there is no list, every column of the table in table order. If a row has more or fewer values than that, MySQL cannot pair them and rejects the whole statement with 1136; no rows are inserted.

at row n counts value lists from 1, so at row 2 in a multi-row INSERT points at the second bracketed list. For INSERT ... SELECT the count compares the SELECT list with the target columns. The most common surprise is an INSERT without a column list that worked for months and then fails, because a migration added a column to the table.

Common causes

• An INSERT without a column list after a column was added to (or dropped from) the table.
• A missing or extra value in one row of a multi-row INSERT, often in data generated by a script or pasted from a spreadsheet.
• Two values merged into one string by a misplaced quote: 'Ali, York' is one value, not two.
• INSERT INTO target SELECT * FROM source where the two tables do not have the same number of columns.
• A column list and a SELECT list that were edited separately, so one has more entries than the other.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT ordinal_position, column_name, is_nullable, column_default, extra
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'customers'
ORDER BY ordinal_position;

SELECT table_name, COUNT(*) AS column_count
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name IN ('customers', 'staging_customers')
GROUP BY table_name;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO customers (id, name, city)
VALUES (2, 'Ali', 'York');

INSERT INTO customers (id, name, city)
VALUES (2, 'Ali', 'York'),
       (3, 'Zoe', DEFAULT),
       (4, 'Sam', 'Hull');

INSERT INTO customers (id, name, city, email)
SELECT id, name, city, email
FROM staging_customers;

Examples

An INSERT without a column list after a new column was added

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(50));
INSERT INTO customers VALUES (1, 'Sara', 'Leeds');  -- works

ALTER TABLE customers ADD COLUMN email VARCHAR(200);

INSERT INTO customers VALUES (2, 'Ali', 'York');  -- now 4 columns, 3 values

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

One short row in a multi-row INSERT

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(50));

INSERT INTO customers (id, name, city)
VALUES (2, 'Ali', 'York'),
       (3, 'Zoe'),
       (4, 'Sam', 'Hull');

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

A misplaced quote turns two values into one

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(50));

INSERT INTO customers (id, name, city) VALUES (2, 'Ali, York');

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

INSERT ... SELECT * between tables of different width

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100), city VARCHAR(50), email VARCHAR(200));
CREATE TABLE staging_customers (id INT, name VARCHAR(100), city VARCHAR(50), email VARCHAR(200), source VARCHAR(20));
INSERT INTO staging_customers VALUES (5, 'Kim', 'Bath', 'kim@example.com', 'csv');

INSERT INTO customers SELECT * FROM staging_customers;

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

How to prevent it

Name the columns in every INSERT, REPLACE and INSERT ... SELECT in application code and scripts, and avoid SELECT * as the source of an insert. Then adding a column that has a default or allows NULL never breaks existing writes.

When you generate multi-row INSERTs from files or spreadsheets, validate that every record has the expected number of fields before building the SQL, and pass values as parameters so a comma or quote inside a value cannot change the count.

Common questions

Learn more on CodeWithSQL

• DML commands (INSERT, UPDATE, DELETE)
• SQL commands: DDL, DML, DCL, TCL and DQL

Official documentation

• INSERT statement
• INSERT ... SELECT statement
• MySQL 8.0 server error message reference

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.