Column count doesn't match value count in MySql

When we try to insert/update data that exceeds column length, the default behavior should be to truncate the value and insert/update the column; though this was there in MySQL 4 by default but strangely in MySQL 5.x this feature is disabled by default. In this article will highlight how we can resolve this problem.

Cross Join Queries in MySQL

To Highlight more on this check out the error message below:

mysql> create table user(id int PRIMARY KEY AUTO_INCREMENT, name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user values(“Hello World”);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

In MySQL 5.x their are various Server SQL Modes and by default it is set to STRICT MODE. This does not allow to insert/update values exceeding column length. To overcome this their are two ways.

Stored Procedures in MySQL

1) Set Value in Global Variable
2) Add Environment variable in my.cnf file

Setting Global Variable in MySQL:
You can check for mode set in your MySql by executing following query. If you are using MySQL5 it will return empty unless explicitly set.

Now to set it we will execute following query.

Adding Environment Variable in my.cnf file:
In this open my.cnf located at /etc and check for sql-mode text. Replace that text with following value.
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
So after adding this line your my.cnf will look like:

Finally restart the MySQL daemon to reflect the changes.

Tagged . Bookmark the permalink.

3 Responses to Column count doesn't match value count in MySql

  1. Akeel khambati says:

    hello sir akeel here,
    sir, for this particular post “Column count doesn’t match value count in MySql”, can u please specify what do u mean by ‘column length’ because i am a bit confused by that word. especially looking at the example that u have given above, i am getting more confused….
    thank you.

  2. Akeel khambati says:

    Akeel khambati :
    hello sir akeel here,
    sir, for this particular post “Column count doesn’t match value count in MySql”, can u please specify what do u mean by ‘column length’ because i doubt that u have got this 1 in the wrong sense… actually the above specified error occurs when ur fields in the db and the fields u have entered data for , don’t match… e.g u have 2 fields in db but u enter data for only 1 field … ya ofcourse if u specify which fields for which u want to enter data in the insert statement, then its ok… ….
    thank you.

  3. Akeel khambati says:

    dont count the 1st comment please … i cant find a way to delete it…..:-)

Leave a Reply