Read about OpenEMR's Response to the COVID-19 Pandemic at https://www.open-emr.org/covid19/

The Mysql 8 Challenge for Developers

Hi,

For all developers in need of a challenge. See if you can get OpenEMR to work with MySQL 8 and report your findings here. Note we have a nice docker development/testing environment that you can set up to work on this problem:
openemr/README.md at master · openemr/openemr · GitHub

More details of this problem can be found here:
OpenEMR does not work with mysql-8 · Issue #1632 · openemr/openemr · GitHub

thanks and may the OpenEMR force be with you,
-brady

Our initial investigation shows the problem is with the OpenEMR install script itself and not with MySQL as the interactive command seems to work OK.

Stay tuned and we will update you once we have a fix.

-Sena

Hi @visolve ,

Yep, I got hooked on trying to solve this late last night. Turns out there are at least 3 issues (and possibly more):

  1. Need to set --default-authentication-plugin=mysql_native_password in mysql itself
  2. Can not create a mysql user and grant the user table permissions in one query anymore
  3. mysql decided to reserve the groups word

Here’s a PR where I rushed together some fixes. Still lots of testing to go and feel free to add to it, test it, or submit your own solution. Goal is to have this fixed and out in the next patch asap.

-brady

Can you confirm or deny MySQL SSL and connection pooling will keep working?

hi @jesdynf ,
Good points. Note the connection pooling can be easily tested (I’ll just check if there’s basically more than one connection in phpmyadmin). And I’ll test the SSL at some point during testing. Updated the issue (OpenEMR does not work with mysql-8 · Issue #1632 · openemr/openemr · GitHub) so I don’t forget to do this.
thanks,
-brady

MySQL 8 has numerous excellent enhancements and that is a great news! Unfortunately, some of them are not backward compatible and wont work with earlier version(s) of MySQL.

I guess you are asking here, whether the application communication will continue work as it is as before.

Fortunately, MySQL 8 supports backward compatibility in connecting to MySQL data base from applications like OpenEMR. However, they have also added a new functionality that you can connect with the MySQL server with encrypted passwd!

Nah, I was just checking to make sure some HIPAA-relevant features that went in for MySQL 5.6/5.7 stayed functioning. SSL access to the database is necessary for encrypt-over-wire, and connection pooling is necessary to make SSL negotiation not a terrible per-query expense.

I tried initially, with my previous instance of OpenEMR, but was unable to upgrade my databases.
so, I gave up.
The thing with MySql version 8 is that the default password method is caching_sha2_password. Does openemr function with caching_sha2_password ? If so, I’ll try a new install.

hi @gutiersa, the fixes were just brought into this patch

so think you’ll have to unzip the patch file into the openemr directory first to take advantage of mysql 8 fixes then install openemr and then go back and run the patch script to capture any database patching

here was another related fix by @brady.miller

Great, thanks, I will try it and post back.

Sandra

Hi @gutiersa ,

OpenEMR can support mysql8, but you are correct, you need to have mysql8 use the following setting:
–default-authentication-plugin=mysql_native_password

Last time I checked, php does not support the new mysql8 default authentication setting (guessing this will push lots of folks to mariadb for the time being).

There is also a bit of a caveat now in OpenEMR until we rebuild the packages using patch 3 (we will likely do this in a week or so) since in the patches we always set the setup.php to be empty. So, if you wished to do an install from a openemr 5.0.1 package that you used the patch 3 on before doing the install, you’d also need to download the setup.php script here:
openemr/setup.php at rel-501 · openemr/openemr · GitHub

Of course, when we rebuild the packages to incorporate patch 3 in a week or so, then this caveat will go away.

-brady

Hi @brady.miller

Any updates here? I am trying to deploy an instance of openemr with MySQL 8.0 in FreeBSD but I keep coming up with issues.
These are the issues and my workarounds so far. (Please note this is not inteneded to be used on a production server.

Mysql8 default authentication is caching_sha2_password which is not compatible with openemr. The error message from the web server may say something like: “The server requested authentication method unknown to the client [caching_sha2_password]”. Hence this line needs to be added to my.cnf under the [mysqld] section:

default-authentication-plugin = mysql_native_password

However, it still won’t work if root has already been created. Therefore a new root can be created with these two commands at the level of mysql. This is a really good tutorial https://lefred.be/content/how-to-grant-privileges-to-users-in-mysql-8-0/ :

root@localhost [mysql]> CREATE USER ‘root’@’’ identified with mysql_native_password by ‘secretstrongpassword’;
root@localhost [mysql]> GRANT alter,create,delete,drop,index,insert,select,update,trigger,alter routine, create routine, execute, create temporary tables on mysql.*
to ‘root’@’’;

If the mysql server is not located in the localhost change the bind-address directive to ‘*’ (the default may read 127.0.0.1). This line needs to be added to my.cnf under the [mysqld] section:

bind-address = *

At one point this line was added to the my.conf file and it broke my server. I am not sure if it was openemr. After this my server did not restart. I got this message in the mysql8 error log: query_cache_size unknown variable. Removing the following line from the [mysqld] section fixed my problem:

query_cache_size = 256M

I am still working on this if anyone is interested. I am willing to continue but I need some guidance.

Comments are welcome. I will update this post as I move further.

Thanks.

Hi @gutiersa,

isn’t the setting actually spelled
default-authentication-plugin ?

Yes, thanks for the correction!

1 Like