Automatically truncate data when inserting

Question

My MySQL Server (running with PHP via PDO on Windows 2008 Server) returns error code 1406 (data too long for field) when inserting strings longer than allowed in a column. The thing is I read somewhere that what MySQL usually truncates the data when it is not in strict mode. I changed the sql_mode in my.ini so that even at startup it doesn't enter in strict mode (it is currently ""), but it is still giving me the error and rolls back, so the data is lost (truncating is the desired behaviour of the site).

I entered to the command line and made an insert with a long string in a shorter varchar field and it does truncate the data and save, but it is the site that doesn't. When I changed the mode back to strict, it didn't truncate in the command line (only the error).

Also, I made the site output the current sql mode, both global and session (@@GLOBAL.sql_mode and @@SESSION.sql_mode) and they both output "" but just don't work as desired.

Does anyone know what is causing this and/or how to change it?

My suspicion is that it may have to do with PDO enabled with PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION, but I have read and can't find anything helpful about that (I don't really think this is definitely the explanation, but I am just putting this out there so that you know as much as possible about the problem).

Thank you very much

Answer 1

You should not really do that - please don't let bad data into your database, and sanitize it in your scripts before you insert.

If you don't know actual width of your VARCHAR columns or don't want to hard-code it in your scripts, you can read it from database by querying INFORMATION_SCHEMA table COLUMNS using query like this:

SELECT
    column_name,
    data_type,
    character_maximum_length,
    ordinal_position
FROM information_schema.columns
WHERE table_name = 'mytable'

(you may want to limit this to only data_type = 'varchar'). Having this information and specifically character_maximum_length, you can use PHP substr to trim your input strings to desired length.

Advantage of this approach is that it does not alter any server configuration, and should work for any other databases, not only MySQL.

But if you insist on doing it unsafe way, you can try to temporarily disable strict mode by executing this:

SET sql_mode = '';

After that MySQL should silently trim strings. Read more here.