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.
- Client programs built with the old (4.0 and prior) libraries cannot connect to new servers (4.1 and later) by default.
- 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.
- 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.
(written 2007-04-02)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S161 - Data Access and Security in MySQL [192] Current MySQL and PHP paths and upgrades - (2005-01-28)
[193] The wrong MySQL - (2005-01-29)
[401] What is an SQL injection attack? - (2005-08-02)
[535] MySQL permissions and privileges - (2005-12-20)
[647] Checking for MySQL errors - (2006-03-15)
[947] What is an SQL injection attack? - (2006-11-27)
[2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
[2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
[2647] Removing duplicates from a MySQL table - (2010-02-22)
[3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
[4390] Checking MySQL database backups have worked (not failed) - (2015-01-10)
[4406] Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25)
[4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
[4491] Web Server Admin - some of those things that happen, and solutions - (2015-05-10)
[4493] Forgotten / lost MySQL root password - (2015-05-16)
S159 - MySQL 5 and PHP 5 [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
[494] MySQL - a score of things to remember - (2005-11-12)
[515] MySQL - an FAQ - (2005-12-03)
[1417] What software version do we teach? - (2007-10-31)
[1455] Connecting to MySQL 5 from PHP on Mac OSX Leopard - (2007-12-03)
[1754] Upgrade from PHP 4 to PHP 5 - the TRY issue - (2008-08-15)
[1766] Diagrams to show you how - Tomcat, Java, PHP - (2008-08-22)
[2172] PHP4 v PHP5 - Object Model Difference - (2009-05-11)
[3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
S153 - Sourcing, Running and Configuring MySQL [334] Symbolic links and hard links - (2005-06-02)
[489] Which MySQL server am I using? - (2005-11-07)
[591] Key facts - SQL and MySQL - (2006-02-04)
[907] Browser -> httpd -> Tomcat -> MySQL. Restarting. - (2006-10-28)
[1095] Apache httpd , browser, MySQL and MySQL client downloads - (2007-02-28)
[1123] mysqldump and mysqlrestore - (2007-03-30)
[1689] Some sideways thoughts on the news - (2008-06-27)
[1731] Apache httpd, MySQL, PHP - installation procedure - (2008-08-01)
[1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
[1935] Summary of MySQL installation on a Linux system - (2008-12-11)
[2085] MySQL - licensing issues, even with using the name - (2009-03-16)
[2209] Monitoring and Tuning your MySQL installation - (2009-05-31)
[2426] Which version of MySQL am I running? - (2009-09-26)
[2444] Potted MySQL installation - (2009-10-09)
[2445] Securing MySQL on a production server - (2009-10-09)
[2458] Cant connect to local MySQL server through socket /tmp/mysql.sock - (2009-10-17)
[4487] Starting MySQL. ERROR! The server quit without updating PID file - how we fixed it. - (2015-05-06)
Some other Articles
Government pressure on me to shut up after I make the papersFunction / method parameters with * and ** in PythonB-2-B Networking, 20th April. Useful for YOUR business?Well House Manor, Melksham, Art GalleryMySQL - Password security (authentication protocol)Science Museum - larger exhibits / store, Wroughton, SwindonGolfing, Wiltshire (near Melksham)Avon Vale hunt at the Point to PointA day at the racesMacFormat - Me and My Mac