Mysql ошибка 1406 - Не ошибается лишь тот, кто ничего не делает!

Error Code: 1406. Data too long for column

CREATE  TABLE `TEST` 
(

  `idTEST` INT NOT NULL ,

  `TESTcol` VARCHAR(45) NULL ,

  PRIMARY KEY (`idTEST`) 
);

Now Insert some values

INSERT INTO TEST
VALUES
(
    1,
    'Vikas'
)

select 

SELECT * FROM TEST;

Inserting record more than the length

INSERT INTO TEST
VALUES
(
    2,
    'Vikas Kumar Gupta Kratika Shukla Kritika Shukla'
)

If we select the length

SELECT LENGTH('Vikas Kumar Gupta Kratika Shukla Kritika Shukla')

 '47'

And it is showing the error message

Error Code: 1406. Data too long for column

But what is my expectation is, I want to insert at least first 45 characters in Table

please let me know if the question is not clear.

I know the cause of this error. I am trying to insert values more than the length of datatype.

I want solution in MySQL as It is possible in MS SQL. So I hope it would also be in MySQL.

asked Apr 11, 2013 at 12:39

MySQL will truncate any insert value that exceeds the specified column width.

to make this without error try switch your SQL mode to not use STRICT.

Mysql reference manual

EDIT:

To change the mode

This can be done in two ways:

Open your my.ini (Windows) or my.cnf (Unix) file within the MySQL installation directory, and look for the text «sql-mode».

Find:

Code:

# Set the SQL mode to strict 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace with:

Code:

# Set the SQL mode to strict 
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

You can run an SQL query within your database management tool, such as phpMyAdmin:

Code:

SET @@global.sql_mode= '';

PHA

1,5784 gold badges18 silver badges37 bronze badges

answered Apr 11, 2013 at 12:56

echo_Meecho_Me

37k5 gold badges58 silver badges78 bronze badges

I think that switching off the STRICT mode is not a good option because the app can start losing the data entered by users.

If you receive values for the TESTcol from an app you could add model validation, like in Rails

validates :TESTcol, length: { maximum: 45 }

If you manipulate with values in SQL script you could truncate the string with the SUBSTRING command

INSERT INTO TEST
VALUES
(
    1,
    SUBSTRING('Vikas Kumar Gupta Kratika Shukla Kritika Shukla', 0, 45)
);

answered Oct 27, 2016 at 12:23

Hirurg103Hirurg103

4,7532 gold badges33 silver badges50 bronze badges

This happened to me recently.
I was fully migrate to MySQL 5.7, and everything is in default configuration.

All previously answers are already clear and I just want to add something.

This 1406 error could happen in your function / procedure too and not only to your table’s column length.

In my case, I’ve trigger which call procedure with IN parameter varchar(16) but received 32 length value.

I hope this help someone with similar problem.

answered Apr 22, 2020 at 9:58

Besides the answer given above, I just want to add that this error can also occur while importing data with incorrect lines terminated character.

For example I save the dump file in csv format in windows. then while importing

LOAD DATA INFILE '/path_to_csv_folder/db.csv' INTO TABLE table1
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY '"'
 ESCAPED BY '"'
 LINES TERMINATED BY 'n'
IGNORE 1 LINES;

Windows saved end of line as rn (i.e. CF LF) where as I was using n. I was getting crazy why phpMyAdmin was able to import the file while I couldn’t. Only when I open the file in notepadd++ and saw the end of file then I realized that mysql was unable to find any lines terminated symbol (and I guess it consider all the lines as input to the field; making it complain.)

Anyway after making from n to rn; it work like a charm.

LOAD DATA INFILE '/path_to_csv_folder/db.csv' INTO TABLE table1
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY '"'
 ESCAPED BY '"'
 LINES TERMINATED BY 'rn'
IGNORE 1 LINES;

answered Sep 23, 2020 at 10:01

This is a step I use with ubuntu. It will allow you to insert more than 45 characters from your input but MySQL will cut your text to 45 characters to insert into the database.

Run command

sudo nano /etc/mysql/my.cnf
Then paste this code

[mysqld]
sql-mode=»NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION»
restart MySQL

sudo service mysql restart;

answered Sep 3, 2019 at 17:58

JoeJoe

8011 gold badge8 silver badges14 bronze badges

Since this question is prioritized on search-results, I will quickly say that you can pre-truncate your data before saving using substr(); then move on to the more serious issue of saving large data resulting in Error Code: 1406. Data too long for column.

I disagree with all answers and comments advising on turning off the strict mode. The presumption is, data that needs saving must be saved — not left to the chance of mysteriously disappearing at will without notice. Good table structure is advised but if you must save any large data, you can change the column’s capacity to:

