PHP5.3でMySQL接続エラー(old_passwords=1)

【環境】

接続元: PHP 5.3.20 (WebサーバA、新規)
接続先: MySQL 5.0.95 (MySQLサーバB、運用中)

【現象】

次のエラーが表示されてMySQLサーバに接続できない。
Warning (2): mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file [CORE\cake\libs\model\datasources\dbo\dbo_mysql.php, line 374]

xampp_1.5.4a(PHP5.1.4) → MySQLサーバB 接続OK
xampp_1.7.7(PHP5.3.8) → MySQLサーバB 接続ERR
コマンドプロンプトのmysqlコマンド → MySQLサーバB 接続OK
WebサーバA → 別のMySQLサーバ(MySQL5.5) 接続OK
つまりPHP5.3とMySQLサーバBの組み合わせで接続できない。新規のWebサーバAをPHP5.2やPHP5.1にすることはできない。

【原因】

PHP5.2から5.3になったとき、mysqlドライバ部分がmysqlndライブラリに変更された。mysqlndライブラリは41バイトのパスワードフォーマットを使用し、古い16バイトのパスワードフォーマットを使うとエラーを生成する。
http://php.net/manual/ja/migration53.incompatible.php

接続先のMySQLサーバのmy.cnfには old_passwords=1 が設定してあった。SELECT PASSWORD();を実行してみると16バイトのハッシュが表示された。またmysql.UserテーブルのPasswordカラムはchar(41)だが、保存されているデータは16バイトであった。

【検討】

新規のMySQLサーバの場合は、my.cnfのold_passwords=0にしてMySQLサーバを再起動し、DBユーザを新規作成したりパスワードを再設定すればよい。

ところがMySQLサーバBは運用中であり、DBユーザも登録されている。なるべく再起動せずにすむ方法、既存のDBユーザに影響しない方法を実施したい。本番サーバで操作する前に実験サーバで次の2つの確認をした。以下、WebサーバAから接続するDBユーザを ‘taro’@’%’、パスワード ‘1234’ とする。

【実験1】

1-1. old_passwords=0にしてMySQLを再起動し、既存のDBユーザ(16バイトハッシュ)で接続できるか?

mysqlコマンド 接続OK
PHP 5.2 接続OK
PHP 5.3 接続ERR

1-2. DBユーザ taro’@’%’のパスワードを設定する。

SET PASSWORD FOR taro@'%' = PASSWORD('1234');

1-3. DBユーザ ‘taro’@’%’ (41バイトハッシュ)で接続できるか?

mysqlコマンド 接続OK
PHP 5.2 接続OK
PHP 5.3 接続OK

【実験2】

2-1. 別のMySQLサーバ(old_passwords=0)を用意し、41バイトハッシュを生成した。

SELECT PASSWORD('1234');
*A4B6157319038724E3560894F7F932C8886EBFCF

2-2. 対象のMySQLサーバはold_passwords=1のままで、DBユーザのパスワードを41バイトハッシュにするために次のSQLを実行した。

SET PASSWORD FOR taro@'%' = '*A4B6157319038724E3560894F7F932C8886EBFCF';
を実行する。

2-3. mysql.userテーブルを表示して41バイトハッシュになっていることを確認した。

SELECT * FROM user WHERE Host='%' AND User='taro';

2-4. DBユーザ ‘taro’@’%’ (41バイトハッシュ)で接続できるか?

mysqlコマンド 接続OK
PHP 5.2 接続OK
PHP 5.3 接続OK

【まとめ】

実験1では、old_passwords=0 にしても既存のDBユーザの接続に影響がないことがわかった。
実験2では、old_passwords=0にしなくても、41バイトハッシュを設定することができた。

本番のMySQLサーバでは実験2の方法を実施した。