Mysql 1406 Data too long for column

Question

I have a string containing more than 600 characters, where I want to Insert it in a column VARCHAR (255) column
I tested on two different databases:

For a mysql 5.7.31-34-log database, I ran into this exception:
SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'name' at row 1"

I dumped the sql_mode for each database and this the result:

["@@sql_mode"]=> string(40) "STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION"

On the other hand for a mysql 5.7.19 database, the string is well registered into my database, but with a warning message: Level Code Message
Warning 1265 Data truncated for column 'name' at row 1

sql_mode:

["@@sql_mode"]=> string(0) ""

I would be grateful if someone could explain to me it is a good practice to disable strict mode, and does truncate remove a part of the string if exceeded ?

STRICT_ALL_TABLES causes data truncate to be an error whereas with this mode disabled the truncation generates warning only. — Akina
Is it a good practice to disable strict mode No. Good practice is to check/adjust data before storing. Error is a detection that the above operation wwas performed wrongly, and it does not allow to save wrong data. — Akina
my string Is well inserted without any truncate I don't believe. dbfiddle.uk/… Check actual column definition by SHOW CREATE TABLE. — Akina
'The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.; - dev.mysql.com/doc/refman/8.0/en/char.html — P.Salmon

Kazi Mohammad Ali Nur Kazi Mohammad Ali Nur · Accepted Answer · 2021-02-05T08:55:55

you cannot store 600 character in varchar(255) since It's allowing only 255 character.dependingvon your configuration data will be rejected or truncated after 255 character. The version of MySql you are using allow way more that 255 for varchar data type. please alter the column to extend the length. you may use varchar(600) for example.

Mysql 1406 Data too long for column

2 Answers