MySQL Error 1136 at Row 2: Fix a Mismatched VALUES Row

When MySQL reports ERROR 1136 (21S01): Column count doesn't match value count at row 2, the second tuple in a multi-row VALUES list has a different number of values than the INSERT column list. MySQL requires every tuple to match that list. The row 2 label identifies the tuple where the mismatch occurs; it does not tell you how many rows were written, so check the target before retrying a batch. See the MySQL error reference.

Find the tuple with the wrong number of values

This statement names three target columns, but the second tuple supplies only two values:

CREATE TABLE products (
  product_code VARCHAR(20) PRIMARY KEY,
  price DECIMAL(10, 2) NOT NULL,
  stock INT NOT NULL DEFAULT 0
);

INSERT INTO products (product_code, price, stock)
VALUES
  ('A-100', 19.99, 4),
  ('B-200', 9.99);

Count the expressions in each tuple against the named columns. The first tuple has three values; the second has two.

Correct the row shape

Provide a valid value for the third column. Because this example defines a default for stock, the second tuple can use DEFAULT:

INSERT INTO products (product_code, price, stock)
VALUES
  ('A-100', 19.99, 4),
  ('B-200', 9.99, DEFAULT);

If the omitted field has no default, provide a value that matches the data model. Use NULL only when the column allows it and a missing value is meaningful. Otherwise, split the rows into separate INSERT statements with their own column lists.

Check generated multi-row data

Confirm the target schema and insert list agree:

SHOW CREATE TABLE products;

If application code builds the VALUES tuples, validate each row before generating SQL. For example, in Python:

expected_columns = 3

for row_number, values in enumerate(rows, start=1):
    if len(values) != expected_columns:
        raise ValueError(
            f"row {row_number} has {len(values)} values; "
            f"expected {expected_columns}"
        )

For the general causes of Error 1136, including INSERT ... SELECT, see MySQL Error 1136: Column and Value Counts Do Not Match. For basic multi-row syntax, see MySQL INSERT Multiple Rows.