Authentication problem while using MySQL Version 4.X

Thursday, November 11th, 2004

Authentication problem while using MySQL Version 4.X

Created 2004-11-11 by Hamid Ali Raja

I am facing problem while writing to MySQL 4.X database using Write to Database action. What should I do?

This issue is related to MySQL authentication protocol built in MySQL 4.1 and above versions. This protocol is based on password hashing algorithm that is not compatible with the one used by older clients and stores passwords differentally as compared to older versions. Therefore, if you upgrade your server to 4.X and try to connect with the older client, you may end in a failure.

You can do one of the following options to solve this problem.

1. You can upgrade your all client programs to use 4.1.1 or newer client library.

2. Use pre-4.1 style password to use a pre-4.1 client program.

You can use SET PASSWORD statement and the OLD_PASSWORD() function to reset the password for user who wants to use pre-4.1 client program.

mysql> SET PASSWORD FOR-> ‘someuser_abc’@’somehost_xyz’ = OLD_PASSWORD(‘somenewpwd’);

You can also use UPDATE and FLUSH privileges to reset the password:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(‘somenewpwd’) -> WHERE Host = ‘somehost_xyz’ AND User = ‘someuser_abc’;

Specify the password you want to use for “somenewpwd”. You can not get your old password from MySQL, so select the new one.

3. Configure your server to use older password hashing algorithm:

a) Start mysqld with the –old-passwords option.
b) You can identify accounts those had updated their passwords to longer 4.1 format using the following query:

mysql> SELECT host, user, password FROM mysql.user -> WHERE LENGTH(Password) >16;

Now you can reset the password for records displayed by the query using the host and user values and assign a password using the OLD_PASSWORD() function. You can either use SET PASSWORD or UPDATE, as discussed above.