Fix for MySQL ERROR 1406: Data too long for column” but it shouldn't be?

This error can occur if you try to set data higher than the allowed limit. As an example, you cannot store a string in a column of type bit because varchar or string takes size higher than bit data type.

You need to use the following syntax for bit type column:

anyBitColumnName= b ‘1’
OR
anyBitColumnName= b ‘0’

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table IncasesensitiveDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.70 sec)

Insert some records in the table using insert command. The query to insert record is as follows:

mysql> insert into ErrorDemo(Name,isStudent) values('John',1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Sam',0);
Query OK, 1 row affected (0.21 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Mike',0);
Query OK, 1 row affected (0.16 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Larry',1);
Query OK, 1 row affected (0.23 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Carol',1);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Robert',0);
Query OK, 1 row affected (0.17 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('James',1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Bob',1);
Query OK, 1 row affected (0.19 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('David',1);
Query OK, 1 row affected (0.15 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Ricky',0);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from ErrorDemo;

The following is the output:

+----+--------+-----------+
| Id | Name   | isStudent |
+----+--------+-----------+
|  1 | John   |           |
|  2 | Sam    |           |
|  3 | Mike   |           |
|  4 | Larry  |           |
|  5 | Carol  |           |
|  6 | Robert |           |
|  7 | James  |           |
|  8 | Bob    |           |
|  9 | David  |           |
| 10 | Ricky  |           |
+----+--------+-----------+
10 rows in set (0.00 sec)

The actual sample output snapshot is as follows:

The error is the following as discussed above. It gets generated in the below query:

mysql> update ErrorDemo set isStudent='1' where Id=9;
ERROR 1406 (22001): Data too long for column 'isStudent' at row 1

To avoid the above error, you need to prefix b before ‘1’. Now the query is as follows:

mysql> update ErrorDemo set isStudent=b'1' where Id=9;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Check the table records once again using select statement. The query is as follows:

mysql> select *from ErrorDemo;

The following is the output:

+----+--------+-----------+
| Id | Name   | isStudent |
+----+--------+-----------+
|  1 | John   |           |
|  2 | Sam    |           |
|  3 | Mike   |           |
|  4 | Larry  |           |
|  5 | Carol  |           |
|  6 | Robert |           |
|  7 | James  |           |
|  8 | Bob    |           |
|  9 | David  |           |
| 10 | Ricky  |           |
+----+--------+-----------+
10 rows in set (0.00 sec)

The actual sample output snapshot is as follows:

Look at the is Student column.

Now we will update the same id with the value 0. This will give a blank value with corresponding Id. The query is as follows:

mysql> update ErrorDemo set Name='Maxwell', isStudent=b'0' where Id=9;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Check the record of particular row updated above. Here is the Id 9. Now row has been updated with record Id 9. The query is as follows:

mysql> select *from ErrorDemo where Id=9;

The following is the output:

+----+---------+-----------+
| Id | Name    | isStudent |
+----+---------+-----------+
|  9 | Maxwell |           |
+----+---------+-----------+
1 row in set (0.00 sec)