44

I have two MySQL instances. The 1st one truncates strings on insert when data is too long. The 2nd one raises an error:

ERROR 1406 (22001): Data too long for column 'xxx' at row 1

I want the 2nd one to truncate the data as well. Is there any MySQL setting to manage this behavior?

Maksym Polshcha
  • 18,030
  • 8
  • 52
  • 77
  • 1
    What is the filetype & length of column ? do you have same values, for same filed on both the servers – metalfight - user868766 Aug 27 '13 at 07:15
  • @metalfight-user868766 Don't understand your question about the filetype. The engine is InnoDB, column type - varchar. The columns length doesn't metter. And yes, the databases are almost identical. – Maksym Polshcha Aug 27 '13 at 07:18
  • 1
    i think columns length matters, VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. is your string is greater then the length of varchar filed – metalfight - user868766 Aug 27 '13 at 07:21
  • @metalfight-user868766 He knows the string is longer than the declared length. The question is how he controls whether this causes an error or causes the value to be truncated to the declared length. – Barmar Aug 27 '13 at 07:22
  • 2
    try to disable `STRICT_TRANS_TABLES` and `STRICT_ALL_TABLES` reference here http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_strict_all_tables – bansi Aug 27 '13 at 07:24
  • @metalfight-user868766 Yes, my string is greater. The question is how to change MySQL settings to get the data truncated instead of error raising. – Maksym Polshcha Aug 27 '13 at 07:25
  • @bansi You're right. I had `STRICT_ALL_TABLES` in my `sql_mode`. Removing the setting helps. If you make your comment an answer I'll accept it. – Maksym Polshcha Aug 27 '13 at 07:34

3 Answers3

58

You can disable STRICT_TRANS_TABLES and STRICT_ALL_TABLES. This allows the automatic truncation of the inserted string.

Quote from MySQL Documentation.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

Reference: MySQL Server SQL Modes

Chris Ballance
  • 33,810
  • 26
  • 104
  • 151
bansi
  • 55,591
  • 6
  • 41
  • 52
  • this is the answer to my problem also – Betty Mock Jan 01 '14 at 00:52
  • BTW - the method below should not be considered valid - you need to set the sql-mode in your my.cnf - but you don't need NO_AUTO_CREATE_USER or any other, you just need to make sure you don't have STRICT_TRANS_TABLES or STRICT_ALL_TABLES or anything else that propogates this warning. In fact sql-mode='' would also fix the error. – Ross Mar 20 '16 at 22:54
  • just be sure that suppressing errors for `INSERT` and `UPDATE` of **any invalid data** is really what you want – Paolo Nov 12 '22 at 15:26
  • disabling these modes do not just disable truncate errors, it disables many other errors too including not allowing null into null column, not allowing `abc` into `integer` column , not allowing 1000-00-00 into date column, ... I'm not sure what mysql developer were thinking when they decided "all or nothing"! – AaA Apr 09 '23 at 07:22
14

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 6.1.7, “Server SQL Modes”.

How you can change it: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Found two ways to disable strict mode:

  1. add below to my.cnf

    sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

  2. way is using mysql console.

    SET @@global.sql_mode= '';

Please test them before running on production environment.

DLight
  • 1,535
  • 16
  • 20
metalfight - user868766
  • 2,722
  • 1
  • 16
  • 20
1

if you use cpanel ,

replace 

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

into /usr/my.cnf

to

sql-mode=""

run

/etc/init.d/mysql restart
Mikel Tawfik
  • 658
  • 3
  • 9
  • 23
  • `NO_AUTO_CREATE_USER` `NO_ENGINE_SUBSTITUTION` has nothing to do with the error in question which is caused by strict mode. – AaA Apr 09 '23 at 07:24