MySQL PHP 不兼容

MySQL PHP incompatibility

提问by Evernoob

I'm running WAMP locally, but connecting to a remote MySQL database. The local version of PHP is the latest 5.3.0.

我在本地运行 WAMP，但连接到远程 MySQL 数据库。PHP的本地版本是最新的5.3.0。

One of the remote databases, being version 5.0.45 works fine. However, the other remote database I'm trying to connect to, which is version 5.0.22 throws the following error before dying:

远程数据库之一，版本 5.0.45 工作正常。但是，我尝试连接的另一个远程数据库（版本 5.0.22）在死之前抛出以下错误：

Warning: mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter than expected. PID=5880 in ...

Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication in ...

警告：mysql_connect() [function.mysql-connect]：OK 数据包比预期短 6 个字节。PID=5880 在...

警告：mysql_connect() [function.mysql-connect]：mysqlnd 无法连接到 MySQL 4.1+，使用旧的身份验证...

WTF?

跆拳道？

UPDATE:

更新：

Reverting to PHP 5.2.* i.e. anything lower than 5.3.0 resolves the problem completely. As long as I am not running 5.3.0 I can connect to both databases. I'm not sure what the explanation is for this weirdness.

恢复到 PHP 5.2.* 即低于 5.3.0 的任何东西都可以完全解决问题。只要我运行的不是 5.3.0，我就可以连接到两个数据库。我不确定这种奇怪的解释是什么。

回答by VolkerK

The MySQL account you're using probably has an old 16 character long password (hash).
You can test that with a MySQL client (like HeidiSQL, the MySQL console client or any other client) and an account that has access to the mysql.usertable. If the Password field contains 16 chars it's an old password and mysqlndcannot use it to connect to the MySQL server.
You can set a new password for that user with

您使用的 MySQL 帐户可能有一个旧的 16 个字符长的密码（哈希）。
您可以使用 MySQL 客户端（如 HeidiSQL、MySQL 控制台客户端或任何其他客户端）和有权访问mysql. user桌子。如果密码字段包含 16 个字符，则它是旧密码，mysqlnd无法使用它连接到 MySQL 服务器。
您可以为该用户设置新密码

SET PASSWORD FOR 'username'@'hostmask' = PASSWORD('thepassword')

see dev_mysql_set_password

见dev_mysql_set_password

edit:
You should also check if the server is set to use/create old passwords by default.

编辑：
您还应该检查服务器是否设置为默认使用/创建旧密码。

edit2:
Please run the query

编辑2：
请运行查询

SELECT
  Length(`Password`),
  Substring(`Password`, 1, 1)
FROM
  `mysql`.`user`
WHERE
  `user`='username'

