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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Change column type to LONGTEXT

User · Answer

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

User · Answer

Varchar has its own limits  Maybe try changing datatype to text

User · Answer

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

User · Answer

For me  I defined column type as BIT  e g   quot boolean quot   When I tried to set column value  quot 1 quot  via UI  Workbench   I was getting a  quot Data too long for column quot  error  Turns out that there is a special syntax for setting BIT values  which is  b 1

User · Answer

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

User · Answer

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

[mysql] "Data too long for column" - why?

Examples related to mysql

Examples related to sql