Error Code 1406 Data too long for column - MySQL

Question

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

User · Answer

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

User · Answer

Go to your Models and check  because you might have truncated a number of words for that particular column eg  max length  150

User · Answer

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

User · Answer

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   quot    ESCAPED BY   quot    LINES TERMINATED BY   n  IGNORE 1 LINES   Windows saved end of line as  r n  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  r n  it work like a charm  LOAD DATA INFILE   path to csv folder db csv  INTO TABLE table1  FIELDS TERMINATED BY       ENCLOSED BY   quot    ESCAPED BY   quot    LINES TERMINATED BY   r n  IGNORE 1 LINES

User · Answer

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

User · Answer

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    Or   You can run an SQL query within your database management tool  such as phpMyAdmin    Code   SET   global sql mode

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to sql-server