admin@localhost failing to authenticate with MySQL

[SyncError]

We were recently having issues with our Plesk server running incredibly slow (often timing out when trying to access the Plesk Control Panel). Our VPS provider made some fixes and at the time requested our admin password.

I changed the admin password via the Control Panel, however, apparently used a password that was too long and no error checks were in place to let me know of the error. We were no longer able to log in to the Plesk Control panel at all. I was instructed to change the password in the /etc/psa/.psa.shadow file. Since doing so we have been able to log into the Plesk Control panel however we have not been able to restart the PSA.

When attempting to start the MySQL or PSA service we receive error messages that the 'admin@localhost' is failing to authenticate.

To fix the problem I know I most likely need to either reset the admin@localhost MySQL user's password or delete and recreate the user. However, I have been unable to do so.

I am able to start the PSA only by running my MySQL with the skip-grant-tables option, which is a short term solution at best due to the security issues. I log into the mysql console using root and attempt to reset the admin@localhost password, but always receive an error message.

My problem seems to be the same as found in this old thread [here].

If I attempt to reset the password:
SET PASSWORD FOR admin=PASSWORD('newpassword');

I get the following error:
'ERROR 1132: You must have privileges to update tables in the mysql database to be able to change passwords for others'.

I also attempted to reset the password from within the Plesk Console since I have access again, however, that seem to have no effect on the admin@locahost MySQL user as I had hoped.

Has anyone had and found a solution to this problem?

 

[jamesyeeoc]

In the old post, did you try PaulC's exact procedure?

Posted by PaulC on 8th December 2001 06:38 PM:

Ok

The password inside mysql doesn't match the one that PSA needs to run.

To fix this run these commands

/usr/local/psa/rc.d/psa stop
/usr/local/psa/mysql/bin/safe_mysqld --skip-grant-tables &
<hit enter>
/usr/local/psa/mysql/bin/mysql
SET PASSWORD FOR admin=PASSWORD('setup');
FLUSH PRIVILEGES;
kill `cat /usr/local/psa/mysql/var/*.pid`
/usr/local/psa/rc.d/psa start

Replace setup with your real PSA admin password

Another thing to possibly try would be to install phpMyAdmin and see if that will allow you to manually change the password stored in the database.

 

[SyncError]

With the exception that my paths are different and that I have no rc.d in my /usr/local/psa, yes I have attempted to follow his procedures exactly.

I had actually attempted this before I found the thread, but tried again after reading his support answers.

I stop the psa service:

[root@binarywisdom psa]# service psa stop
===> Reading /etc/psa/psa.conf ...
/usr/local/psa/admin/bin/httpsdctl stop: httpd stopped
Stopping Plesk:                                            [  OK  ]
service psa stop

I edit my /etc/my.cnf file to look like so:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I start mysqld:

[root@binarywisdom etc]# service mysqld start
Starting MySQL:                                            [  OK  ]

I enter the myql console to enter commands:

[root@binarywisdom psa]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 251 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET PASSWORD FOR admin=PASSWORD('newpass');
ERROR 1132: You must have privileges to update tables in the mysql database to be able to change passwords for others
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.19 sec)

mysql> exit
Bye

I can continue to kill all my MySQL, restart it and PSA, but as you can see it fails to set the admin@localhost password.

I have also attempted this via phpMyAdmin and receive the same error code. I've also attempted to reset the password while logged in as both root@localhost and admin@localhost.

 

[jamesyeeoc]

Do you not have the 'safe_mysqld' daemon on your system?

/usr/local/psa/mysql/bin/safe_mysqld --skip-grant-tables &
<hit enter>

On my 7.5.3 system it is in /usr/bin

 

[SyncError]

I'm running safe_mysqld:

[root@binarywisdom psa]# ps -u root
  PID TTY          TIME CMD
    1 ?        00:00:00 init
15872 ?        00:00:38 syslogd
16021 ?        00:00:00 sshd
16031 ?        00:00:00 xinetd
19960 ?        00:00:00 qmail-lspawn
20336 ?        00:00:00 spamd
21184 ?        00:00:00 httpd
21851 ?        00:00:00 httpd
23585 ?        00:00:00 crond
24000 ?        00:00:02 smbd
24193 ?        00:00:00 smbd
24226 ?        00:00:00 nmbd
24545 ?        00:00:00 saslauthd
25248 ?        00:00:00 sshd
26720 pts/0    00:00:00 bash
 5016 pts/0    00:00:00 safe_mysqld
 9378 ?        00:00:00 httpsd
