Upgrade 11.5.30: ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost'

[SacAutos]

CentOS 5.9 server, current update level, Plesk 11.0.9 MU#55. Attempting to do the update to Plesk 11.5.30 via the command line. Started nicely but failed shortly thereafter:

----- Begin -----

===> Cumulative Plesk database upgrade (revertable stage) has been started.
===> Preparing Plesk database upgrade (revertable stage).
ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)

ERROR while trying to (to check that DB sitebuilder5 is accessible to user pp_sb_db), the query was:
Check the error reason(see log file: /tmp/plesk_11.5.30_installation.log), fix and try again


===> Restoring database from backup /var/lib/psa/dumps/mysql.preupgrade.11.0.9-11.5.30.20130703-161630.dump.gz

ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)

[ERROR] Database restoring failed
Try to restore database from /var/lib/psa/dumps/mysql.preupgrade.11.0.9-11.5.30.20130703-161630.dump.gz manually


Trying to start psa...

**** Product prep-install for BASE failed.

***** problem report *****
ERROR while trying to (to check that DB sitebuilder5 is accessible to user pp_sb_db), the query was:
Check the error reason(see log file: /tmp/plesk_11.5.30_installation.log), fix and try again

----- end -----

Ideas on how to fix? Fortunately, this was at an early enough phase that nothing is broken - yet.

 

[SacAutos]

Since nobody has come to my rescue, I've been continuing to research this issue. I have noticed (and posted about this before) that my ability to access mysql via the command line went away some time ago after an update. If I try to just get an interactive MySQL session going at the command line, it fails. Example:

[root@~]# mysql -uadmin -p
Enter password:
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
[root@~]#

Where I had entered my admin password. I've seen notes where posters have suggested adding 'skip-grant-tables' to the MySQL my.cnf configuration file and restarting. Of course, that didn't work either. So I'm still stuck. Ideas on how I can get moving forward are appreciated!

 

[Faris Raouf]

The reason you can't connect to MySQL locally is that you can't use the unencrypted password.

Since Plesk 10.x. you need to use:

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

(note that those are backticks and not single quotes)

With regards to the other issues, it may be down to the way the password is stored. Take a look at some of the other threads relating to database access issues during the 10.5 upgrade and you'll see what needs to be done --- but note that I don't know if the issue you are experiencing has the same cause. (The basic solution was to try using mysql_upgrade to update the database tables).

 

[Nikolay.]

Check following file: /usr/local/sb/config

In [database] section you should see something like:

adapter = PDO_MYSQL
host = localhost
username = pp_sb_db
password = oaq21Az2xtNeSY
port = 3306
dbname = sitebuilder5

Then check that you can access MySQL DB with following command (substitute your own password from config file):

# mysql -upp_sb_db -poaq21Az2xtNeSY sitebuilder5

Then run following query:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow ` mysql -e "select length(Password), User, Host from user where User = 'pp_sb_db'"

If you can't access sitebuilder5 DB and/or the query above doesn't say 41 in the first column (for the single row), then resetting password for this user should help. Use a query like this to reset password:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow ` mysql -e "SET PASSWORD FOR 'pp_sb_db'@'localhost' = PASSWORD('oaq21Az2xtNeSY')"

Values in italic should match ones in your /usr/local/sb/config

Verify that after this operation two queries above work as expected.

 

[SacAutos]

Thank you for the advice. However, everything worked. I found the database section of /usr/local/sb/config and it seemed to be formatted properly. The first sitebuilder5 query worked as detailed. And the Password length query worked as well: exactly 41 in length. I even did the password reset. There seems to be a problem with accessing the license key server at the moment so I can't try the install again, though I would suspect that it would still fail. I'll try again later and report the results.

 

[Nikolay.]

