asked on

Strange access denied problem

Hi,

Yesterday I signed up for another dedicated linux server with the hosting company I use and I am having problems getting MySQL 4.1.3 working on it. I've tried just about everything I can think of to login via the MySQL client but get a variation of the following error every time:

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

I put skip-grant-tables in to my.cnf and restarted the mysqld service and logged in to the client as root no problem. I manually cleaned up the User table by resetting all the passwords using:

UPDATE mysql.user SET Password=PASSWORD('mynewpass');
FLUSH PRIVILEGES;

and granting all priveleges to all users. My User table now looks like this:

+-----------------------+------------+------------------+
| host | user | password |
+-----------------------+------------+------------------+
| localhost | root | *EAA2B914CE13E7B |
| srv01.mysite.net | root | *EAA2B914CE13E7B |
| localhost | wpphpadmin | *EAA2B914CE13E7B |
| localhost | myuser | *EAA2B914CE13E7B |
| localhost.localdomain | myuser | *EAA2B914CE13E7B |
| 127.0.0.1 | myuser | *EAA2B914CE13E7B |
+-----------------------+------------+------------------+

I've cut off the priveleges for readability but they are all set to Y apart from Grant_Priv which only root has.

When I remove the skip-grant-tables from the my.cnf file and restart the service I get the following errors when I try to login:

mysql -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql -h 127.0.0.1 -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'localhost.localdomain' (using password: YES)

mysql -h srv01.mysite.net -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'srv01.mysite.net' (using password: YES)

mysql -h localhost -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql -u wpphpadmin -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'wpphpadmin'@'localhost' (using password: YES)

etc.

The same goes for the myuser user as well. None of them seem to work.

My setup is:
Redhat Linux 2.4.21-4.0.1
Apache 2.0.46
PHP 4.3.2
MySQL 4.1.3
Ensim WEBppliance Pro 4.0.0-14

Any help you can give me would be much appreciated.

Thanks

Steve

ASKER CERTIFIED SOLUTION

psadac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveMcNabb

ASKER

I installed it from the RPMs off the mysql.com site.

Here are the permissions I have at the moment:

/var/lib/
drwxr-xr-x 5 mysql mysql 4096 Jul 12 09:52 mysql

/var/lib/mysql/ (everything else is mysql:mysql)
drwx--x--x 2 mysql mysql 4096 Jul 12 09:51 mysql
lrwxrwxrwx 1 root root 54 Jul 12 09:52 mysql.sock -> /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/
srwxrwxrwx 2 mysql mysql 0 Jul 12 09:52 mysql.sock

/var/
drwxr-xr-x 13 root root 4096 Jul 12 09:54 log

/var/log/
-rw-r----- 1 mysql mysql 48118 Jul 12 09:52 mysqld.log
-rw-r----- 1 mysql mysql 1367 Jul 11 08:38 mysqld.log.1

I did find a strange line in mysqld.log which I had missed:

mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run

So I put skip-grant-tables back in to my.cnf, restarted the service and ran mysql_fix_privilege_tables. I noticed it had changed the user table quite a lot so I ran the following again:

UPDATE mysql.user SET Password=PASSWORD('mynewpass');
FLUSH PRIVILEGES;

I removed skip-grant-tables, restarted the service but I'm back to the same problem again. The user table now looks like this:

+-----------------------+------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+-----------------------+------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| localhost | root | *72913D1E9DDC2B402B075AEAB2D3BBB2F4B9783C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 |
| srv01.mysite.net | root | *72913D1E9DDC2B402B075AEAB2D3BBB2F4B9783C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 |
| localhost | wpphpadmin | *72913D1E9DDC2B402B075AEAB2D3BBB2F4B9783C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 |
| localhost | myuser | *72913D1E9DDC2B402B075AEAB2D3BBB2F4B9783C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 |
| localhost.localdomain | myuser | *72913D1E9DDC2B402B075AEAB2D3BBB2F4B9783C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 |
| 127.0.0.1 | myuser | *72913D1E9DDC2B402B075AEAB2D3BBB2F4B9783C | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 |
+-----------------------+------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

Sorry for the spam but there might be something here that I'm missing.

After the latest restart I checked the mysqld.log file and got the following:

040712 09:52:33 mysqld started
040712 9:52:33 Warning: Asked for 196608 thread stack, but got 126976
040712 9:52:33 InnoDB: Started; log sequence number 0 43644
/usr/sbin/mysqld: ready for connections.
Version: '4.1.3-beta-standard' socket: '/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock' port: 3306

Not sure if this warning is important.

SteveMcNabb

ASKER

Ok, I think I've got it sorted out now.

I think I got an error on my last FLUSH PRIVILEGES for I restarted the server and tried again and got logged in with all users.

On a side note, I ran in to problems when I tried to connect using anything except the MySQL client. I was getting:

Client does not support authentication protocol requested by server phpmyadmin

So I added old-passwords to my.cnf and redid all the passwords with:

UPDATE mysql.user SET Password=OLD_PASSWORD('mynewpass');
FLUSH PRIVILEGES;

and it worked. Just including this in case anyone else has the same problems.

I'm going to leave this open for a bit psadac in case I've any related problems but the points will be yours for setting me on the right course :-)

kuddel

Here is an additional information:
The official documentation of phpMyAdmin (http://phpmyadmin.sourceforge.net/documentation/) says under FAQ [1.17a]:
Question: I cannot connect to the MySQL server. It alaways returns the error message, "Client does not support authentication protocol requested by server; consider upgrading MySQL client"
Answer: You tried to access MySQL with an old MySQL client library. The version of your MySQL client library can be checked in your phpinfo() output.
In general, it should have at least the same minor version as your server - as mentioned in FAQ 1.17.
The only way to fix this problem is manually compiling php and its MySQL extension against a current MySQL client library.
If you still face this problem, please compile your MySQL client library against the same or a newer MySQL release.

Hope this helps someone.

kuddel