All geek questions in one place

Should I disable MySQL strict mode?

I am working on a site with MySQL strict mode enabled. One person had a long user agent row that was logged in our log table, and unfortunately the user agent row exceeded the column limit and thus triggered a warning. Data was not inserted at all.

To avoid such problems, should I disable MySQL strict mode or do I need to come up with something on my own (I use PHP)?

+5
source share
4 answers

Check the data before inserting it into your database. If the row is too large to fit your table, either your column is too narrow or the data is invalid. You need to decide if you trim it before saving, follow some error messages or both.

Do not disable the security features of your DBMS, which is completely wrong to do.

+9
source

Would you prefer your data to be truncated silently (possibly leading to broken data), or would you even like to know that you have a problem?

I would recommend leaving strict mode enabled and limited by checking your data in your PHP. Your PHP application knows, or at least needs to know, what to do with a line that is too long. If you turn off strict mode and leave this MySQL solution, then MySQL will silently truncate your rows and you will get a database full of garbage.

Changing and fixing the code is easy; repairing corrupted data is often impossible.

If you disable strict mode, you will have strange problems like this:

VARCHAR (4) stores more characters than four

+2
source

It's good that you are trying to do this with strict disconnected mode and see how everything works, I never had a disconnect problem, but be careful.

However, if long user agents are the only issue with strict mode, and you do not visually read them, than I recommend just hashing your UA or increasing the length of the field.

+1
source

If you have a need to do this, if you do not fulfill the requests, and you do not have the knowledge or budget to fix this, then yes.

If someone asks how to disable strict mode correctly, simply adding it to the MySQL configuration:

sql_mode=NO_ENGINE_SUBSTITUTION innodb_strict_mode=0
0
source

Source: https://habr.com/ru/post/1346418/

More articles:


All Articles