Geek Answers Handbook

Automatically crop data when pasting

My MySQL Server (running PHP through PDO on Windows 2008 Server) returns error code 1406 (data is too long for the field) when inserting rows longer than allowed in the column. The fact is, I read somewhere that MySQL usually truncates data when it is not in strict mode. I changed sql_mode in my.ini so that even when it starts, it does not enter strict mode (it is currently ""), but it still gives me an error and rolls back, so the data is lost (truncation is the desired behavior of the site) .

I entered into the command line and pasted with a long line in the shorter varchar field, and it truncates the data and saves it, but this is the site that does not. When I changed the mode to strict, it was not cut off on the command line (only an error).

In addition, I made a site displaying the current sql mode, both global and session (@@ GLOBAL.sql_mode and @@ SESSION.sql_mode), and both of them print "", but just do not work as desired.

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

My suspicion is that this may be due to PDO with PDO :: ATTR_ERRMODE = PDO :: ERRMODE_EXCEPTION, but I read and cannot find anything useful in this (I really don't think this is definitely an explanation, but I just put it there so that you know as much as possible about the problem).

Thank you very much

+4
source share
2 answers

You should not do this - please do not put bad data in your database and deactivate it in your scripts before inserting.

If you do not know the actual width of the VARCHAR columns or do not want to hardcode it in your scripts, you can read it from the database by querying INFORMATION_SCHEMA table COLUMNS using this query:

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

(you can limit this to data_type = 'varchar' ). With this information, and in particular character_maximum_length , you can use PHP substr to trim your input lines to the desired length.

The advantage of this approach is that it does not change the server configuration and should work for any other databases, not just MySQL.

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

 SET sql_mode = '';

After that, MySQL should silently trim the rows. More details here .

+3
source

Perhaps the mysql server is in non-forgiving mode, otherwise. traditional (most often). What you are reading is true if the mysql server is in forgiveness mode. If you try to insert a line too long, this can be really important information, so mysql throws an error. You have several alternatives:

1: Use ignore insert (which converts all errors to warnings and goes to a data break)

2: for the current session set sql_mode to ''

With any of these problems, your problem should go away.

PS: I read the error you get, but I still think that the server is working in traditional mode (so I wasn’t mistaken that I recommended sql_mode set to empty).

PS2: After changing my.cnf did mysql server reboot?

0
source

More articles:


All Articles