Mysql strict mode

Amiel · July 9, 2019, 10:14am

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)

visolveemr · July 10, 2019, 4:00pm

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 · July 11, 2019, 8:15am

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 · July 11, 2019, 9:46am

233 tables (in full install of master branch)

visolveemr · July 11, 2019, 4:08pm

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

-ViSolve OpenEMR support Team

brady.miller · July 12, 2019, 1:56am

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
(search for sql_mode in the codebase and you’ll see what I mean)

-brady

visolveemr · July 12, 2019, 4:57am

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 · July 12, 2019, 7:48am

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

github.com

openemr/demo_farm_openemr/blob/master/docker/scripts/demoLibrary.source#L156


                                     git pull origin master &&
                                     cd ~/ &&
                                     bash /home/openemr/git/demo_farm_openemr/demo_build.sh ${2}"
}


startMysql () {
docker run --detach --name mysql-openemr \
                    --env "MYSQL_ROOT_PASSWORD=hey" \
                    --net mynet \
                    --ulimit nofile=65536:65536 \
                    mariadb:10.2 \
                    --max-connections=450
}


restartMysql () {
docker stop mysql-openemr
docker rm mysql-openemr
startMysql
}


startPhp () {

visolveemr · July 12, 2019, 8:44am

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