14849 ?        00:00:00 crond
14880 ?        00:00:00 crond
14914 ?        00:00:00 qmail_check
14944 ?        00:00:00 crond
19731 pts/0    00:00:00 ps
19739 ?        00:00:00 chkrcptto
20166 ?        00:00:00 qmail_check
20171 ?        00:00:00 qmail-qstat
20173 ?        00:00:00 grep
20192 ?        00:00:00 sed
20675 ?        00:00:00 smbd
20692 ?        00:00:00 qmail-qstat
20693 ?        00:00:00 find
20694 ?        00:00:00 wc
[root@binarywisdom psa]#

I'm running 7.5 and its located in /usr/bin. Like I said, as far as I can tell the only deviation from Paul's how-to would be paths and perhaps version numbers.

 

[jamesyeeoc]

Ok, was just wondering since your post referred to manually running the normal mysqld

I'm out of suggestions

 

[jamesyeeoc]

Using phpMyAdmin, are you able to browse the database 'mysql', table 'users' and see if admin has 'Y' for all privs? Sorry, that's probably what you already tried....

 

[SyncError]

I've just about given up as well, but now I've discovered something that just makes this all the more confusing.

[root@binarywisdom bin]# /usr/bin/mysql -uadmin -p[b]correctpass[/b]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 403 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

[root@binarywisdom bin]# /usr/bin/mysql -uadmin -p[b]wrongpass[/b]
ERROR 1045: Access denied for user: 'admin@localhost' (Using password: YES)
[root@binarywisdom bin]#

[root@binarywisdom bin]# /usr/bin/mysql -uadmin -p[b]'cat /etc/psa/.psa.shadow'[/b]
ERROR 1045: Access denied for user: 'admin@localhost' (Using password: YES)
[root@binarywisdom bin]#

So now what does this mean? My 'admin@localhost' does indeed have the correct password, and it does have the same password as whats in my /etc/psa/.psa.shadow file... BUT when I tell it to read from the shadow file it fails. It appears that its is either having trouble reading the shadow file properly or the text inside the file needs to be formatted differently?

If I vi my /etc/psa/.psa.shadow file it just looks like this:

correctpass
~
~
~
~

Is it missing any formatting?

 

[jamesyeeoc]

No formatting, file should have just the single line consisting of the password, mine doesn't even have a LF or CR at the end.

File should have ownership psaadm : psaadm

Perms: -rw-------

You could try renaming the file and create a new one:

mv .psa.shadow .psa.shadow.bad
echo correctpass > .psa.shadow
chown psaadmsaadm .psa.shadow
chmod 600 .psa.shadow

And just for grins, check that psaadm belongs to the right groups:

[root@ns2 root]# groups psaadm
psaadm : psaadm psaserv

 

[SyncError]

/me scratches his head.

Okay first of all you got my hopes up. My shadow file was file both in ownership and permissions, but I went ahead and assumed it could be corrupted in some way, so I backed it up.

[root@binarywisdom psa]# mv .psa.shadow .psa.shadow.bad
[root@binarywisdom psa]# echo correctpass> .psa.shadow
[root@binarywisdom psa]# chown psaadm:psaadm .psa.shadow
[root@binarywisdom psa]# chmod 600 .psa.shadow

I then attempted to check the file as I had before by using a cat statement in my mysql login... (that IS supposed to be possible right?):

[root@binarywisdom psa]# /usr/bin/mysql -uadmin -p'cat /etc/psa/.psa.shadow'
ERROR 1045: Access denied for user: 'admin@localhost' (Using password: YES)

Hhmm, didn't work. Okay, so I figure, let's try it all again anyways. I shutdown PSA and MYSQL:

[root@binarywisdom psa]# service psa stop
===> Reading /etc/psa/psa.conf ...
/usr/local/psa/admin/bin/httpsdctl stop: httpd stopped
Stopping Plesk:                                            [  OK  ]
[root@binarywisdom psa]# service mysql stop
mysql: unrecognized service
[root@binarywisdom psa]# service mysqld stop
Stopping MySQL:                                            [  OK  ]

I then vi and comment out my skip-grant-tables parameter, and attempt to restart MySQL:

[root@binarywisdom psa]# vi /etc/my.cnf
[root@binarywisdom psa]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]

I check my pids. Even though the MySQL Daemon failed to start (or so it says) there is both a process owned by 'root' and 'msyql' running. I scratch my head for a second and try it all again. I kill all the processes owned by root and mysql related to safe_mysqld and mysqld. Then attempt to begin it all again.

