Issue Plesk can't access psa database after mariadb update

[GuidoDe]

I've upgraded MariaDB from 5.5 to 10.2 (on CentOS 7.7) following this tutorial: How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.0/10.1/10.2 on Linux?

MariaDB 10.2 is running. User/Websites can connect to their databases. But I can't connect with admin anymore.

MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --all-databases --routines --triggers > /tmp/all-databases-new.sql

Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
FATAL ERROR: Upgrade failed[/CODE]

I tried to set a new password for admin but...

systemctl stop mariadb
[root@host ~]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 76597
[root@host ~]# 200317 08:23:11 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
200317 08:23:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[1]+  Done                    mysqld_safe --skip-grant-tables --skip-networking
[root@host ~]# mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

It's no longer possible to access the Plesk GUI because Plesk can't access the psa database anymore. What to do now?

Many thanks in advance!

 

[learning_curve]

@GuidoDe Have a read of this: Plesk upgrade/installation/migration fails: Access denied for user 'admin'@'localhost'

 

[GuidoDe]

Yes. Thanks.

The reason why "mysqld_safe --skip-grant-tables --skip-networking &" failed was, that Mariadb could not write the pid-file. Mariadb tried to write it it in /var/run/mariadb but that directory was non existent. You can create the directory or change the configuration, so that Mariadb uses another directory for the pi-file. After that it was possible to execute "mysqld_safe --skip-grant-tables --skip-networking &" and to set a new password for the database user "admin".

For all internal purposes, Plesk gets the database password from /etc/psa/.psa.shadow. So I put it in plain text, which works but of course plain text is bad. Does anybody know how to store the password as hash again? I think it was stored as AES128 hash, which needs the right secret?

 

[abashurov]

For all internal purposes, Plesk gets the database password from /etc/psa/.psa.shadow. So I put it in plain text, which works but of course plain text is bad. Does anybody know how to store the password as hash again? I think it was stored as AES128 hash, which needs the right secret?

It's actually always plaintext, even if it was generated by Plesk. Otherwise, running MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin -Dpsa -p would not be possible.