MySQL 8 broken - Not able to login after installation

[sanjeev-js]

I have installed it using the installation instructions of linux, but after installation when I try to login, then it shows always invalid username or password, but i am using the correct one. Please help to fix this, i don't know php otherwise i will try to fix the source code.

[stephenwaite]

hi @sanjeev8386 , i've noticed that firefox will save the mysql username password instead of the openemr login on the setup page, suggest you try to reinstall

[sanjeev-js]

Hey @stephenwaite , I have reinstalled it around 10-12 times but there is no change, always showing incorrect username or password. please do something for it. I have installed it through chrome browser.I am trying to install it through firefox. if it will not work i will get back to you.

[sanjeev-js]

Hey @stephenwaite , I have reinstalled it through firefox but the result is same.

[stephenwaite]

hi @sanjeev8386 , what version are you trying to install?

[bradymiller]

hi @sanjeev8386 ,
Also:
What mysql/mariadb version are you using? Are you able to connect to the mysql/mariadb database directly?

[sanjeev-js]

Hey @bradymiller I am using mysql 8.0.18 version, and I am also able to connect it with the database
can you please help me and fix this.

[bradymiller]

hi @sanjeev8386 ,
I'm suspicious of the mysql 8 version here since it has been buggy with php ever since it's been released. I'll do some testing with mysql 8 tomorrow.

[bradymiller]

2 quick questions:

1. What version of OpenEMR are you using?
2. In your openemr mysql database, do you have any entries in the gacl_groups_aro_map tables?

[bradymiller]

Also, What PHP version are you using?

[bradymiller]

btw, this will be a good launching point into ensuring MySQL 8 support especially since next LTS ubuntu release (20.4) will be going to MySQL 8.

[bradymiller]

Yep, some MySQL 8 weirdness going on:
I confirmed this bug on MySQL 8.0.18 (with --default-authentication-plugin=mysql_native_password) and PHP 7.3.11 using the development OpenEMR codebase(the dev codebase has much nicer logging of login errors in Administration->System->Logs which allowed me to pinpoint the issue). This stems from an installation issue where the gacl for the user is not set up properly. I was able to get OpenEMR login working via mysql command:

INSERT INTO `gacl_groups_aro_map` (`group_id`, `aro_id`) VALUES ('11', '10');

This should be basically done here during installation:
https://github.com/openemr/openemr/blob/master/acl_setup.php#L253

This is definitely concerning what else bugs could be looming in mysql 8. Will make it clear that OpenEMR does not work for MySQL 8 on the download page until we get fixed packages up.

[bradymiller]

Did a install with gacl debug turned on and looks like this is the issue:

So, appears this query is breaking in mysql 8:
https://github.com/openemr/openemr/blob/master/gacl/gacl_api.class.php#L1947-L1952

As MariaDB and MySQL diverge, guessing these things will keep cropping up. To avoid this, will add Mysql 8 along with all supported versions to travis functional testing (the functional testing is very limited, but it at least test login, which is failing on the mysql8 travis test i just added:

I think i am going to add testing jobs for all supported versions of mariadb and mysql to try to prevent these issues (and will get even better when have more functional testing in place @zerai (hint, hint :) ). Doing this will likely bump up the travis test time from 10 min to 15 min, but maybe we can convince travis to give us a higher concurrent job limit (currently 5).

And of course will figure out why this query is breaking in mysql 8.

[bradymiller]

lol,
mysql 8 decided to reserve the keyword 'member':
https://dev.mysql.com/doc/refman/8.0/en/keywords.html
Every 3-4 years, we get burned by something like this...
Good example why good practice to use those backtics

[bradymiller]

btw, very interesting fact on this. Per the mysql docs:
member - added in 8.0.17 (reserved); became nonreserved in 8.0.19
This basically means it should only affect mysql 8.0.17 and 8.0.18 . Hopefully 8.0.19 gets released soon and can prove that (this would prevent us from needing to rebuild/test 5.0.2 packages which is time consuming; fix would still be included in the next 5.0.2 patch though).

[bradymiller]

Brought in fix that is working well. It will also go into next 5.0.2 patch and will plan to rebuild the packages if MySQL 8.0.19 is not out yet (and confirm old code works on it).

[sarafgaurav]

In case it helps the discussion, I had no problems with PHP 7.4.2 and MySQL 8 (other than PHP warnings during actual execution of OpenEMR code). However, when I downgraded to 7.3.14, it could not talk to MySQL 8 for the initial OpenEMR setup until I did
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
and changed
[mysqld] default-authentication-plugin=mysql_native_password
in my.cnf. You might have to flush privileges after the alter statement. These settings are required for the said PHP and MySQL combination because older PHPs have trouble using newer authentication mechanisms in MySQL.

Also, it looks like my observation is validated by @bradymiller 's screenshot from Travis tests that show PHP 7.3.x failing with MySQL 8. So hopefully what worked for me should work for others. I'm contributing to OSS discussions for the first time, let me know if you have any feedback.

[bradymiller]

hi @sarafgaurav ,

Thanks for your observation, congrats on your first OSS discussion contribution, and welcome to the OpenEMR community!

Here's an updated travis screenshot with a nice green checkmark for mysql 8:

The default-authentication-plugin=mysql_native_password issue is something that I think the php community is still grappling with. Only real official documentation about it is here:
https://www.php.net/manual/en/mysqli.requirements.php

But there was still this related bug in 7.4.1 here:
https://bugs.php.net/bug.php?id=78981

I still need to set it even with PHP 7.4.2 on OpenEMR or weird things happen (such as in the docker that does the travis testing: https://github.com/openemr/openemr/blob/master/ci/apache_73_8/docker-compose.yml#L6-L7). Hopefully it will be sorted out before Ubuntu 20.4 gets released which will use mysql 8 by default.

-brady

[gutiersa]

Re: "Hopefully it will be sorted out before Ubuntu 20.4 gets released which will use mysql 8 by default."

The more reason for us to get OpenEMR working with Mysql8.
I am in, who else?

[gutiersa]

You might have to flush privileges after the alter statement.

this is no longer needed in Mysql 8

[stu01509]

hi @sanjeev8386 , i've noticed that firefox will save the mysql username password instead of the openemr login on the setup page, suggest you try to reinstall

Hi @stephenwaite

Are we have any idea to avoid firefox save the MySQL username, password instead of the OpenEMR login username and password?

[stephenwaite]

good point @stu01509 , let's create a new install issue

[stu01509]

good point @stu01509 , let's create a new install issue

I would like to solve it, Please assign the issue to me after you created an issue.

[stale]

This issue has been automatically marked as stale because it has not had any recent activity within the past 90 days. It will be closed in 7 days if no further activity occurs.

[stale]

This issue has been automatically closed because it has not had any recent activity within the past 97 days.