无法使用旧的身份验证连接到MySQL 4.1+(Cannot connect to MySQL 4.1+ using old authentication)

无法使用旧的身份验证连接到MySQL 4.1+(Cannot connect to MySQL 4.1+ using old authentication)

我试图连接到一个mySQL数据库在http://bluesql.net ，但是当我尝试连接，它给出了这个错误：

Connect Error (2000) mysqlnd cannot connect to MySQL 4.1+ using old authentication

我已经研究了这一点，它与MySQL 4.1之前使用的一些旧密码方案有关。 较新的版本可以选择使用旧的密码，我读过的可能会导致此问题。

我正在运行php 5.3，并连接到mySQLi（新的mysqli（...））。 我希望我可以在代码中做一些连接到bluesql.net的数据库 - 显然我不控制他们的数据库的设置。 降级php版本不是一个选项。

任何人都有什么想法？

I'm trying to connect to a mySQL database at http://bluesql.net, but when I try to connect, it gives this error:

Connect Error (2000) mysqlnd cannot connect to MySQL 4.1+ using old authentication

I've looked into this, and it has to do with some old password scheme used before MySQL 4.1. Newer versions have the option to use old passwords, which I've read may cause this problem.

I'm running php 5.3, and connecting with mySQLi (new mysqli(...)). I'm hoping I can do something in the code to connect to the DB at bluesql.net - clearly I don't control how their database is set up. Downgrading php versions isn't an option.

Anyone have any ideas?

最满意答案

编辑：这仅适用于您在MySQL服务器的控制...如果你不看看Mysql密码哈希方法旧对新

首先检查SQL查询

SHOW VARIABLES LIKE 'old_passwords'

（在MySQL命令行客户端， HeidiSQL或您喜欢的任何前端）是否将服务器默认设置为使用旧密码模式。 如果这返回old_passwords,Off只是在user表中刚刚拥有旧密码条目。 MySQL服务器将使用这些帐户的旧认证例程。 您可以简单地为该帐户设置一个新密码，并使用新的例程。

您可以通过查看mysql.user表（具有可访问该表的帐户）来检查将使用哪个例程。

SELECT `User`, `Host`, Length(`Password`) FROM mysql.user

对于具有旧密码的帐户，对于具有新密码的帐户，这将返回16 （对于没有密码的帐户，您可能还需要照顾）。 使用MySQL前端的用户管理工具（如果有的话）或

SET PASSWORD FOR 'User'@'Host'=PASSWORD('yourpassword'); FLUSH Privileges;

（将User和Host替换为从上一个查询获得的值）。然后再次检查密码的长度。 现在应该是41 ，你的客户端（如mysqlnd）应该能够连接到服务器。

另请参阅MySQL文档：* http://dev.mysql.com/doc/refman/5.0/en/old-client.html * http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html * http://dev.mysql.com/doc/refman/5.0/en/set-password.html

edit: This only applies if you are in control of the MySQL server... if you're not take a look at Mysql password hashing method old vs new

First check with the SQL query

SHOW VARIABLES LIKE 'old_passwords'

(in the MySQL command line client, HeidiSQL or whatever front end you like) whether the server is set to use the old password schema by default. If this returns old_passwords,Off you just happen to have old password entries in the user table. The MySQL server will use the old authentication routine for these accounts. You can simply set a new password for the account and the new routine will be used.

You can check which routine will be used by taking a look at the mysql.user table (with an account that has access to that table)

SELECT `User`, `Host`, Length(`Password`) FROM mysql.user

This will return 16 for accounts with old passwords and 41 for accounts with new passwords (and 0 for accounts with no password at all, you might want to take care of those as well). Either use the user management tools of the MySQL front end (if there are any) or

SET PASSWORD FOR 'User'@'Host'=PASSWORD('yourpassword'); FLUSH Privileges;

(replace User and Host with the values you got from the previous query.) Then check the length of the password again. It should be 41 now and your client (e.g. mysqlnd) should be able to connect to the server.

see also the MySQL documentation: * http://dev.mysql.com/doc/refman/5.0/en/old-client.html * http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html * http://dev.mysql.com/doc/refman/5.0/en/set-password.html