Main Content

MySQL - Password security (authentication protocol)

Archive - Originally posted on "The Horse's Mouth" - 2007-04-02 09:10:43 - Graham Ellis

Ever had this message?

bash> mysql -h www.weekendinwiltshire.co.uk -u weekend -p
Enter password: ******
Client does not support authentication protocol requested by server; consider upgrading MySQL client

The MySQL login password / security model changed between MySQL version 4.0 and version 4.1. In MySQL 4.0 (and prior) there were security risks that related to the password being intercepted between clients and the server, which were fixed with a new protocol from MySQL 4.1.

  1. Client programs built with the old (4.0 and prior) libraries cannot connect to new servers (4.1 and later) by default.
  2. The error message suggests that you upgrade your client programs - i.e. rebuild them with the new libraries or (in the case of clients such as mysql and mysqladmin) download fresh copies.
  3. If you are unable to upgrade your client (e.g. if it's commercial software or is provided to you as part of a shared / ISP service) you can instruct recent versions of MySQL to accept old-style passwords for specific accounts.


Here's the MySQL instruction that you'll need to set the old password mode for a particular account ...You will, though, have to run the actual command to set it from a new style client:

SET PASSWORD FOR "wwweb"@"localhost" = OLD_PASSWORD("paddington") ;

This issue of out-of-date clients is particularly relevant to PHP installations, where you're using an older version of PHP on a web server to contact a new MySQL - perhaps running on a different host. In the past, PHP shipped with the MySQL drivers but a new, and tighter, open source license applied to recent versions of MySQL precludes the shipping of parts of MySQL with PHP. The easiest solution is to download and install MySQL before you download and install PHP, and to ensure that you use PHP installation switches to pull in the mysql or mysqli functions as required, together with the appropriate drivers already installed with MySQL.

Further discussions on the MySQL upgrade here and more detail of the password hashing in MySQL here.