Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
4K+ Views
