mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

本文介绍如何解决使用PHP连接远程MySQL服务器时遇到的旧密码哈希问题,包括禁用旧密码格式、更新数据库中所有用户的密码格式,并提供具体步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

//在转载的基础上修改了一点 , 把原先排版会乱的的表(运行结果)改成图

When I was working with XAMPP in Ubuntu and asked write PHP script to connect to remote MySQL server which is using PASSWORD hash function to save the password for user, and I found following error.


Warning: mysql_connect() [function.mysql-connect]: Premature end of data (mysqlnd_wireprotocol.c:554) in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.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 in path/to/the/file/where/connection/script/is/written/

As you will see, the core issue here is that MySQL can have passwords with hashes stored in the old 16-character format, which is not supported by PHP 5.3′s new mysqlnd library.
Since I couldn’t find a good solution with a quick Google, here is how I solved this without having to downgrade PHP or MySQL (as some of the solutions suggested):

1. Change MySQL to NOT to use old_passwords
It seems that even MySQL 5.x versions still default to the old password hashes. You need to change this in “my.cnf” (e.g. /etc/my.cnf): remove or comment out the line that says
old_passwords = 1 
//old_passwords = 1为16位
//所以这里要先
//SET old_passwords = 0;
//这样使新密码的长度也变成41位
Restart MySQL. If you don’t, MySQL will keep using the old password format, which will mean that you cannot upgrade the passwords using the builtin PASSWORD() hashing function. You can test this by running:
  
mysql> SELECT Length(PASSWORD('xyz')); 
结果如下图:很明显16位
 
The old password hashes are 16 characters, the new ones are 41 characters.
2. Change the format of all the passwords in the database to the new format
Connect to the database, and run the following query:
mysql> SELECT user, Length(`Password`) FROM `mysql`.`user`;  

This will show you which passwords are in the old format, ex:
如图:
 
Notice here that each user can have multiple rows (one for each different host specification).
To update the password for each user, run the following:
UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';  
如图:
Finally, flush privileges:
FLUSH PRIVILEGES;  
最后再查询一下
mysql> SELECT user, Length(`Password`) FROM `mysql`.`user`;  
结果如下图:(变成41位了,成功)
 
成功后退出exit
最后重新启动一下mysql
service mysqld restart
ok
 
原文地址:http://blog.youkuaiyun.com/dragon8299/article/details/6543368 
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值