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).