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

Question

I have the following existing table in a mysql database:

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| f1                  | int(11)       | NO   | PRI | NULL    | auto_increment |
| field2              | int(11)       | NO   | MUL | NULL    |                |
| field3              | int(11)       | YES  |     | NULL    |                |
| field4              | int(11)       | YES  |     | NULL    |                |
| field6              | varchar(64)   | YES  |     | NULL    |                |
| field7              | varchar(16)   | YES  |     | NULL    |                |
| field8              | varchar(32)   | YES  |     | NULL    |                |
| field9              | varchar(128)  | YES  |     | NULL    |                |
| field10             | varchar(128)  | YES  |     | NULL    |                |
| field11             | varchar(128)  | YES  |     | NULL    |                |
| field12             | varchar(64)   | YES  |     | NULL    |                |
| field13             | varchar(32)   | YES  |     | NULL    |                |
| field14             | varchar(32)   | YES  |     | NULL    |                |
| field15             | int(11)       | YES  | MUL | NULL    |                |
| field16             | date          | YES  |     | NULL    |                |
| field17             | date          | YES  |     | NULL    |                |
| field18             | int(11)       | YES  | MUL | NULL    |                |
| field19             | varchar(64)   | YES  |     | NULL    |                |
| field20             | varchar(64)   | YES  |     | NULL    |                |
| field21             | varchar(16)   | YES  |     | NULL    |                |
| field22             | varchar(20)   | YES  |     | NULL    |                |
| field23             | varchar(1000) | YES  |     | NULL    |                |
| field24             | int(11)       | NO   | MUL | NULL    |                |
| field25             | int(11)       | NO   |     | 0       |                |
| field26             | decimal(19,2) | YES  |     | 0.00    |                |
| field27             | decimal(19,2) | YES  |     | 0.00    |                |
| field28             | int(11)       | YES  | MUL | NULL    |                |
| field29             | int(11)       | YES  | MUL | NULL    |                |
| field30             | varchar(128)  | YES  |     | NULL    |                |
| field31             | varchar(128)  | YES  |     | NULL    |                |
| field32             | varchar(16)   | YES  |     | NULL    |                |
| field33             | int(11)       | YES  |     | NULL    |                |
| field34             | int(11)       | YES  |     | NULL    |                |
| field35             | varchar(128)  | YES  |     | NULL    |                |
| field36             | int(11)       | YES  | MUL | NULL    |                |
| field37             | int(11)       | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

I try the following statement to add another row and I'm getting the following error:

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

Here are the ways I've tried to insert the row into the table:

insert into table (Field, Type, Null, Key, Default, Extra) VALUES ("contract_expiration", "date", "YES", "", "NULL", "");

insert into table VALUES ('contract_expiration','date','YES','','NULL','');

Both return the same error. There are no triggers on the table, I'm not sure what's going on.

Any suggestions? I'm relatively new to mysql administration, I know a bit but this has me stumped and searches for solutions have turned up nothing.

Any help that could be provided would be MUCH appreciated!

Answer 1

NULL is not a valid field name in:

insert into `table`(Field, Type, Null, Key, Default, Extra) 
    VALUES ("contract_expiration", "date", "YES", "", "NULL", "");

And Key and default are reserved words. Try this:

insert into `table`(Field, `Type`, `Null`, `Key`, `Default`, Extra) 
    VALUES ("contract_expiration", "date", "YES", "", "NULL", "");

Answer 2

mysql> desc classroom;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| clId    | int(11)     | NO   | PRI | NULL    | auto_increment |
| clFName | varchar(30) | NO   |     | NULL    |                |
| clSName | varchar(10) | NO   |     | NULL    |                |
| clCapc  | int(3)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from classroom;
+------+------------+---------+--------+
| clId | clFName    | clSName | clCapc |
+------+------------+---------+--------+
|    1 | Classroom1 | cl1     |    100 |
|    2 | 2          | 2       |      2 |
|    3 | 3f         | 3s      |      3 |
|    4 | 3f         | 3s      |      3 |
|    5 | class4     | class4  |    100 |
+------+------------+---------+--------+
5 rows in set (0.00 sec)

I also have same error

mysql> insert into classroom values('Gudadhe', 'Akash', 20);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

This error can be solved by specifying column names before inserting directly as follows

By writing classroom(clFName, clSName, clCapc) we are specifying columns into which we want to insert values

mysql> insert into classroom(clFName, clSName, clCapc) values('Gudadhe', 'Akash', 20);
Query OK, 1 row affected (0.06 sec)