Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 .

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:

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"
You can run an SQL query within your database management tool, such as phpMyAdmin:
Code:
SET @@global.sql_mode= '';
                Thank you, this solution work very well for me.   SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
– calraiden
                Nov 4, 2015 at 12:44
                >> SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';It didn't work for me!
– codeforester
                Dec 16, 2016 at 0:10
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
    SUBSTRING('Vikas Kumar Gupta Kratika Shukla Kritika Shukla', 0, 45)
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;
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;
Since this question is prioritized on search-results, I will quickly say that you can pre-truncate your data before saving using substr(); then move on to the more serious issue of saving large data resulting in Error Code: 1406. Data too long for column.
I disagree with all answers and comments advising on turning off the strict mode. The presumption is, data that needs saving must be saved - not left to the chance of mysteriously disappearing at will without notice. Good table structure is advised but if you must save any large data, you can change the column's capacity to:
TEXT: 65,535 characters - 64 KB
MEDIUMTEXT: 16,777,215 - 16 MB
LONGTEXT: 4,294,967,295 characters - 4 GB
                Why so many downwotes, I've had varchar(10000) and trying to give 10254 characters and this error occured. :)
– temo
                Nov 22, 2019 at 12:41
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 got this error after creating my table structure first with a primary key set then trying to upload a csv file. My CSV file had information in the primary key column. It was an export from another sql server. No matter how I tried to export and import, it wouldn't work.
What I did to solve it was to drop my primary key column in my db and my csv, upload, then add my primary key column back.
                Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
– Community
                Aug 11, 2022 at 9:29
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.