When inserting values to a table in MySQL, you might run into this error:
Error Code: 1406. Data too long for column
That error message means you are inserting a value that is greater than the defined maximum size of the column.
The solution to resolve this error is to update the table and change the column size.
Example
We have a simple table employees:
CREATE TABLE employees (
id int(5),
full_name varchar(5)
);
And you insert the first row of data:
INSERT INTO employees
VALUES(1, 'Alex with an extra long full name, longer than 10');
Since the full_name value is longer than the defined length, MySQL will throw the error Data too long for column. To resolve that, modify the column size:
ALTER TABLE employees
MODIFY COLUMN full_name varchar(50);
Another workaround is to disable the STRICT mode, MySQL will automatically truncate any insert values that exceed the specified column width instead of showing the error. The trade-off is, you might not be aware that your data won’t fit and end up losing original data.
To disable the STRICT mode, you can:
- Edit your MySQL configuration file:
Open your my.ini (Windows) or my.cnf (Unix) file and look for “sql-mode”, and remove the tag STRICT_TRANS_TABLES
- Or execute this command:
SET @@global.sql_mode= '';
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Download TablePlus for Mac.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
How to deal with the 1406 (22001) Data too long for column error message.
4041 views
By. Jacob
Edited: 2020-08-29 11:02
ERROR 1406 (22001): Data too long for column
This error happens because you are trying to insert data that is longer than the column width.
There is at least a few solutions to this problem.
We can truncate the data, cutting off the data that goes beyond the column boundary; this is however not ideal, as it might lead to data loss and data corruption, and it does not deal with the underlying reason for the problem very effectively — it just moves it beyond the horizon. So, what can we do instead?
Assuming you got access to the PHP code, you should most likely be validating the data length before submitting it to the database. Doing this will ensure that the data is never too long for the column.
You can also make the column itself longer in order to fit more data into the column, this can be done with the alter table query:
alter table request_log modify column user_agent varchar(500);
The maximum size of a varchar column is 65.535 characters.
ERROR 1406 (22001): Data too long for column
The error happens because the input data was too long for the column in a database table.
For example, if you defined the column width with varchar(100), and the data is more than 100 characters long, you will get the error:
ERROR 1406 (22001): Data too long for column
This error can also occur when a script is attempting to insert a string in a bit column. For example, a developer might accidentally have written ‘1’ instead of 1 — when inserting bit values, the value should not be quoted, as this would cause MySQL to treat it as a string rather than a bit. A single character might be 8 bits long, while a single bit is just 1 bit long, and hence the error Data too long for column is triggered.
Solutions
To solve the problem, you should be sure that the data does not exceed the column width in the database table before submitting it. This can be done with a combination of strlen and substr
// If the data is longer than the column width, it is violently cut off! $user_agent ?? $_SERVER['HTTP_USER_AGENT']; if (strlen($user_agent) > 255) { $user_agent = substr($user_agent,0,255); }
We can also increase the width of the varchar column; in order to increase the width to 500 characters, we can execute the following SQL query:
alter table request_log modify column user_agent varchar(500);
Debugging errors in general
The problem when debugging these errors often is that the CMS you are using might not report the exact error on the front-end, so you will have to track down the error message manually. This can be difficult, because you need to find the exact line in your code where the error is triggered.
Sometimes you might not even be able to tell what exactly the error is, since the front-end error message that is shown is a catch-all 500 — Internal Server Error. So, in order to debug the problem, you will often have to dig through the error log file. If you are using Apache, the website error log will often be located at: /var/log/apache2/my_site_name_com-error.log
Of course, finding the relevant error in the log might prove difficult. However, if you are able to consistently reproduce the error by sending a specific request, you could simply filter for your own IP address:
grep 'xxx.xxx.xxx.xxx' /var/log/apache2/my_site_name_com-error.log
This should tell you the exact line where the error occurred in your PHP code, and allow you to do further debugging.
Another nice trick is to add a «test» request parameter, and then you can perform debugging even on the live server, without effecting users. I.e.:
if (isset($_GET['debugging'])) { var_dump($variable_to_debug); exit(); }
Finally, if you are worried about a user guessing the test parameter, you can just add a check for you IP address; this will avoid the risk that someone enters in the «debugging» parameter and sees something they should not.
-
How to configure phpMyAdmin with automatic login by setting auth_type to config.
-
How to create new users in MySQL and control their permissions for better security.
-
How to generate sitemaps dynamically using PHP.
-
How to perform simple SELECT statements in SQL to communicate with SQL databases.
-
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
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)
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');
This is an article which is showing a message generated in the process of inserting record in a table exist located in a database used by a Laravel web-based application development. The error is shown in the following output of page which is shown as follows :
Illuminate Database QueryException (22001) SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `first_name`, `last_name`, `city`, `state`, `phone`, `updated_at`, `created_at`) values (mike, mike.rowling@gmail.com, $2y$10$Mh4tYElfB11u1foRi0ZIm.Tq9ex.ygU6sLtorw9CPjiMaszgUFvWy, Mike, Rowling, Boston, New York, xxxxxxxx, 2017-11-07 07:27:58, 2017-11-07 07:27:58))
Another information is also shown as follows which is extracted from a file named laravel.log located in the storage/logs folder :
[2017-11-07 07:29:38] local.ERROR: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `first_name`, `last_name`, `city`, `state`, `phone`, `updated_at`, `created_at`) values (mike, mike.rowling@gmail.com, $2y$10$l/hRf2UH7qiiy7r9SnRsIuMg3hO.Spe3Mh1toM32ozpW4BmhsFp3m, Mike, Rowling, Boston, New York, xxxxxxxx, 2017-11-07 07:29:38, 2017-11-07 07:29:38)) {"exception":"[object] (Illuminate\Database\QueryException(code: 22001): SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `first_name`, `last_name`, `city`, `state`, `phone`, `updated_at`, `created_at`) values (mike, mike.rowling@gmail.com, $2y$10$l/hRf2UH7qiiy7r9SnRsIuMg3hO.Spe3Mh1toM32ozpW4BmhsFp3m, Mike, Rowling, Boston, New York, xxxxxxxx, 2017-11-07 07:29:38, 2017-11-07 07:29:38)) at /var/www/html/laravel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, PDOException(code: 22001): SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'state' at row 1 at /var/www/html/laravel/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458)
[stacktrace]
The problem is in the column named ‘state’ which is already generated as part of a table. The state column only defined for storing 2 characters. But in the above insert process, the data which is passed and it is trying to be stored is more than 2 characters in length. It is shown in the above as an example is ‘New York’ as the entry filled in the column ‘state’. Since it is more than 2 characters, it will generate an error that “Data too long for column ‘state’ at row”. To resolve the problem , just change the column definition to have larger character definition. It can be done by two methods as shown below :
1. Using directly access to MySQL Database Server
alter table users change state state(100);
2. Using migration script file available which can be executed using php artisan tool available inside the Laravel web-based application project. It shown in the following snippet code inside the migration script file handled for creating users table :
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->string('first_name',50);
$table->string('last_name',50);
$table->string('city',100)->nullable();
$table->string('state',100)->nullable();
$table->string('phone',12)->nullable();
$table->rememberToken();
$table->timestamps();
});