If you find yourself getting an error that reads something like “ERROR 1136 (21S01): Column count doesn’t match value count at row” in MariaDB, it’s probably because you’ve specified the wrong number of expressions for the number of columns in the column when attempting to insert data into a table.

In other words, you’re trying to insert too many columns or not enough columns.

To fix, adjust your INSERT statement to insert the correct number of values into the table.

You can also name the columns in your INSERT statement so that MariaDB knows which columns each expression needs to be inserted into.

Example of Error

Suppose we have the following table:

+-------+---------+---------+
| PetId | PetName | PetType |
+-------+---------+---------+
|     1 | Fluffy  | Cat     |
|     2 | Tweet   | Bird    |
+-------+---------+---------+

The following code will cause the error:

INSERT INTO Pets VALUES ( 3, 'Wag', 'Dog', 'Brown' );

Result:

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

In this case, I tried to insert data for four columns into a table that only has three columns.

We’ll get the same error if we try to insert too few columns:

INSERT INTO Pets VALUES ( 3, 'Wag' );

Result:

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

Solution 1

The obvious solution is to insert the correct number of rows. Therefore, we could rewrite our code as follows:

INSERT INTO Pets VALUES ( 3, 'Wag', 'Dog' );

Result:

Query OK, 1 row affected (0.010 sec)

Solution 2

Another way of doing it is to explicitly name the columns for which we want to insert data. This technique can be used to insert less columns than are in the table.

Example:

INSERT INTO Pets ( PetId, PetName ) VALUES ( 3, 'Wag' );

Result:

Query OK, 1 row affected (0.005 sec)

This method may result in a different error if there are any constraints that require a value to be passed for that column (for example, if the table has a NOT NULL constraint on that column). Therefore, you’ll need to ensure you’re complying with any constraints on the column when doing this.