Is it safe for me to turn off STRICT_TRANS_TABLES?

Question

My PHP/mySQL backend hosted on an external site has been running fine since 2014. Recently, it started throwing up "field has no default value" errors.

I checked the config and found STRICT_TRANS_TABLES, which gives these errors for fields with no default in some cases.

My question is whether or not it is safe for me to remove this config value. It's mySQL 5.5.5-10.3.12-MariaDB.

Alternatively I could give everything default values, but I don't know which of these solutions is more likely to cause the existing codebase to stop working properly.

Resnatron

Answer 1

I encourage use of strict mode in MySQL because if you disable strict mode, you risk causing some unwanted effects, such as:

• If you insert a value to a column where it can't fit, the value is silently truncated. Like inserting a long string into a shorter varchar column, or inserting a big integer into an INT column. This leads you to potentially have bogus values in your database. I prefer these cases to be errors, to prevent such bogus values.

• Non-strict mode allows nonsensical dates, like 0000-00-00. There is no such date in the calendar. I'd rather this value not be allowed. If I need to symbolize an absence of a value, I'll use NULL.

Will these cases affect your app? There's no way I or anyone else on Stack Overflow can predict that. You need to test it yourself.

Ethyl

"Will these cases affect your app? There's no way I or anyone else on Stack Overflow can predict that." Absolutely. Your answer is exactly what I was looking for. Thanks! – Resnatron