Error 1406 mysql

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)

1. How to configure phpMyAdmin with automatic login by setting auth_type to config.

2. How to create new users in MySQL and control their permissions for better security.

3. How to generate sitemaps dynamically using PHP.

4. How to perform simple SELECT statements in SQL to communicate with SQL databases.

5. The error happens when importing database backups using the SOURCE command, either because you got the path wrong, or because you used the command incorrectly.

More in: MySQL

In mysql, ERROR 1406 (22001):data too long for column
: DEFAULT CHARSET=utf8 is added at the end of the created table

drop table if exists sys_user;
create table sys_user (
  user_id           bigint(20)      not null auto_increment    comment 'User ID',
  dept_id           bigint(20)      default null comment       'Department ID',
  login_name        varchar(30)      not null comment         'login account',
  user_name       varchar(30)       not null comment       'user nickname',
  user_type       varchar(2)       default '00' comment       'user type (00 system user)',
  email       varchar(50)       default '' comment       'user email',
  phonenumber varchar(11)       default '' comment       'phone number',
  sex       char(1)       default '0' comment       'User gender (0 male 1 female 2 unknown)',
  avatar       varchar(100)       default '' comment       'avatar path',
  password       varchar(50)       default '' comment       'password',
  salt       varchar(20)       default '' comment       'salt encryption',
  status       char(1)       default '0' comment       'Account status (0 normal 1 disabled)',
  del_flag       char(1) default '0' comment 'Delete flag (0 means present 2 means deleted)',
  login_ip       varchar(50)       default '' comment 'last login ip',   login_date datetime comment 'last login',
  create_by       varchar(64)       default '' comment 'creator',   create_time datetime comment 'create time',
  update_by       varchar(64)       default '' comment 'updater',  update_time datetime comment 'update time',
  comment       varchar(500)       default null comment 'Remarks', primary key (user_id)
) engine=innodb auto_increment=100 comment = 'user information table' DEFAULT CHARSET=utf8;

insert into sys_user values(1,  103, 'admin', 'admin', '00', '[email protected]', '15888888888', '1', '', '123456', '111111', '0', '0', '127.0.0.1', '2018-03-16 11-33-00', 'admin', '2018-03-16 11-33-00', 'ry', '2018-03-16 11-33-00', 'ADMIN');
insert into sys_user values(2,  105, 'ry',    'admin', '00', '[email protected]',  '15666666666', '1', '', '123456', '222222', '0', '0', '127.0.0.1', '2018-03-16 11-33-00', 'admin', '2018-03-16 11-33-00', 'ry', '2018-03-16 11-33-00', 'TEST');