Ошибка data too long for column

I’ve written a MySQL script to create a database for hypothetical hospital records and populate it with data. One of the tables, Department, has a column named Description, which is declared as type varchar(200). When executing the INSERT command for Description I get an error:

error 1406: Data too long for column ‘Description’ at row 1.

All the strings I’m inserting are less than 150 characters.

Here’s the declaration:

CREATE TABLE Department(
    ...
    Description varchar(200)
    ...);

And here’s the insertion command:

INSERT INTO Department VALUES
(..., 'There is some text here',...), (..., 'There is some more text over here',...);

By all appearances, this should be working. Anyone have some insight?

Change column type to LONGTEXT

There is an hard limit on how much data can be stored in a single row of a mysql table, regardless of the number of columns or the individual column length.

As stated in the OFFICIAL DOCUMENTATION

The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.

Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

Here you can find INNODB TABLES LIMITATIONS

in mysql if you take VARCHAR then change it to TEXT bcoz its size is 65,535
and if you can already take TEXT the change it with LONGTEXT only if u need more then 65,535.

total size of LONGTEXT is 4,294,967,295 characters

answered Sep 17, 2019 at 10:49

Varchar has its own limits. Maybe try changing datatype to text.!

answered Sep 19, 2013 at 18:14

For me, I defined column type as BIT (e.g. «boolean»)

When I tried to set column value «1» via UI (Workbench), I was getting a «Data too long for column» error.

Turns out that there is a special syntax for setting BIT values, which is:

b'1'

answered Aug 10, 2020 at 8:47

If your source data is larger than your target field and you just want to cut off any extra characters, but you don’t want to turn off strict mode or change the target field’s size, then just cut the data down to the size you need with LEFT(field_name,size).

INSERT INTO Department VALUES
(..., LEFT('There is some text here',30),...), (..., LEFT('There is some more text over here',30),...);

I used «30» as an example of your target field’s size.

In some of my code, it’s easy to get the target field’s size and do this. But if your code makes that hard, then go with one of the other answers.

answered Feb 25, 2019 at 7:59

With Hibernate you can create your own UserType. So thats what I did for this issue. Something as simple as this:

    public class BytesType implements org.hibernate.usertype.UserType {

         private final int[] SQL_TYPES = new int[] { java.sql.Types.VARBINARY };
     //...
    }

There of course is more to implement from extending your own UserType but I just wanted to throw that out there for anyone looking for other methods.

answered Mar 29, 2021 at 12:12

answered May 12, 2021 at 10:42

answered Jun 15, 2017 at 5:27

For me, I try to update column type «boolean» value
When I tried to set column value 1 MySQL Workbench, I was getting a «Data too long for column» error.

So for that there is a special syntax for setting boolean values, which is:

   UPDATE `DBNAME`.`TABLE_NAME` SET `FIELD_NAME` = false WHERE (`ID` = 'ID_VALUE');   //false for 0

   UPDATE `DBNAME`.`TABLE_NAME` SET `FIELD_NAME` = true WHERE (`ID` = 'ID_VALUE');  //true for 1

answered Nov 8, 2022 at 10:20

I had a different problem which gave the same error so I’ll make a quick recap as this seems to have quite different sources and the error does not help much to track down the root cause.

Common sources for INSERT / UPDATE

Size of value in row

This is exactly what the error is complaining about. Maybe it’s just that.
You can:

• increase the column size: for long strings you can try to use TEXT, MEDIUMTEXT or LONGTEXT
• trim the value that is too long: you can use tools from the language you’re using to build the query or directly in SQL with LEFT(value,size) or RIGHT(...) or SUBSTRING(...)

Beware that there is a maximum row size in a MySQL table as reported by this answer. Check documentation and InnoDB engine limitations.

Datatype Mismatch

One or more rows are of the wrong datatype.
common sources of error are

• ENUM
• BIT: don’t use 1 but b'1'

Data outlier

In a long list of insert one can easily miss a row which has a field not adhering to the column typing, like an ENUM generated from a string.

Python Django

Check if you have sample_history enabled, after a change in a column size it must be updated too.

answered Feb 7 at 9:54

One of the column in my table was initially created as a varchar(1000). Now there is a need to increase the size of that column to hold around 5000 characters. I used the alter statement to increase the size of that column.

DESC table_name as well as SHOW CREATE TABLE table_name clearly indicates the new size of that column to be 5000 characters. However, interestingly when I try to insert any data exceeding 1000 characters ERROR 1406 (22001) : Data too long for column error shows up.

Out of desperation I changed the datatype to text, and still it’s limited to 1000 characters. Any suggestion?

I created an other dummy table with a column size of varchar(5000) and that works just fine.

Engine used is InnoDB and Default Charset is UTF8.