php 无法使用旧身份验证连接到 MySQL 4.1+

Cannot connect to MySQL 4.1+ using old authentication

提问by B T

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

我正在尝试连接到http://bluesql.net上的 mySQL 数据库，但是当我尝试连接时，出现以下错误：

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.

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

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.

我正在运行 php 5.3，并与 mySQLi（新的 mysqli(...)）连接。我希望我可以在代码中做一些事情来连接到 bluesql.net 上的数据库 - 显然我无法控制他们的数据库是如何设置的。降级 php 版本不是一种选择。

Anyone have any ideas?

谁有想法？

回答by VolkerK

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

编辑：这仅适用于您控制 MySQL 服务器的情况...如果您不看看Mysql 密码散列方法 old vs new

First check with the SQL query

首先检查SQL查询

SHOW VARIABLES LIKE 'old_passwords'

(in the MySQL command line client, HeidiSQLor whatever front end you like) whether the server is set to use the old password schema by default. If this returns old_passwords,Offyou just happen to have old password entries in the usertable. 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.

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

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

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

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

This will return 16for accounts with old passwords and 41for 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

这将为使用旧密码的帐户返回16，为使用新密码的帐户返回41（对于根本没有密码的帐户，返回 0，您可能也需要注意这些）。
要么使用 MySQL 前端的用户管理工具（如果有的话）要么

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

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

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

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

另请参阅 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

回答by TehShrike

If you do nothave control of the server

如果您无法控制服务器

I just had this issue, and was able to work around it.

我刚刚遇到了这个问题，并且能够解决它。

First, connect to the MySQL database with an older client that doesn't mind old_passwords. Connect using the user that your script will be using.

首先，使用不介意 old_passwords 的旧客户端连接到 MySQL 数据库。使用您的脚本将使用的用户进行连接。

Run these queries:

运行这些查询：

SET SESSION old_passwords=FALSE;
SET PASSWORD = PASSWORD('[your password]');

In your PHP script, change your mysql_connect function to include the client flag 1:

在 PHP 脚本中，更改 mysql_connect 函数以包含客户端标志 1：

define('CLIENT_LONG_PASSWORD', 1);
mysql_connect('[your server]', '[your username]', '[your password]', false, CLIENT_LONG_PASSWORD);

This allowed me to connect successfully.

这使我能够成功连接。

Edit:as per Garland Pope's comment, it may not be necessary to set CLIENT_LONG_PASSWORD manually any more in your PHP code as of PHP 5.4!

编辑：根据Garland Pope的评论，从 PHP 5.4 开始，可能不再需要在 PHP 代码中手动设置 CLIENT_LONG_PASSWORD 了！

Edit:courtesy of Antonio Bonifati, a PHP script to run the queries for you:

编辑：由Antonio Bonifati 提供，这是一个为您运行查询的 PHP 脚本：

<?php const DB = [ 'host' => '...', # localhost may not work on some hosting 
    'user' => '...',
    'pwd' => '...', ]; 

if (!mysql_connect(DB['host'], DB['user'], DB['pwd'])) {
    die(mysql_error());
} if (!mysql_query($query = 'SET SESSION old_passwords=FALSE')) {
    die($query);
} if (!mysql_query($query = "SET PASSWORD = PASSWORD('" . DB['pwd'] . "')")) {
    die($query);
}

echo "Excellent, mysqli will now work"; 
?>

回答by Elliot Yap

you can do these line on your mysql query browser or something

你可以在你的 mysql 查询浏览器或其他东西上做这些行

SET old_passwords = 0;
UPDATE mysql.user SET Password = PASSWORD('testpass') WHERE User = 'testuser' limit 1;
SELECT LENGTH(Password) FROM mysql.user WHERE User = 'testuser';
FLUSH PRIVILEGES;

note:your username and password

注意：您的用户名和密码

after that it should able to work. I just solved mine too

之后它应该可以工作。我也刚解决了我的

回答by Chad Skeeters

