PyMySQL访问被拒绝“使用密码(否)"但使用密码 [英] PyMySQL Access Denied "using password (no") but using password
问题描述
这里是我的摇头丸.
我正在尝试从 Python 连接到本地 MySQL 8.0.11.0 安装上的数据库.
I am attempting to connect to a database on my local MySQL 8.0.11.0 install from Python.
这是我正在使用的代码:
Here's the code I'm using :
conn = pymysql.connect(host='localhost', port=3306, user='root', password='placeholder', db='CustomerInfo')
Python返回以下内容:
Python is returning the following :
Traceback (most recent call last):
File "D:\Python\FileCheck.py", line 38, in <module>
conn = pymysql.connect(host='localhost', port=3306, user='root', password='placeholder', db='CustomerInfo')
File "C:\Program Files\Python36\lib\site-packages\pymysql\__init__.py", line 90, in Connect
return Connection(*args, **kwargs)
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 704, in __init__
self.connect()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 974, in connect
self._request_authentication()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 1203, in _request_authentication
auth_packet = self._read_packet()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 1059, in _read_packet
packet.check_error()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 384, in check_error
err.raise_mysql_exception(self._data)
File "C:\Program Files\Python36\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")
我已确认通过 MySQL Workbench登录时可以访问数据库.奇怪的是，即使我正在发送密码，Python也会告诉我"using password: NO".
I have confirmed that I have access to the database when logging in through MySQL Workbench. The weird thing is that Python is telling me "using password: NO" even though I'm sending a password.
我尝试在脚本中将passwd更改为"password"并创建具有适当特权的新用户.两者都没有.
I've tried changing passwd to "password" in the script and creating a new user with the appropriate privileges. Neither has worked.
不确定下一步要检查什么.
Not sure what to check next.
:以下是授予根用户的权限:
Here are grants for root:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *....
GRANT ALL PRIVILEGES ON `customerinfo`.* TO `root`@`localhost` WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
在connections.py中的 connect 和 _request_authentication 中添加了调试行.
EDIT 2: Added debug lines to connect and _request_authentication in connections.py.
这是最新的:
C:\Users\Paul Miller>python.exe D:\Python\FileCheck.py
** DEBUG 1 **
connect, line 973
host= localhost
user= root
password= placeholder
** DEBUG 2 **
_request_authentication line 1172
user= root
password= placeholder
Traceback (most recent call last):
File "D:\Python\FileCheck.py", line 38, in <module>
conn = pymysql.connect(host='localhost', port=3306, user='root', password='placeholder', db='CustomerInfo')
File "C:\Program Files\Python36\lib\site-packages\pymysql\__init__.py", line 90, in Connect
return Connection(*args, **kwargs)
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 704, in __init__
self.connect()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 982, in connect
self._request_authentication()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 1218, in _request_authentication
auth_packet = self._read_packet()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 1067, in _read_packet
packet.check_error()
File "C:\Program Files\Python36\lib\site-packages\pymysql\connections.py", line 384, in check_error
err.raise_mysql_exception(self._data)
File "C:\Program Files\Python36\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")
启用日志记录.日志中没有任何意外，但这是最新尝试生成的内容:
EDIT 3: Enabled logging. No surprises in the log, but here's what the latest attempt generated:
2018-05-15T10:27:15.197445Z 43 Connect root@localhost on CustomerInfo using TCP/IP
2018-05-15T10:27:15.197540Z 43 Connect Access denied for user 'root'@'localhost' (using password: NO)
发现了问题.我添加了一些调试语句，如下所示:
EDIT 4: Found the problem. I added some debug statements as follows:
if self._auth_plugin_name in ('', 'mysql_native_password'):
print("** DEBUG 3 **")
print(self.password)
print("\n")
authresp = _scramble(self.password.encode('latin1'), self.salt)
问题是此IF块失败了……程序流没有到达"authresp"语句.当我的同伴运行相同的程序时，其密码将在控制台中打印.
The problem is this IF block fails... program flow isn't getting to the "authresp" statement. When a peer of mine runs this same program, his passwords prints in the console.
所以，现在我只需要弄清楚为什么我不去这个分支.
So, now I just need to figure out why I'm not going down this branch.
推荐答案
将MySQL从8.0.11.0降级到5.7.22后，解决了该问题.
Resolved the issue after downgrading MySQL from 8.0.11.0 to 5.7.22.
除了我在原始问题中列出的内容之外，我还尝试了以下操作:
In addition to what I listed in my original question, I also tried the following:
- 安装了较低版本的PyMySQL(在我的情况下为0.8.1至0.8.0)
- 完全删除了两个版本的PyMySQL并安装了v0.8.0
- 删除了MySQL 8.0，然后重新安装
以上方法均无效时，我将MySQL降级至v5.7，这是我的同龄人使用的版本.这样就解决了错误.
When none of the above worked, I downgraded MySQL to v5.7, which is what one of my peers is using. That resolved the error.
这篇关于PyMySQL访问被拒绝“使用密码(否)"但使用密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