on the 5.0.22 server (the one that's "failing"). Replace usernameby the account you're using in mysql_connect().
What does that return?

在 5.0.22 服务器上（“失败”的那个）。替换username为您在 mysql_connect() 中使用的帐户。
那返回什么？

回答by Matt

I have been trying to find a simple fix for this problem. Try this approach. In MySQL type

我一直试图找到解决这个问题的简单方法。试试这个方法。在 MySQL 类型中

SELECT Host, User, Password FROM mysql.user;

If your password is sixteen characters, this is because you have used OLD_PASSWORD on your user's or have been running an old version of MySQL. To update type in

如果您的密码是 16 个字符，那是因为您对用户使用了 OLD_PASSWORD 或运行了旧版本的 MySQL。更新输入

UPDATE mysql.user SET Password=PASSWORD('newpass')
  WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

swapping root, localhost and newpass for your user, host and pass respectively. Now when you re-type

分别为您的用户、主机和通行证交换 root、localhost 和 newpass。现在当你重新输入

SELECT Host, User, Password FROM mysql.user;

Your password should have changed. This fixed it for me.

您的密码应该已更改。这为我修好了。

回答by Guss

Your database server is set to use old passwords by default. The error message you get is mysqlnd seeing a database that can support the new (safer) authentication but refuses to do so. In such a case, mysqlnd aborts the connection and refuses to work.

默认情况下，您的数据库服务器设置为使用旧密码。您收到的错误消息是 mysqlnd 看到一个可以支持新（更安全）身份验证但拒绝这样做的数据库。在这种情况下，mysqlnd 中止连接并拒绝工作。

Make sure your my.cnf does not have

确保您的 my.cnf 没有

old-passwords = 1 

After you comment out that setting from my.cnf (or remove it from where else it might be set), and restart your server, make sure to re-set your password using the command VolkerK describes, otherwise you won't be able to log in.

从 my.cnf 中注释掉该设置（或从其他可能设置的位置删除它）并重新启动服务器后，请确保使用 VolkerK 描述的命令重新设置密码，否则您将无法登录。

回答by abbas

A more simple solution is to delete the database user and create a new one with the same username and password.

一种更简单的解决方案是删除数据库用户并使用相同的用户名和密码创建一个新用户。

回答by The Mad Gamer

As the user Evernoob above said:

正如上面的用户 Evernoob 所说：

"Reverting to PHP 5.2.* i.e. anything lower than 5.3.0 resolves the problem completely. As long as I am not running 5.3.0 I can connect to both databases. I'm not sure what the explanation is for this weirdness"

“恢复到 PHP 5.2.* 即低于 5.3.0 的任何东西都可以完全解决问题。只要我没有运行 5.3.0，我就可以连接到两个数据库。我不确定这种奇怪的解释是什么”

In the case of connecting to a shared hose (in our case DreamHost), who is using the oldpassword option, we could not modify the users table. These suggested options will work in other scenarios, just not with shared web hosting.

在连接到使用 oldpassword 选项的共享软管（在我们的示例中为 DreamHost）的情况下，我们无法修改用户表。这些建议的选项适用于其他场景，只是不适用于共享网络托管。

Of note, we are running WAMP.

值得注意的是，我们正在运行 WAMP。

回答by Herb Meehan

Reverting to PHP 5.2.* did the trick! THANK YOU!

恢复到 PHP 5.2.* 成功了！谢谢你！

If you're using WAMP... left click wamp > php > version> get more>

如果您使用的是 WAMP... 左键单击 wamp > php > 版本> 获取更多>

select the version you want and download.

选择您想要的版本并下载。

install/run the exe

安装/运行exe

left click wamp > php > version> PHP 5.2.*

左键单击 wamp > php > 版本 > PHP 5.2.*

That fixed this issue. I couldn't run any of these SQL commands without getting a "command denied to user 'XXX'@'localhost'" error. Good look trying to log on as SU 'Root'. Maybe on a personal server, but not going to happen on a real host.

那解决了这个问题。我无法运行任何这些 SQL 命令，而不会收到“命令被拒绝给用户 'XXX'@'localhost'”错误。尝试以 SU 'Root' 身份登录时看起来不错。也许在个人服务器上，但不会在真实主机上发生。

回答by Little Brain

I just had this problem with a Wordpress site that suddenly stopped working and displayed this error.

我刚刚遇到了一个 Wordpress 网站的问题，该网站突然停止工作并显示此错误。

I found a GUI in the website's Control Panel that allowed me to change the password for the database user. I tried changing it to the current password and this was not allowed because the current password was not strong enough.

我在网站的控制面板中找到了一个 GUI，它允许我更改数据库用户的密码。我尝试将其更改为当前密码，但这是不允许的，因为当前密码不够强。

I changed the database password to a new, stronger password and edited my wp-config.php file to have the new password.

我将数据库密码更改为一个新的、更强的密码，并编辑了我的 wp-config.php 文件以获得新密码。

This worked!

这有效！

So my guess is, the hosting provider upgraded something, maybe MySQL, to a version that requires stronger passwords. The fix for me was to use the cpanel GUI to change the database user's password, and update wp-config.php to match.

所以我的猜测是，托管服务提供商将某些东西（可能是 MySQL）升级到需要更强密码的版本。我的解决方法是使用 cpanel GUI 更改数据库用户的密码，并更新 wp-config.php 以匹配。

回答by AbdolHosein

After lots of search and tries I found a way without needage to any downgrade/upgrade of MySQL and any affect on other users of running instance of MySQL.

经过大量搜索和尝试，我找到了一种无需对 MySQL 进行任何降级/升级以及对运行 MySQL 实例的其他用户产生任何影响的方法。

The solution is to resetting password by:

解决方案是通过以下方式重置密码：

for MySQL versions newer than 5.7.5 ( > 5.7.5):

对于高于 5.7.5 (> 5.7.5) 的 MySQL 版本：

ALTER USER 'mysqlUsername'@'hostname' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

ALTER USER 'mysqlUsername'@'hostname' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

for older versions of MySQL: ( <= 5.7.5)

对于旧版本的 MySQL：( <= 5.7.5)

SET PASSWORD FOR 'mysqlUsername'@'hostname' = PASSWORD('mysqlUsernamePassword');

为 'mysqlUsername'@'hostname' 设置密码 = PASSWORD('mysqlUsernamePassword');

I found the point here!

我在这里找到了重点！

It made mine working well!

它使我的工作正常！

回答by user97410

My webhost has different versions of PHP/MySQL configured and to use a certain one I need to use the correct .php extension - notably .php5 It might be something as simple as that.

我的网络主机配置了不同版本的 PHP/MySQL，要使用某个版本，我需要使用正确的 .php 扩展名 - 特别是 .php5 它可能就这么简单。

回答by kenorb

If you're using MySQL 8 or above, the default authentication pluginis caching_sha2_password, so to downgrade to mysql_native_password(in case of PHP incompatibility), set it in my.cfg, e.g.

如果您使用的是 MySQL 8 或更高版本，则默认身份验证插件是caching_sha2_password，因此要降级到mysql_native_password（在 PHP 不兼容的情况下），请将其设置为my.cfg，例如

[mysqld]
default-authentication-plugin=mysql_native_password

then restart MySQL service.

然后重启MySQL服务。

See: Upgrading to MySQL 8.0 : Default Authentication Plugin Considerations& Native Pluggable Authentication.

请参阅：升级到 MySQL 8.0：默认身份验证插件注意事项和本机可插入身份验证。

If still won't work, change the root password as suggested in the accepted answer, e.g.

如果仍然不起作用，请按照已接受的答案中的建议更改 root 密码，例如

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'root';
FLUSH PRIVILEGES;

Related:

有关的：

• MySQL remote connection fails with “unknown authentication method”
• mysql_connect(): The server requested authentication method unknown to the client
• Multiple MySQL Versions for Development
• Mysql. The server requested authentication method unknown to the client

• MySQL 远程连接因“未知身份验证方法”而失败
• mysql_connect()：服务器请求客户端未知的身份验证方法
• 用于开发的多个 MySQL 版本
• mysql。服务器请求客户端未知的身份验证方法