TEXT: 65,535 characters - 64 KB
MEDIUMTEXT: 16,777,215 - 16 MB
LONGTEXT: 4,294,967,295 characters - 4 GB

answered Dec 14, 2021 at 13:33

AjowiAjowi

4413 silver badges12 bronze badges

Try to check the limits of your SQL database. Maybe you’r exceeding the field limit for this row.

answered Jul 23, 2015 at 14:49

TerranologyTerranology

6009 silver badges15 bronze badges

I got the same error while using the imagefield in Django.
post_picture = models.ImageField(upload_to='home2/khamulat/mydomain.com/static/assets/images/uploads/blog/%Y/%m/%d', height_field=None, default=None, width_field=None, max_length=None)

I just removed the excess code as shown above to post_picture = models.ImageField(upload_to='images/uploads/blog/%Y/%m/%d', height_field=None, default=None, width_field=None, max_length=None) and the error was gone

answered Oct 7, 2020 at 14:42

mirunimiruni

8711 bronze badges

I got this error after creating my table structure first with a primary key set then trying to upload a csv file. My CSV file had information in the primary key column. It was an export from another sql server. No matter how I tried to export and import, it wouldn’t work.

What I did to solve it was to drop my primary key column in my db and my csv, upload, then add my primary key column back.

answered Mar 3, 2021 at 21:34

Although the answers above indicate to update my.ini file, but I feel it would be better to alter column lengeth to TEXT or LONGTEXT, so that any higher length can be added.

answered Aug 7, 2022 at 15:09

Go to your Models and check, because you might have truncated a number of words for that particular column eg. max_length=»150″.

answered Jun 18, 2017 at 12:38

Источник

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

Источник

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)

Источник

How to deal with the 1406 (22001) Data too long for column error message.

6059 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.

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

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

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

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

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

The following is the output:

The actual sample output snapshot is as follows:

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

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

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

The following is the output:

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:

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:

Источник

TablePlus

Data truncation error 1406 — Data too long for column …

September 16, 2019

When inserting values to a table in MySQL, you might run into this error:

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 :

And you insert the first row of data:

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:

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:

Источник

Fix MYSQL ERROR 1406 (22001) at line.

Hello, IвЂ™m following your tutorial on how to import databases in mysql

I receive the following error after attempting to import:

ERROR 1406 (22001) at line 5450: Data too long for column вЂIPвЂ™ at row 1

I suspect the error is due to how the import is executed: By creating a blank database first, then importing.

Is there a way to import the database without creating a blank one beforehand?

This is for a WordPress database.

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

ItвЂ™s highly unlikely the issue to be related to the database existing on the instance when importing it.

This issue is with the structure of the database. What IвЂ™ll suggest is to update the IP column to letвЂ™s say be a longer varchar structure. Like varchar (30) for instance. Once you have done that, you can export the database again and import it into your current setup.

Questions

Working on improving health and education, reducing inequality, and spurring economic growth? We’d like to help.

You get paid; we donate to tech nonprofits.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow вЂ“ whether youвЂ™re running one virtual machine or ten thousand.

Источник

I have an application which connects to SQL through ODBC 11.

ODBC statement is :

SELECT PID
FROM PENTITY PENTITY01 WHERE ((NUM1 NOT BETWEEN ? + 10.7895 AND ? + 200.6734 AND NUM2 NOT IN (5996/ 8, ? — 89.3892, ? + 80.7543))

and the SQLBindparameter statement is :

static UCHAR num1[12]=12.589

rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, sqlType, precision, scale,
&num1, sizeof(num1), NULL);

With this SQLBindparameter statement I am getting error, It is working without any error if I change the value to 12.

The same code is working when connecting to SQL server 2008.

Thanks in advance.

Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

Thank you for your understanding and support.

Elvis Long
TechNet Community Support

During my research the difination for «UCHAR», it’s an integer type.

A UCHAR is an 8-bit integer with the range: 0 through 255 decimal. Because a UCHAR is unsigned, its first bit (Most Significant Bit (MSB)) is not reserved for signing.

This type is declared as follows: typedef unsigned char UCHAR, *PUCHAR;

I don’t know why your code in SQL Server 2008 R2 is working. I’m not sure whether it is casted automaticlly. I will do more research for this issue.

Have a good day.

From my research, I found:

«[Microsoft][ODBC SQL Server Driver] String data right truncation » error may be returned from a call to
SQLBindParameter if the size of the string parameter being used is greater than the size of the column being compared to. In other words if the string size of the to the left of the is less than the string size of the to the right , ODBC may return this error.

The resolution is to make the string size of the to the right of the less than or equal to the string size of the on the left.

It is difficult to track down this type of problem when third party development applications are being used. ODBC Trace can be used to help determine if this problem is occuring.

Here is an example where the customer has submitted a query «select count(*) from type1 where type1 = ?», type1 is varchar(5) and the data type being passed by the application is char[9].

