Wednesday, January 11, 2006

Mysql: Client Does not support authentication protocol


If you have ever gotten the error "Client does not support authentication protocol" when trying to use php or any other language to connect to a mysql server, there is a simple method to fix it.

Why Does this happen?

MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. If you upgrade the server from 4.1, attempts to connect to it with an older client may fail with the following message:
shell> mysql
Client does not support authentication protocol requested by server; consider upgrading MySQL client


To solve this problem, the following has been the easiest and most effective

-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Alternatively, use UPDATE and FLUSH PRIVILEGES: mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
-> WHERE Host = 'some_host' AND User = 'some_user';

Substitute the password you want to use for “newpwd” in the preceding examples. MySQL cannot tell you what the original password was, so you'll need to pick a new one.
Tell the server to use the older password hashing algorithm:

For each account record displayed by the query, use the Host and User values and assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE, as described earlier.

Note: In older versions of PHP, the mysql extension does not support the authentication protocol in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done in order to use this MySQL client library. For more information about the mysqli extension, see http://php.net/mysqli.


Post a Comment

Links to this post:

Create a Link

<< Home