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

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.

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.

1. Run command

   sudo nano /etc/mysql/my.cnf

2. Then paste this code

   [mysqld] sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3. restart MySQL

   sudo service mysql restart;

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

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

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.

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 \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 '"'
 ESCAPED BY '"'
 LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

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

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