Here is the relevant portion of the trace. The following information from the «exit» of SQLDescribeParam

SWORD * 0x0095e898 (12)

UDWORD * 0x0095e880 (5)

Maps to the following with the actual value in parenthesis — SQL_VARCHAR Size 5:

The «exit» value from SQLBindParameter provides the following
information:

SWORD 1
SWORD 1
SQL Data Type SWORD 12
Parameter Size UDWORD 5
SWORD 0
Value PTR 0x0181c188
Value Buffer Size SDWORD 5
String Length SDWORD * 0x0181c103 (9)

The string length parameter is the length of the string being bound to the parameter, in this instance there is a size mismatch which results in the SQLError and the SQLErrorW with the message «[Microsoft][ODBC SQL Server
Driver] String data right truncation » .

Источник

Export to relational database fails with SQL error: SQLCODE=-302, SQLSTATE=22001

Troubleshooting

Problem

When you export crawled, analyzed, or searched documents to a relational database, the export fails and the message “DB2 SQL error: SQLCODE=-302, SQLSTATE=22001” is written to the system log in the ES_NODE_ROOT/logs directory.

Cause

The length of an exported field or facet value is longer than the length of the corresponding column in the database table.

Resolving The Problem

You can resolve this problem in one of the following ways:

Configure IBM Cognos Content Analytics to truncate any exported field or facet value that is longer than the length of the of the corresponding column in the database table. In your database mapping file, set the value of the policy property to truncate.

Restriction

Recreate the database table so that its columns are large enough to contain the exported field or facet values.

To recreate the database table:

Determine which table needs to be recreated by checking the SQL error message in the export audit log in the ES_NODE_ROOT/logs/audit directory. For example, the following message indicates that the DEVICEAVAILABILITY column from the ESADMIN.DEVICEAVAILABILITY table is not long enough to store the corresponding field values.

11/27/09 06:05:06.922 GMT+09:00 [Error] [ES_INFO_GENERAL_AUDIT_INFO] [] []
serverx.ibm.com:4368:120:X’0′:bd.java:com.ibm.db2.jcc.am.bd.a:668
FFQX0717I 251658517
com.ibm.db2.jcc.am.co: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null,
DRIVER=3.57.82
com.ibm.db2.jcc.am.co: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null,
DRIVER=3.57.82
at com.ibm.db2.jcc.am.bd.a(bd.java:668)
.
at com.ibm.db2.jcc.am.lm.executeQuery(lm.java:628)
at com.ibm.es.oze.export.rdb.query.dml.Search.execute(Search.java:82)
at java.lang.Thread.run(Thread.java:736)

11/27/09 06:05:06.922 GMT+09:00 [Warning] [ES_INFO_GENERAL_AUDIT_INFO] [] []
serverx.ibm.com:4368:120:X’0′:DB2Utils.java:com.ibm.es.oze.export.rdb.utils.DB2Utils.d
LogSQLException:157
FFQX0717I SELECT «ID»,»DEVICEAVAILABILITY» FROM «ESADMIN».»DEVICEAVAILABILITY» WHERE
«DEVICEAVAILABILITY»=?

In your database mapping file, increase the length of the column in the appropriate table definition. In our example, the database mapping file contains the following definition for the DEVICEAVAILABILITY column in the ESADMIN.DEVICEAVAILABILITY table:

Increase the length of the DEVICEAVAILABILITY column by modifying the size that is specified for the type attribute. For example, change type=»CHAR(4)» to type=»CHAR(6)».

After you update the column definition, drop the table or recreate the database.

Reexport your documents.

If you receive this error for a column that contains binary content, ensure that you specify a size for the BLOB column in its table definition. For example, change type=“BLOB» to type=“BLOB(10M)». If no size is specified, DB2 by default creates a column that can store 1 MB.

Also ensure that you specify a column size that is large enough to store your binary content data. For example, if you crawl files that are no more than 5 MB, set the BLOB column size to 5 MB. If you do not know the size of the data, you can ensure that the column is large enough by specifying 32 MB as the column size because the maximum page size that can be crawled is 32 MB. However, specifying 32 MB as the column size might unnecessarily consume database server resources if most of the crawled data is less than 32 MB.

Источник

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.

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

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

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();
}

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.

EDIT:

Example

ERROR 1406 (22001): Data too long for column

Solutions

Debugging errors in general

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

TablePlus

Data truncation error 1406 — Data too long for column …

Example

Fix MYSQL ERROR 1406 (22001) at line.

Popular Topics

Questions

Welcome to the developer cloud

Export to relational database fails with SQL error: SQLCODE=-302, SQLSTATE=22001

Troubleshooting

Problem

Cause

Resolving The Problem

ERROR 1406 (22001): Data too long for column

Solutions

Debugging errors in general