Can't connect after upgrading to MySQL 8

Can't connect after upgrading to MySQL 8

by Luiz Peralba -
Number of replies: 11

Hi, Folks,

I've recently upgraded to Moodle 3.8+ (Build: 20191212). It works fine using MySQL 5.6. 

When I tried to update our database to MySQL 8, it was presented the message bellow:

Error: Database connection failed 
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php

I tried to connect the database using command line mysql -u xxxxxx -h xxx.xxx.xxx.xxx -P 3320 -p and it worked.

Purged caches and nothing changed. Still can't connect using Moodle.

Any idea about what could be wrong?

Thanks,

Luiz


Average of ratings: -
In reply to Luiz Peralba

Re: Can't connect after upgrading to MySQL 8

by Ken Task -
Picture of Particularly helpful Moodlers

Check config.php file for DB variables.

What you used to test and worked should probably be in config.php.   Look at config-dist.php - Section 3.

Will say that port (3320) isn't the typical port for mysql db server but you might have reasons for that ... dunno.

And for reference:

https://dev.mysql.com/doc/refman/8.0/en/installing.html

https://dev.mysql.com/doc/refman/8.0/en/linux-installation.html

Anything different about your 8 install?   Using same my.cnf file for 8 that you used with 5.6?

Any reason for not running 5.7?

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Can't connect after upgrading to MySQL 8

by Luiz Peralba -

Hi, Ken,

Thank you very much for your reply.

Let's begin... 

Check config.php file for DB variables.

I used the same information. Checked it several times.

Will say that port (3320) isn't the typical port for mysql db server but you might have reasons for that ... dunno.

The database guys said they have multiple instances, thats why they used 3302

Anything different about your 8 install?   Using same my.cnf file for 8 that you used with 5.6?

No, nothing different. Installed as usual. 

Yes, they used the same my.cnf. 

Sorry, I commited a mistake. The previous one was 5.7

I don't know if it helps, but there is a Moodle 3.5.1 instance (not mine) that connects to the same database server, but from another server.

Could it be something related to PHP, maybe mysqli extension? What do you think?

Thanks!

Luiz

Average of ratings: -
In reply to Luiz Peralba

Re: Can't connect after upgrading to MySQL 8

by Ken Task -
Picture of Particularly helpful Moodlers

Don't use MySQL vr 8 mysef so ....

The mysql client software you were using from server to test connection ... was it/is it the version 8 mysql client?

Are you being forced to vr 8 by whoever or can you run a 5.7?

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Can't connect after upgrading to MySQL 8

by Luiz Peralba -

Hi, Ken,

The mysql client software you were using from server to test connection ... was it/is it the version 8 mysql client?

Nope. It's 5.7 and it worked. 

Are you being forced to vr 8 by whoever or can you run a 5.7?

Yes, the database team wants to upgrade, so they are demanding me to use the new version.


Using command line it's ok. Using Moodle I get the same error message of connection failure. 

Thanks again.

Luiz

Average of ratings: -
In reply to Luiz Peralba

Re: Can't connect after upgrading to MySQL 8

by Ken Task -
Picture of Particularly helpful Moodlers

Then install the version 8 MySQL client on the server where moodle code resides.

Do test same way.

I don't run vr 8 of MySQL yet ... while I am certain there are 'improvements' over 5.7 not at all sure there is anything in Moodle code that 'takes advantage of those improvements'.

Now I might be sending you to a rabbit hole with this one (remember have already confession I don't run vr 8 of MySQL), but ...

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

"For example, in PHP, MySQL connectivity usually is based on mysqlnd, which currently does not know about caching_sha2_password. Until an updated version of mysqlnd is available, the way to enable PHP clients to connect to MySQL 8.0 is to reconfigure the server to revert to mysql_native_password as the default authentication plugin, as previously discussed."

Comment: if DB admins are insisting, seems to me they should be involved with trouble shooting ***ANY*** application attempting to connect.  If they don't ... you have a 'caddy' of a DB server but no one can 'drive it'1???!!!

Surely DB server keeps logs ... you can see the moodle end ... what do DB server techs see?

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Can't connect after upgrading to MySQL 8

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
I tested Moodle 3.8 with MySQL 8 (and PHP 7.3) on a Debian 10 recently. Had no problems.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Can't connect after upgrading to MySQL 8

by Ken Task -
Picture of Particularly helpful Moodlers

Good to know ... please share php driver info ... what I found mentions php-mysqlnd ... and need for MySQL server config tweak 'revert to mysql_native_password'.

Guess there are no Moodle docs on MySQL vr. 8 configuration yet.

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Can't connect after upgrading to MySQL 8

by Luiz Peralba -

Hi, Ken,

The support team updated PHP and now the connection with MySQL8 server is working.

Thank you very much. I really appreciate.

Regards.

Luiz


Average of ratings: -
In reply to Luiz Peralba

Re: Can't connect after upgrading to MySQL 8

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Now it is all working, could you post the details of the PHP upgrade (from version, to version) and the php-mysql driver you are using? (See Ken's question above.)
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Can't connect after upgrading to MySQL 8

by Luiz Peralba -

Hi, Visvanath,

We upgraded from 7.1.11 to 7.1.33. 

We had to install libedit-devel. I don't know if the lack of this library was responsible for the connection problem, but we were supposed to install it in order to go on with updating.

I hope it helps.

Luiz


Average of ratings: Useful (1)