I am developing a Ruby on Rails application that uses MySQL as the database back-end. Everything has been progressing well until I decided to hook up the PHP piece to the same MySQL database. The PHP errors out with:

Warning: MySQL_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in … on line …

Fatal error: Cannot connect to MySQL database: Client does not support authentication protocol requested by server; consider upgrading MySQL client in … on line …

Apparently, MySQL has improved security via a new stronger authentication routine and the MySQL (mysql-essential-4.1.14-win32.msi) install I used defaulted to the new routine and PHP (php-4.4.1-installer.exe) defaulted to the old routine. After googling around I found that I could force MySQL to use the old authentication routine and fix the PHP issue by executing the following SQL:

use mysql;
update user set password = old_password('password') where user = 'root';
flush privileges;

The PHP scripts worked great, but when I went back and used the Ruby on Rails (RoR) piece I got the dreaded “Lost connection to MySQL server during query…” error. Grrr… I wanted both PHP and RoR to access mysql without issue. The solution I came up with was to create a MySQL account for PHP access and another for rails access. The PHP account was configured via:

use mysql;
update user set password = old_password('password') where user = 'phpuser';
flush privileges;

Now PHP and RoR access MySQL without issue and they use a least privilege access approach (instead of using the super powerful MySQL root account).