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.

so what you want to have done is that if the value is longer then 45 characters you only want to insert the first 45? — John Snow, Apr 11 '13 at 12:46
See this post for info on how to have mysql truncate fields: http://stackoverflow.com/questions/1018954/automatically-clip-strings-to-the-proper-length-on-insert — Don Dickinson, Apr 11 '13 at 12:48
You can use: SELECT CAST(('Vikas Kumar Gupta Kratika Shukla Kritika Shukla') AS CHAR) — Thanasis, Sep 19 '21 at 08:05

score 80 · Accepted Answer · edited Jan 17 '19 at 12:51

80

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= '';

edited Jan 17 '19 at 12:51

PHA

1,588
5
18
37

answered Apr 11 '13 at 12:56

echo_Me

37,078
5
58
78

How I could change the mode] – vikas Apr 11 '13 at 12:57
you restarted your apache server ? – echo_Me Apr 11 '13 at 13:23
Thank you, this solution work very well for me. SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' – calraiden Nov 04 '15 at 12:44
1

>> SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';It didn't work for me! – codeforester Dec 16 '16 at 00:10
`STRICT_TRANS_TABLES` means _If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement._ [as per docs](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-important) | [Read More](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict) – John Jun 06 '18 at 06:44
1

use this query to check the current sql mode `select @@global.sql_mode from dual` – John Jun 06 '18 at 06:47
Is it a good practice to disable strict mode, and does truncate remove a part of the string if exceeded ? – famas23 Feb 05 '21 at 08:15

score 2 · Answer 2 · answered Oct 27 '16 at 12:23

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

SUBSTRING should start at Position 1 – Wolfgang Blessen Jul 31 '19 at 07:01 — Wolfgang Blessen, Jul 31 '19 at 07:01

score 2 · Answer 3 · answered Apr 22 '20 at 09:58

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.

score 2 · Answer 4 · answered Sep 23 '20 at 10:01

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;

score 1 · Answer 5 · answered Sep 03 '19 at 17:58

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;

Ajowi · Answer 6 · 2021-12-14T13:43:46.300

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

score 0 · Answer 7 · answered Jul 23 '15 at 14:49

0

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

answered Jul 23 '15 at 14:49

Terranology

610
9
15

1

Why so many downwotes, I've had varchar(10000) and trying to give 10254 characters and this error occured. :) – temo Nov 22 '19 at 12:41

score 0 · Answer 8 · answered Oct 07 '20 at 14:42

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

score 0 · Answer 9 · answered Mar 03 '21 at 21:34

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.

score 0 · Answer 10 · answered Aug 07 '22 at 15:09

0

Although the answers above indicate to update my.ini file, but I feel it would be better to alter column lengeth to TEXT or LONGTEXT, so that any higher length can be added.

answered Aug 07 '22 at 15:09

Gopika Syam

1
2

1

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](/help/how-to-answer). – Community Aug 11 '22 at 09:29

score 0 · Answer 11 · answered Jul 16 '23 at 10:01

I have also faced the same problem when I am trying to save 0 in a Boolean type column but mistakenly I was passing 0 as an string.

Make sure you will pass the correct data with satisfying datatype.

In my case I was trying to save '0' instead of 0 in a Boolean type column.

score -2 · Answer 12 · answered Jun 18 '17 at 12:38

-2

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

answered Jun 18 '17 at 12:38

user3713612

89
1
6

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

12 Answers12

EDIT:

Linked