Menu
I have been working on a site that had MySQL strict mode enabled. One person had a long user agent string that was logged in our log table and unfortunately the user agent string exceeded the limit for the column and thus caused a warning. The data was not inserted at all.
To avoid such troubles, should I disable the MySQL strict mode or should I come up with something on my own (I'm using PHP)?
243
When Strict Mode is disabled, the same query would have its invalid or missing values adjusted and would produce a simple warning. This may seem like the preferred result; however, with Strict Mode disabled, specific actions may cause unexpected results.
Strict SQL Mode. 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.
You can change the value of sql_mode to NO_ENGINE_SUBSTITUTION to completely disable strict mode, but you may want to look up each mode that is configured before disabling it. If sql_mode isn't set, you can add it under the [mysqld] heading, then save the file, and restart MySQL.
Validate the data before inserting it into your database. If a string is too big to fit in your table, either your column is too narrow, or the data is invalid. You need to decide whether you truncate it before storing it, do some error reporting, or both.
Do not turn off the safety features of your DBMS, that's the completely wrong thing to do.
89
Would you rather have your data silently truncated (possibly leading to broken data) or would you at least like to know that you have a problem?
I'd recommend leaving strict mode enabled and bounds checking your data in your PHP. Your PHP application knows, or at least should know, what to do with a string that's too long. If you turn off strict mode and leave that decision to MySQL then MySQL will silently truncate your strings and you will end up with a database full of garbage.
Changing and fixing code is easy, fixing broken data is often impossible.
If you disable strict mode, you'll end up with strange problems like this:
VARCHAR(4) storing more characters than four
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
