升級 PHP 至 5.3+ 後,舊專案呼叫 mysql_connect() 會拋出警告:
PHP Warning: 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.
mysql.user 表的 Password 欄位儲存 16 位雜湊值。mysqlnd 驅動,要求 41 位雜湊值進行驗證。my.cnf 設定 old_passwords=1,PASSWORD() 函數僅產生 16 位雜湊,導致驗證失敗。mysql -u root -pSET old_passwords = 0;
UPDATE mysql.user SET PASSWORD = PASSWORD('phpddt.com') WHERE User = 'root' LIMIT 1;
SELECT LENGTH(PASSWORD) FROM mysql.user WHERE User = 'root'; -- 應回傳 41
FLUSH PRIVILEGES;
old_passwords=1 的 session,或修改 my.cnf 移除 old_passwords 旗標後重啟服務。mysqli 或 PDO_MySQL,並啟用 caching_sha2_password(MySQL 8.0+ 預設)以獲得更強安全性。完成上述步驟後,PHP 即可正常連線,不再出現舊式驗證警告。