1. Please take a little time for this simple survey! Thank you for participating!
    Dismiss Notice

admin@localhost failing to authenticate with MySQL

Discussion in 'Plesk for Linux - 8.x and Older' started by SyncError, Jul 16, 2005.

  1. SyncError

    SyncError Guest

    0
     
    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?
     
  2. jamesyeeoc

    jamesyeeoc Guest

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

    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.
     
  3. SyncError

    SyncError Guest

    0
     
    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:
    Code:
    [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:
    Code:
    [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:
    Code:
    [root@binarywisdom etc]# service mysqld start
    Starting MySQL:                                            [  OK  ]
    
    I enter the myql console to enter commands:
    Code:
    [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.
     
  4. jamesyeeoc

    jamesyeeoc Guest

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

    On my 7.5.3 system it is in /usr/bin
     
  5. SyncError

    SyncError Guest

    0
     
    I'm running safe_mysqld:

    Code:
    [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.
     
  6. jamesyeeoc

    jamesyeeoc Guest

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

    I'm out of suggestions
     
  7. jamesyeeoc

    jamesyeeoc Guest

    0
     
    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....
     
  8. SyncError

    SyncError Guest

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

    Code:
    [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:
    Code:
    correctpass
    ~
    ~
    ~
    ~
    
    Is it missing any formatting?
     
  9. jamesyeeoc

    jamesyeeoc Guest

    0
     
    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 psaadm:psaadm .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
     
  10. SyncError

    SyncError Guest

    0
     
    /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.

    Code:
    [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?):
    Code:
    [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:
    Code:
    [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:
    Code:
    [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:

    Code:
    [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.
     
  11. SyncError

    SyncError Guest

    0
     
    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?
     
  12. jamesyeeoc

    jamesyeeoc Guest

    0
     
    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
     
  13. SyncError

    SyncError Guest

    0
     
    Code:
    
    [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]#
    
     
  14. jamesyeeoc

    jamesyeeoc Guest

    0
     
    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.?
     
Loading...