Mysql strict mode

question

(Amiel Elboim) #1

Hi!

We are updating our sql servers to mariaDB 10.3, the default sql_mode of that version is “STRICT_TRANS_TABLES”.

I would like to know if the servers of the community are in strict mode also? We can be sure that all the functionality will work well in the strict mode also? or it’s unknown?

Thank you.

Amiel (Matrix)


(ViSolve) #2

The default mode for OpenEMR is "STRICT_TRANS_TABLES” and hence if you are planning to use "STRICT_TRANS_TABLES” you should not have any surprises.!
-ViSolve OpenEMR support Team


(Brady Miller) #3

Hi,
I thought OpenEMR was not compatible with strict mode, but I could be wrong. How many sql tables do you get after you do an install when in strict mode?
-brady


(Amiel Elboim) #4
233 tables (in full install of master branch)

(ViSolve) #5

We should have been a little more specific with our response to the question.

STRICT_TRANS_TABLES is one of the values of MySQL global in SQL_MODE.

The initial question was on STRICT_TRANS_TABLES. There are other values in SQL_MODE which will break OpenEMR queries depending on the database software version.

MariaDB 10.2.4 SQL_MODE default values:

STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

MariaDB 10.3.14 SQL_MODE default values:

STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

The default SQL mode in MySQL 5.6: NO_ENGINE_SUBSTITUTION

The default SQL mode in MySQL 5.7 : ONLY_FULL_GROUP_BY , STRICT_TRANS_TABLES , NO_ZERO_IN_DATE , NO_ZERO_DATE , ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER , and NO_ENGINE_SUBSTITUTION .

Now, depending on whether you use MySQL or MariaDB the behavior will be different for OpenEMR application.

For example - Error related to only_full_group_by when executing a query in OpenEMR with MySQL, is discussed by Brady et al here!

Another example is, SQL_MODE values like “NO_ZERO_IN_DATE”, and “NO_ZERO_DATE” will also have impact on OpenEMR apps which are default values in MySQL but NOT in MariaDB!

To be specific, Openemr_postcalender_events table has pc_enddate column default values as “0000-00-00” which will throw error when the above values are left as default values!

One can turn off the corresponding SQL_MODE to fix these errors.

In Summary, since you are using MariaDB and NOT MySQL you should be OK but watch out for values

Like ERROR_FOR_DIVISION_BY_ZERO etc.

But again, there is always Murphys Law :slight_smile:

-ViSolve OpenEMR support Team


(Brady Miller) #6

Hi,

I just recalled why OpenEMR does not crash and burn with strict mode. That is because OpenEMR explicitly turns it off in all of its queries :slight_smile:
(search for sql_mode in the codebase and you’ll see what I mean)

-brady


(ViSolve) #7

Perfect!
@Brady - Kudos! Excellent research work!
While we are on this topic, why not standardize (Default) OpenEMR with MariaDB instead of MySQL!
Any thoughts from the community?
- ViSolve OpenEMR Support


(Brady Miller) #8

Hi,
OpenEMR should work with either mariadb or mysql.
Note all the demos on the demo (docker) farm are using MariaDB:


(ViSolve) #9

@Brady - Thanks Doc. Nice to hear the community is ahead with the latest technology like MariaDB!
-ViSolve OpenEMR support team