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

MySQLMySQLi Database

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)
raja
Published on 26-Feb-2019 09:49:20
Advertisements