On OSX, I used MacPorts to address the same problem when connecting to my siteground database. Siteground appears to be using 5.0.77mm0.1-log, but creating a new user account didn't fix the problem. This is what did

在 OSX 上，当连接到我的 siteground 数据库时，我使用 MacPorts 来解决同样的问题。Siteground 似乎使用 5.0.77mm0.1-log，但创建新用户帐户并没有解决问题。这是做的

sudo port install php5-mysql -mysqlnd +mysql5

This downgrades the mysql driver that php will use.

这会降级 php 将使用的 mysql 驱动程序。

回答by Chakravarthi Bharathi

Had the same issue, but executing the queries alone will not help. To fix this I did the following,

有同样的问题，但单独执行查询无济于事。为了解决这个问题，我做了以下事情，

1. Set old_passwords=0 in my.cnf file
2. Restart mysql
3. Login to mysql as root user
4. Execute FLUSH PRIVILEGES;

1. 在 my.cnf 文件中设置 old_passwords=0
2. 重启mysql
3. 以root用户登录mysql
4. 执行同花顺特权；

回答by Robert Luman

If you do not have Administrator access to the MySQL Server configuration (i.e. you are using a hosting service), then there are 2 options to get this to work:

如果您没有 MySQL 服务器配置的管理员访问权限（即您正在使用托管服务），则有两个选项可以使其工作：

1) Request that the old_passwords option be set to false on the MySQL server

1) 请求在 MySQL 服务器上将 old_passwords 选项设置为 false

2) Downgrade PHP to 5.2.2 until option 1 occurs.

2) 将 PHP 降级到 5.2.2，直到出现选项 1。

From what I've been able to find, the issue seems to be with how the MySQL account passwords are stored and if the 'old_passwords' setting is set to true. This causes a compatibility issue between MySQL and newer versions of PHP (5.3+) where PHP attempts to connect using a 41-character hash but the MySQL server is still storing account passwords using a 16-character hash.

据我所知，问题似乎在于 MySQL 帐户密码的存储方式以及“old_passwords”设置是否设置为 true。这会导致 MySQL 和较新版本的 PHP (5.3+) 之间的兼容性问题，其中 PHP 尝试使用 41 个字符的哈希进行连接，但 MySQL 服务器仍然使用 16 个字符的哈希存储帐户密码。

This incompatibility was brought about by the changing of the hashing method used in MySQL 4.1 which allows for both short and long hash lengths (Scenario 2 on this page from the MySQL site: http://dev.mysql.com/doc/refman/5.5/en/password-hashing.html) and the inclusion of the MySQL Native Driver in PHP 5.3 (backwards compatibility issue documented on bullet 7 of this page from the PHP documentation: http://www.php.net/manual/en/migration53.incompatible.php).

这种不兼容性是由 MySQL 4.1 中使用的散列方法的更改引起的，该方法允许短散列长度和长散列长度（此页面上的场景 2 来自 MySQL 站点：http: //dev.mysql.com/doc/refman/ 5.5/en/password-hashing.html) 以及 PHP 5.3 中包含的 MySQL Native Driver（向后兼容性问题记录在 PHP 文档的本页第 7 项中：http: //www.php.net/manual/en /migration53.incompatible.php）。

回答by MortalViews

IF,

如果，

1. You are using a shared hosting, and don't have root access.
2. you are getting the said error while connecting to a remote database ie: not localhost.
3. and your using Xampp.
4. and the code is running fine on live server, but the issue is only on your development machine running xampp.

1. 您使用的是共享主机，并且没有 root 访问权限。
2. 连接到远程数据库时出现上述错误，即：不是本地主机。
3. 以及您使用 Xampp。
4. 并且代码在实时服务器上运行良好，但问题仅出现在运行 xampp 的开发机器上。

Then,

然后，

It is highly recommended that you installxampp 1.7.0 . Download Link

这是强烈建议您安装XAMPP 1.7.0。下载链接

Note: This is not a solution to the above problem, but a FIX which would allow you to continue with your development.

注意：这不是上述问题的解决方案，而是允许您继续开发的 FIX。