Look below as I fail to start MySQL, see that a safe_mysqld is indeed started, choose to start PSA even though MySQL didn't launch. Then somehow, PSA launches successfully:

[root@binarywisdom psa]# ps -umysql
  PID TTY          TIME CMD
[root@binarywisdom psa]# ps -uroot
  PID TTY          TIME CMD
    1 ?        00:00:00 init
15872 ?        00:00:58 syslogd
16021 ?        00:00:00 sshd
16031 ?        00:00:00 xinetd
19960 ?        00:00:00 qmail-lspawn
20336 ?        00:00:00 spamd
21184 ?        00:00:01 httpd
21851 ?        00:00:00 httpd
23585 ?        00:00:00 crond
24000 ?        00:00:03 smbd
24193 ?        00:00:00 smbd
24226 ?        00:00:00 nmbd
24545 ?        00:00:00 saslauthd
25248 ?        00:00:00 sshd
26720 pts/0    00:00:00 bash
32321 pts/0    00:00:00 ps
[root@binarywisdom psa]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]
[root@binarywisdom psa]# ps -uroot
  PID TTY          TIME CMD
    1 ?        00:00:00 init
15872 ?        00:00:58 syslogd
16021 ?        00:00:00 sshd
16031 ?        00:00:00 xinetd
19960 ?        00:00:00 qmail-lspawn
20336 ?        00:00:00 spamd
21184 ?        00:00:01 httpd
21851 ?        00:00:00 httpd
23585 ?        00:00:00 crond
24000 ?        00:00:03 smbd
24193 ?        00:00:00 smbd
24226 ?        00:00:00 nmbd
24545 ?        00:00:00 saslauthd
25248 ?        00:00:00 sshd
26720 pts/0    00:00:00 bash
 1792 pts/0    00:00:00 safe_mysqld
 8055 pts/0    00:00:00 ps
[root@binarywisdom psa]# service psa start
===> Reading /etc/psa/psa.conf ...
Reloading configuration:                                   [  OK  ]
Starting psa-spamassassin service:                         [  OK  ]
Processing config directory: /usr/local/psa/admin/conf/httpsd.*.include
/usr/local/psa/admin/bin/httpsdctl start: httpd started
Starting Plesk:                                            [  OK  ]
[root@binarywisdom psa]#

This basically means I'm fixed. But I'd like to know why it says the MySQL service fails to start. I also still need to test this on a reboot and see if the services will begin on startup or if I have to ssh in and do this routine everytime.

 

[SyncError]

I issued a shutdown -r now, waited a second, and ssh'd back in.

I did a ps -u root... it showed safe_mysqld running but not psa.

I logged into my Plesk Control panel, went to Virtozzo > System Services. It showed everything but PSA and Samba running. I gave it a second and refreshed. Then it showed all services up and running.

I returned to ssh, redid a ps -u root... same. Shows safe_mysqld running but not psa. I seem to remember that it should infact show PSA running as a process owned by 'root' but it doesn't. Either way its up and running and functional.

Again, I appear to be all fixed up, but I don't know why it was reporting [FAILED] on starting up MySQL in ssh, nor do I know why psa is no longer showing as a process owned and running by 'root'.

Do you think I'm set or have more work ahead of me?

 

[jamesyeeoc]

I'm wondering if mysqld failed to start due to a problem accessing or finding the .sock or .pid files.

I don't show process 'psa' running in a ps list.

I do show the following mysqld running with it's startup params:

[root@ns2 etc]# ps -ax |grep 'mysql'
4408 ? S 0:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf
4451 ? S 0:13 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock
11242 pts/0 S 0:00 grep mysql

 

[SyncError]

[root@binarywisdom root]# ps -ax |grep 'mysql'
Warning: bad syntax, perhaps a bogus '-'? See [url]http://procps.sf.net/faq.html[/url]
20676 ?        S      0:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.                                    cnf
20700 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20701 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20702 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20703 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20705 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20738 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20769 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20803 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20834 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20865 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
20896 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
25632 ?        S      0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --bas                                    edir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysql                                    d.pid --skip-locking
16064 pts/1    S      0:00 grep mysql
[root@binarywisdom root]#

 

[jamesyeeoc]

Your post truncated the lines. The error reported just means to use ps aux instead.

So I take it the reboot did go ok then and mysqld did start ok.?