Sql_mode is not set to strict as expected

[gorankosamja]

At epiz_32763840, the sql_mode is empty, i.e. not set to strict mode, i.e. it allows empty value for an enum field. I know it cannot be changed, but I expected it to be set to strict, as suggested in this other forum post:

Change the sql_mode

Unfortunately, you cannot change that setting here, and below is why: Strict SQL mode is enforced on our database servers and cannot be disabled. Strict SQL mode is the current default of modern MySQL versions, because it’s bad to rely on MySQL to automagically fix your data for you. Fixing the data is something that should be done by the software, so I would highly urge you to contact your script developer and tell them to fix their code, rather than demanding custom server settings to fix th…

[Admin]

@gorankosamja

At epiz_32763840, the sql_mode is empty, i.e. not set to strict mode

I’m pretty sure that’s now how it works.

When the sql_mode version is set to empty, it means it uses the default value as determined by the developers of the database system. We don’t customize this value because the default value is most likely to be what other hosting providers use too, so it offers the best compatibility with software and interoperability with other hosting providers. We’re not going to enforce our personal opinions on how to use SQL on our users.

Our database servers run MariaDB 10.3 so, as you can see in the link below, the default means that strict mode is enabled, and specifically the STRICT_TRANS_TABLES option. And looking at the documentation, with this option set, enum fields do not allow empty values.

SQL_MODE

How did you come to this conclusion exactly? Did you just check the value of sql_mode and assume that this would allow empty values in enums or did you actually check whether you could actually set an enum to an empty value?

[gorankosamja]

@Admin

How did you come to this conclusion exactly? Did you just check the value of sql_mode and assume that this would allow empty values in enums or did you actually check whether you could actually set an enum to an empty value?

In the reverse order. I first ran into problem with an enum field, then did research. It can be easily confirmed in phpmyadmin, just I am not sure at the moment if I can insert screenshots here.

[gorankosamja]

Ok, it’s actually easy to insert screenshots, so here they are:

[Admin]

Hmm, it seems you’re right. Interesting, the SQL mode setting is not what I expected and not what I would have put it to. Thank you for investigating this issue and telling us about it!

I’m going to talk to iFastNet to see if this setting is what they intended or if this is an error.

[Admin]

I haven’t reached out to iFastNet yet but I did do some additional investigation.

I tested various other database servers from different hosting providers. Not having strict SQL mode enabled seems to be fairly common. This may be caused by the fact that not all software can run with Strict SQL mode enabled (WHMCS comes to mind).

So it’s possible that this setting was actually done intentionally to enable support for scripts which don’t support Strict SQL mode.

In the end, I guess it means that you have to take extra care with your input validation and queries in PHP to make sure that no unsupported values can be entered in the database.

[EdwinTigor]

Hi i would love to talk on forum.
i never play with enum type, i saw at stack overflow enum is just for integer unsigned. I see you put z.

Why does MySQL add 1 to ENUM values?

[gorankosamja]

To learn about enum in mysql, please read the mysql doc, for example: https://dev.mysql.com/doc/refman/8.0/en/enum.html

[gorankosamja]

Sure, I can live with that.

Thank you for looking into this and for all your great work here!

[EdwinTigor]

I saw. It is possible to enum field get null as value, index 0 is empty after NULL. So i still don’t find the error. Is it?