MySQL 80 service instance has crashed while trying to solve encrypted password obligation for Moodle

MySQL 80 service instance has crashed while trying to solve encrypted password obligation for Moodle

Rolf Cper írta időpontban
Válaszok szám: 4

Hello,

Since 23 december 2021 the MySQL 8.0 instance (with Moodle database) has crashed. I tried upgrading essential software and fixing the problem with the encrypted database-user password, that now is mandatory for MySQL and PHP. So, Moodle platform has to deal with this security restriction, when using newer versions of MySQL and PHP.

1. MySQL Notifier 1.1.8 shows that the MySQL 8.0 instance has stopped. I cannot start it up again.

2. The DBMS HeidiSQL will not connect to MySQL 8.0.27 anymore.

image.png

3. MySQL Workbench 8.0 CE of Oracle will now open an empty workbench (‘No connection established’).

4. The Command Line Client: MySQL 8.0 now hangs and then crashes when trying to login (clear password) to MySQL 8.0.

image%20%281%29.png

5. Thus, I cannot run the instruction "ALTER USER 'moodleuser'@'localhost' IDENTIFIED with mysql_native_password BY '***'"

6. So, I guess I have to re-install MySQL instance.

a) How can I uninstall the MySQL 8.0 instance?

b) Should I relentlessly delete everything in the following folders?

C:\Program Files\MySQL\MySQL Server 8.0

C:\Program Files\MySQL\MySQL Shell 8.0

C:\ProgramData\MySQL\MySQL Server 8.0

c) Can I then reinstall the version 8.0.27 of MySQL via MySQL Installer 1.6 ?

d) Will such an operation only affect the MySQL 8.0 service and not the relevant 8.0 databases (Moodle, Matomo) nor MySQL 5.7 service? 

e) Or is a corrupted password stored inside the databases? 

f) Is the encrypted password stored in a MySQL 8 system-database or client-database?


When I get MySQL 8 up and running again (with encrypted connection), then I will upgrade PHP to version 7.4 (supporting caching_sha2_password).

Any help will be appreciated.

Friendly regards,
Rolf

Environment:
Windows 10 pro, IIS 10, Moodle 3.8.1+ , PHP 7.2.8 , MySQL 8.0.27


Értékelések átlaga: -
Válasz erre: Rolf Cper

Re: MySQL 80 service instance has crashed while trying to solve encrypted password obligation for Moodle

Howard Miller írta időpontban
Kép Kép Kép Kép Kép

"...the encrypted database-user password, that now is mandatory for MySQL and PHP"

It's not. You can (and need to) just set MySQL 8 to use native passwords. In mysql.ini, add...

[mysqld]
default-authentication-plugin=mysql_native_password

and restart. 

If you already have a user set up for the new style encryption...

ALTER USER 'nativeuser'@'localhost'IDENTIFIED WITH mysql_native_password BY 'password';


Értékelések átlaga:Useful (1)
Válasz erre: Howard Miller

Re: MySQL 80 service instance has crashed while trying to solve encrypted password obligation for Moodle

Rolf Cper írta időpontban

Hello Howard,
Thank you for your answer.

I tried the following:

…\MySQL Server 8.0\my.ini

[mysqld]
default-authentication-plugin=mysql_native_password

Result via MySQL Notifier 1.1.8: Start --> StartPending  -->  Stopped (!).



The problem is that I cannot start the background-service of MySQL 8 instance anymore.

So, I cannot try any ALTER USER commands, because the Command Line Interface will crash immediately (with clear password).

In the past I always used a non-encrypted password. I do not know what the status of the password (within MySQL instance?) is at this moment (clean or encrypted?), as I cannot read any data because of the crash of MySQL 8 background-service instance.

Can I use the MySQL Installer Community 1.6 to remove the MySQL 8.0.27 service without affecting the client databases (of Moodle and Matomo platforms)?

If ‘removing’ MySQL 8 has no side effects, I will try that and re-install MySQL 8.0.27  again via the MySQL Installer.

Or do you know a way how to get MySQL80 service to run correctly again? (MySQL57 is running properly.)

 

Friendly regards,

Rolf









Értékelések átlaga: -
Válasz erre: Rolf Cper

Re: MySQL 80 service instance has crashed while trying to solve encrypted password obligation for Moodle

Howard Miller írta időpontban
Kép Kép Kép Kép Kép
Unfortunately, I don't know anything about running MySQL on Windows.
Értékelések átlaga: -
Válasz erre: Rolf Cper

Re: MySQL 80 service instance has crashed while trying to solve encrypted password obligation for Moodle

Rolf Cper írta időpontban

Ongoing story:

Hello,

I tried to repair the still-crashing MySQL80 service by the following actions.

1. I made a kopie of the the MySQL-folders in the Windows subfolders: 

'Program Files', 'Program Files (x86)', and 'ProgramData'

2. I used the Oracle program 'MySQL Installer Community 1.6':

a) to Remove 'MySQL Server 8.0.27'.

b) I unchecked the option ‘Remove the data directory’ .

3. I restarted the computer and used CCleaner to discard useless registry entries.

4.a) Then I used MySQL Installer Community 1.6:

a) to reinstall (Add...) MySQL Server 8.0.27 again.

b) I let the path to ProgramData-folder refer to the old path (containing the old files), so I kept the same data-directory.

c) I selected the option 'Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)'.

MySQL Installer asked for the Current Root Password. I entered the old (unencrypted) password. MySQL Installer does not accept it.

I also tried this with the option 'Use Strong Password Encrypted for Authentication' with same password. MySQL Installer does not accept this either.

I tried to login via the MySQL8 Command Line Client, but this crashes after entering the old password.

Also, an empty password-field will not work. 


So, MySQl80 server is still not running or accepting login.

Do you have any answers to the following questions:

A. Should I also reinstall: MySQL Shell 8.0.27, and the several Connectors 8.0.27 (C++, J, NET)?


B. Does Oracle/MySQL have a default password for the first/clean installation?

C. Where is the root-password stored (in which file or database-table)?

D. Which old files should I delete from the ProgramData\MySQL subfolder, before reinstalling MySQL8 again?

F. Should I remove all traces of MySQL all-together from the computer (v.8 en v.5.7), so I can start with a clean/fresh install?


Friendly regards,

Rolf


Environment:

Windows 10 pro, IIS 10, Moodle 3.8.1+ , PHP 7.2.8 , MySQL 8.0.27


Értékelések átlaga: -