无法使用旧身份验证连接到 MySQL 4.1+ [英] Cannot connect to MySQL 4.1+ using old authentication

问题描述

我正在尝试连接到位于 http://bluesql.net 的 mySQL 数据库，但是当我尝试连接时连接，它给出了这个错误:

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

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

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.

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

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.

有人有什么想法吗?

推荐答案

edit: 这仅适用于您控制 MySQL 服务器的情况...如果您不看看 Mysql 密码散列方法新旧对比

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

首先检查 SQL 查询

First check with the SQL query

SHOW VARIABLES LIKE 'old_passwords'

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

(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.

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

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

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

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;

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

(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.

另见 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

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 4.1+的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！