"Data too long for column" - why?

Question

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?

There might be white space involved. From where does the data come? — Dan Bracuk, Sep 19 '13 at 14:28
I've had that error before because of an un-escaped apostrophe — tgolisch, Sep 19 '13 at 14:32
@tgolisch - I double checked - there are only two apostrophes, and they're both escaped. — Ben C., Sep 19 '13 at 14:43
@DanBracuk - The data is manually entered in the insertion command. It doesn't come from a form or anything like that. There shouldn't be any whitespace. — Ben C., Sep 19 '13 at 14:44
Check tp see if there is a trigger could be trying to insert to a differnt table with a smaller field size. — HLGEM, Sep 19 '13 at 17:05
You need to give us something to work with - we've got a description of the problem but no detail. Can you post the whole table schema and also a single insert that's failing? If the data is sensitive, change it (but check the changed data still fails!). Btw you can check your character settings by doing `SHOW VARIABLES LIKE 'char%';` — Basic, Sep 19 '13 at 18:31

score 121 · Answer 1 · answered Dec 18 '15 at 10:14

121

Change column type to LONGTEXT

answered Dec 18 '15 at 10:14

Alexander Serkin

1,679
1
12
11

5

should be the right answer - instead masking away the issue by disabling strict, rather fixing the cause – Eugen Mayer May 30 '18 at 11:57
2

Additional Info: If the text you want to store in the database is longer than 65535 characters, you have to choose MEDIUMTEXT or LONGTEXT, but be careful: MEDIUMTEXT stores strings up to 16 MB, LONGTEXT up to 4 GB. ;) – subodhkalika Feb 25 '21 at 05:24

score 60 · Answer 2 · edited May 23 '17 at 12:34

60

I had a similar problem when migrating an old database to a new version.

Switch the MySQL mode to not use STRICT.

SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Error Code: 1406. Data too long for column - MySQL

edited May 23 '17 at 12:34

Community

1
1

answered Jan 20 '16 at 19:05

calraiden

1,686
1
27
37

1

This has suited well and solved a similar problem I was facing today. Just out of curiosity...are there any drawbacks with this solution? – Ravi Gaurav Pandey May 05 '18 at 08:36
It depends on your code, because strict mode is an extra control of mysql to avoid inserting invalid data. If your code works fine in the previous version of mysql, you will dont have problem in the new version. The long explantion: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict – calraiden May 06 '18 at 19:08

score 19 · Answer 3 · edited Jun 20 '20 at 09:12

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

score 11 · Answer 4 · edited Mar 11 '20 at 11:29

11

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

edited Mar 11 '20 at 11:29

answered Sep 17 '19 at 10:49

Krishna Jangid

4,961
5
27
33

score 9 · Answer 5 · answered Sep 19 '13 at 14:30

9

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

answered Sep 19 '13 at 14:30

Jayesh Amin

314
2
12

1

Normal varchar length is 255 I think. – Wikunia Sep 19 '13 at 14:31

Ben C. · Answer 6 · 2013-09-19T18:23:16.540

9

Turns out, as is often the case, it was a stupid error on my part. The way I was testing this, I wasn't rebuilding the Department table after changing the data type from varchar(50) to varchar(200); I was just re-running the insert command, still with the column as varchar(50).

edited Sep 19 '13 at 18:23

answered Sep 19 '13 at 18:14

Ben C.

1,761
5
15
24

score 7 · Answer 7 · answered Aug 10 '20 at 08:47

7

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 '20 at 08:47

Illidan

4,047
3
39
49

score 3 · Answer 8 · answered Feb 25 '19 at 07:59

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.

score 2 · Answer 9 · edited Apr 29 '17 at 10:37

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.

score 2 · Answer 10 · answered Jun 06 '19 at 08:43

2

Very old question, but I tried everything suggested above and still could not get it resolved.

Turns out that, I had after insert/update trigger for the main table which tracked the changes by inserting the record in history table having similar structure. I increased the size in the main table column but forgot to change the size of history table column and that created the problem.

I did similar changes in the other table and error is gone.

answered Jun 06 '19 at 08:43

rahimv

541
4
12

1

Ha! My problem too. Forgot I had [`simple_history`](https://django-simple-history.readthedocs.io/en/latest/) on that Django model. – Ross Rogers Apr 21 '20 at 23:29

score 1 · Answer 11 · answered Sep 19 '13 at 14:35

1

I try to create a table with a field as 200 characters and I've added two rows with early 160 characters and it's OK. Are you sure your rows are less than 200 characters?

Show SqlFiddle

answered Sep 19 '13 at 14:35

Joe Taras

15,166
7
42
55

The longest string is 143 characters. – Ben C. Sep 19 '13 at 14:46

score 1 · Answer 12 · answered Mar 29 '21 at 12:12

There was a similar problem when storing a hashed password into a table. Changing the maximum column length didn't help. Everything turned out to be simple. It was necessary to delete the previously created table from the database, and then test the code with new values of the allowable length.

score 1 · Answer 13 · answered May 12 '21 at 10:42

1

If you re using type: DataTypes.STRING, then just pass how long this string can be like DataTypes.STRING(1000)

answered May 12 '21 at 10:42

Dawid Pstrak

15
6

score 0 · Answer 14 · answered Jun 15 '17 at 05:27

0

In my case this error occurred due to entering data a wrong type for example: if it is a long type column, i tried to enter in string type. so please check your data that you are entering and type are same or not

answered Jun 15 '17 at 05:27

havi havish

1
2

nikita sharma · Answer 15 · 2022-11-21T13:16:01.710

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

score 0 · Answer 16 · answered Feb 07 '23 at 09:54

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.