Re: "mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client"

[Mark Rosenthal]

As near as I can tell, a great deal of the confusion has to do with lack of coordination between the MySQL and the PHP teams.

The MySQL side of the issue is discussed in detail in the MySQL reference manual, section "5.5.9 Password Hashing in MySQL 4.1". To improve security, in MySQL 4.1.? the width of the hashed password has been increased from what it was in MySQL 4.0.? and before. It used to be 16 bytes. in 4.1.0, they increased it to 45 bytes, but they seem to have decided to reduce it to 41 bytes in 4.1.1 and thereafter. So 41 bytes is now the standard size of the password hash.

MySQL clients distributed after the change know enough to figure out what the server they're talking to wants to see, and send it the right size password. Old clients will always send a 16 byte hashed password.

So, what counts as a client? Anything which links in the mysqlclient library. All the utilities provided in the MySQL distribution link that in - notably the command line executables "mysql" and "mysqladmin".

But what about PHP? In order to make a client interface available to PHP programmers, the code for the API has to link in the mysql client library. So, what version of the mysql client library is the PHP MySQL API using? The only clues are in the PHP manual. Section "LXXI. MySQL Functions" states, "This MySQL extension doesn't support full functionality of MySQL versions greater than 4.1.0. For that, use MySQLi." Could it be that the PHP developers are trying to encourage programmers to migrate to from the PHP mysql extension to the PHP mysqli extension by not supporting 41 byte MySQL password hashes under the old PHP mysql extension? I can't tell for sure. In section "LXXII. Improved MySQL Extension" they tell the person installing PHP to use a special option, and then say, "If you would like to install the mysql extension along with the mysqli extension you have to use the same client library to avoid any conflicts." Maybe installing PHP according to those instructions will allow both the old and the new PHP extensions to authenticate a user with both pre- and post-4.1.1 MySQL servers.

The solution recommended in section 5.5.9 of the MySQL reference manual is to start the server with the "--old-passwords" flag. This doesn't force it to use old passwords. It just tells the server that it's allowed to talk to a client that doesn't understand the new password size.

You ought to be able to enable old passwords in the my.ini file instead of on the command line, but I haven't been able to find definitive documentation on the syntax. I can't find it mentioned in the manual, but some documentation is available at the command line. The command "mysqld --help --verbose" reports that one of the variables you can set is "old-passwords", whereas the command "mysqladmin variables" queries the running server and reports that one of its variables is called "old_passwords".

Is the proper name of the variable in the my.ini file "old_passwords" with an underscore or "old-passwords" with a hyphen? Every other variable in my.ini is assigned a value, as in "port=3306". Does "old_passwords" take a value. If so what are legal values? "true", "false", "on", "off"? Does case matter? None of this seems to be documented. After trying all possibilities in the my.ini file, restarting the server every time I changed the file, and running "mysqladmin variables" each time, I found that the variable doesn't take a value, but is accepted with either an underscore or a hyphen. To allow the server to accept passwords from old clients, put the following lines into the [mysqld] section of the my.ini file:

# Enable old clients that can only send 16 byte password hashes
old-passwords

But that's only part of the story. The 'password' column in the 'user' table has to be wide enough to store a 41 byte password hash, but it can still contain a 16 byte hash. Simply telling the server that it's allowed to talk to an old client isn't enough to allow that client to log in. The hashes also have to match. The PASSWORD() function has been changed to produce a 41 byte hash. To produce a 16 byte hash, you have to call the OLD_PASSWORD() function.

So the things you have to pay attention to are:

1. the width of the 'password' column in the 'user' table in the 'mysql' database
2. the width of the password hash that's actually stored in that column
3. whether or not the server has permission to talk to an old client
4. the vintage of the client

---
Mark Rosenthal <[e-mail]>
Open Source Software Consultant
Arlington Software Enterprises