Please post the output of:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow ` mysql -e "SELECT User, Host FROM user WHERE length(Password) != 41"


What's weird is that even restoring database from backup failed with the same "Access denied" error.

I suggest you trying upgrade again later and if it fails again, then contact somebody from Parallels (e.g. Kirill K) on this forum and provide them credentials to the server so they can check the real error reason. Also share a link to this thread with them if you do contact them.

 

[SacAutos]

It appears that I am cursed. I successfully updated another of our servers this morning. However, this one is still stuck. I tried the update yet again and still received the license key error:

Gathering information about installed license key...
File downloading PSA_11.5.30/plesk_getkeyinfo.sh: 27%..63%..100% was finished.
File downloading PSA_11.5.30/plesk_getkeyinfo.sh: 27%..63%..100% was finished.
Error in execution of '/root/parallels/PSA_11.5.30/plesk_getkeyinfo.sh'

Failed to check whether a new license key is available.
Error in execution of '/root/parallels/PSA_11.5.30/plesk_getkeyinfo.sh'

so I'm stuck there.

Going back to the earlier query, here's the result of the MySQL password query:

[root@vista ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow ` mysql -e "SELECT User, Host FROM user WHERE length(Password) != 41"
+------------------+-----------+
| User | Host |
+------------------+-----------+
| pma_b_x_SQ1_V0pc | localhost |
| pma_5Gp_91a4LlAB | localhost |
| pma_mN5SB3k1ANPs | localhost |
| pma_AVf4xv5iBLT_ | localhost |
| pma_y1HJPrVv3HN7 | localhost |
| pma_s7RR1nh5x9v2 | localhost |
| pma_00Np72MviNvN | localhost |
| pma_6eDBybwguKG1 | localhost |
| pma_yZFnoBBzO2zK | localhost |
+------------------+-----------+

Frustrated as I've lost my long holiday weekend window...

 

[Nikolay.]

Frustrated as I've lost my long holiday weekend window...

Sorry to hear that.

Good news though! If following command yields a user name (see in italics below) that is not in the query above and is a valid MySQL user name (you can query mysql.User table again to check that), then you can safely remove all other users from the query above and then upgrade should go smoothly.

# grep "controluser.*=" /usr/local/psa/admin/htdocs/domains/databases/phpMyAdmin/libraries/config.default.php
$cfg['Servers'][$i]['controluser'] = $GLOBALS['db_host'] != 'localhost' ? '' : 'pma_lrloBFmNZCoU';

Regarding the previous error - what is the output of:

# sh /root/parallels/PSA_11.5.30/plesk_getkeyinfo.sh 11.5.30; echo $?

You may skip or replace the line starting with PLSK in the output.

 

[weathermon]

Hi guys,

I'm experiencing the exact same problem trying to upgrade to 11.5 from 11.0:

===> Cumulative Plesk database upgrade (revertable stage) has been started.
===> Preparing Plesk database upgrade (revertable stage).
ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)

ERROR while trying to (to check that DB sitebuilder5 is accessible to user pp_sb_db), the query was:
Check the error reason(see log file: /tmp/plesk_11.5.30_installation.log), fix and try again


===> Restoring database from backup /var/lib/psa/dumps/mysql.preupgrade.11.0.9-11.5.30.20130820-101638.dump.gz

ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user 'pp_sb_db'@'localhost' (using password: YES)

[ERROR] Database restoring failed
Try to restore database from /var/lib/psa/dumps/mysql.preupgrade.11.0.9-11.5.30.20130820-101638.dump.gz manually


Trying to start psa...

**** Product prep-install for BASE failed.

***** problem report *****
ERROR while trying to (to check that DB sitebuilder5 is accessible to user pp_sb_db), the query was:
Check the error reason(see log file: /tmp/plesk_11.5.30_installation.log), fix and try again
STOP Bootstrapper 11.5.30 prep-install for BASE AT Tue Aug 20 10:17:17 EST 2013


[root@psa1 ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow ` mysql -e "SELECT User, Host FROM user WHERE length(Password) != 41"
+-----------------+---------------------+
| User | Host |
+-----------------+---------------------+
| billing | localhost |
| sitebuilder_db | localhost |
| roundcube | localhost |
| bug115166_68861 | % |
| biancabrady | % |
| bug115166_14860 | % |
| wordpress | % |
| aspcomplete | % |
| aspcomplete | localhost |
| aspcsk | % |
| aspcsk | localhost |
| aspcss | % |
| aspcss | localhost |
| aspsession | % |
| aspsession | localhost |
+-----------------+---------------------+


I deleted all the other pma_* users that weren't being used and only left those above users but still the same problem with access denied. I can access the database fine using the pp_sb_db username and password from /usr/local/sb/config

Cheers