PyMySQL Access Denied "using password (no") but using password

Question

Headscratcher here for me.

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 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)")

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.

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.

EDIT: 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

EDIT 2: Added debug lines to connect and _request_authentication in connections.py.

Here's the latest:

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)

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.

`1)` Isn't it supposed to be `password` instead of `passwd`? `2)` In the error it also says it tries to connect with `root`, not `paul` - so I would check the connection settings and usage examples — arieljannai, May 14 '18 at 18:08
Thanks @arieljannai. That was a screen paste error on my part. I get the same message whether I try root or the user I created, paul. I've also tried both "password" and "passwd". — paulmiller3000, May 14 '18 at 20:06
I notice that the user is 'root'@'localhost'. Are you sure you are supplying the correct password for that particular root account? Remember, there are probably several root user accounts. — Honeyboy Wilson, May 14 '18 at 20:22
@paulmiller3000 You get the `Access denied for user 'root'@'localhost' (using password: NO)` even when using **`paul`**? — arieljannai, May 15 '18 at 08:16
@arieljannai I get access denied for paul when using paul and access denied for root when using root. I only created paul as a test to see if something was wrong with root. — paulmiller3000, May 15 '18 at 10:30

score 6 · Accepted Answer · answered May 15 '18 at 13:42

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:

Installed lower version of PyMySQL (0.8.1 to 0.8.0 in my case)
Completely removed both versions of PyMySQL and installed v0.8.0
Removed MySQL 8.0 and reinstalled

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.

Nikolay Shindarov · Answer 2 · 2021-04-03T01:38:16.950

1

One possible cause of this issue is a PyMySQL version 0.8.x or lower. If this is the case, the issue could be resolved by upgrading it in pip to PyMySQL==0.9.3.

edited Apr 03 '21 at 01:38

answered Apr 02 '21 at 14:51

Nikolay Shindarov

1,616
2
18
25

score 0 · Answer 3 · answered May 14 '18 at 20:26

0

That is strange. I would install pymysql in a virtual environment and then drop some debugging lines in this function:

https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L940

That should show what variables its actually using for the connection and make it obvious what is wrong.

answered May 14 '18 at 20:26

d g

1,594
13
13

Thanks. Added print statements to debug. Appears to be receiving the right variables; updated my answer to include excerpts. – paulmiller3000 May 14 '18 at 20:45

PyMySQL Access Denied "using password (no") but using password

3 Answers3

Linked