Bug #96562 Exception when calling "connection.reset_session()"
Submitted: 16 Aug 2019 3:22 Modified: 26 Aug 2019 12:01
Reporter: Duong Do Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:8.0.11, 8.0.17 OS:Linux (Centos/Ubuntu)
Assigned to: CPU Architecture:x86

[16 Aug 2019 3:22] Duong Do
Description:
Cannot reset session when using C-extension in mysql-connector-python.
When callling connection.reset_session(), there will be an error:

"_mysql_connector.MySQLInterfaceError: Access denied trying to change to user 'USERNAME@HOSTNAME' (using password: YES). Disconnecting."

How to repeat:
Python version: 3.6~3.7
mysql-connector-python version: 8.0.11~8.0.17 (any version that enables C-extension by default)

#python code
import mysql.connector
db_conn = { 'host': 'host_name', 'user': 'user_name', password: 'password', port: port_number}
conn = mysql.connector.connect(**db_conn)

cursor = conn.cursor(dictionary=True)
cursor.execute('SELECT 1')
cursor.fetchall()
cursor.close()
conn.reset_session()

# Exception shows up at last line: conn.reset_session(), when conn's class
# is "CMySQLConnection".

Suggested fix:
One workaround is to set "use_pure=True" for connector
conn = mysql.connector.connect(use_pure=True, **db_conn)
In that case conn's class is "MySQLConnection" and there are no errors.
[16 Aug 2019 3:29] Duong Do
MySQL server: 5.6.15
[20 Aug 2019 11:54] Umesh Shastry
Hello Duong Do,

Thank you for the report and testcase.
I've tried to reproduce but I'm not seeing any exceptions raised after the call to connection.reset_session() as mentioned in the report. I'm adding details for your reference, is there anything else I'm missing? Thank you.

-

## Tried with MySQL Server 5.6.45(10), 5.7.27 and 8.0.17
## By default, use_pure (use the pure Python implementation) is False as of MySQL 8 and hence didn't use use_pure=False
 
import logging
import mysql.connector
import sys
import platform

print("OS: {} {}".format(platform.system(), platform.release()))
print(sys.version)
driver  = mysql.connector
print("Driver: {} {}".format(driver.__name__, driver.__version__))

db_conn = { 'host': 'xx.xx.xx.com', 'user': 'ushastry', 'port': 3333}
conn = mysql.connector.connect(**db_conn)

cursor = conn.cursor()
cursor.execute("SELECT VERSION()")
print("MySQL Server:{}".format(cursor.fetchone()));

cursor = conn.cursor(dictionary=True)
cursor.execute('SELECT 1')
print(cursor.fetchall())
cursor.close()
conn.reset_session()

OS: Windows 10
3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)]
Driver: mysql.connector 8.0.17
MySQL Server:('5.6.10',)
[{'1': 1}]

regards.
Umesh
[22 Aug 2019 2:37] Duong Do
# Hi Umesh,

# Thanks for your support. My machine is Linux (Centos).
# I used anaconda for Python environment.
# When I tried to add prints as you did, I got the following printout:
# (I add another print to make sure class of connector is CMySQLConnection)
Z
OS: Linux 4.14.13-1.el7.elrepo.x86_64
3.6.5 |Anaconda, Inc.| (default, Apr 29 2018, 16:14:56)
[GCC 7.2.0]
Driver: mysql.connector 8.0.17
Connector class: <class 'mysql.connector.connection_cext.CMySQLConnection'>
MySQL Server:('5.6.15-log',)
[{'1': 1}]
Traceback (most recent call last):
  File "/project_path/anaconda3/envs/env3/lib/python3.6/site-packages/mysql/connector/abstracts.py", line 915, in reset_session
    self.cmd_reset_connection()
  File "/project_path/anaconda3/envs/env3/lib/python3.6/site-packages/mysql/connector/abstracts.py", line 1034, in cmd_reset_connection
    raise NotImplementedError
NotImplementedError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/project_path/anaconda3/envs/env3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 630, in cmd_change_user
    self._cmysql.change_user(username, password, database)
_mysql_connector.MySQLInterfaceError: Access denied trying to change to user 'username'@'hostname' (using password: YES). Disconnecting.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "test.py", line 46, in <module>
    conn.reset_session()
  File "/project_path/anaconda3/envs/env3/lib/python3.6/site-packages/mysql/connector/abstracts.py", line 923, in reset_session
    self._database, self._charset_id)
  File "/project_path/anaconda3/envs/env3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 633, in cmd_change_user
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1873 (28000): Access denied trying to change to user 'username'@'hostname' (using password: YES). Disconnecting.
[22 Aug 2019 2:44] Duong Do
Another attempt with Ubuntu 16.0.4, MySQL Server 5.7.27, Python 3.5 (not using Anaconda) results in similar error:

Driver: mysql.connector 8.0.17
Connection class:  <class 'mysql.connector.connection_cext.CMySQLConnection'>
MySQL Server:('5.7.27-0ubuntu0.16.04.1',)
[{'1': 1}]
Traceback (most recent call last):
  File "/home/administrator/.local/share/virtualenvs/blog-rdDX1Mrc/lib/python3.5/site-packages/mysql/connector/abstracts.py", line 915, in reset_session
    self.cmd_reset_connection()
  File "/home/administrator/.local/share/virtualenvs/blog-rdDX1Mrc/lib/python3.5/site-packages/mysql/connector/abstracts.py", line 1034, in cmd_reset_connection
    raise NotImplementedError
NotImplementedError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/administrator/.local/share/virtualenvs/blog-rdDX1Mrc/lib/python3.5/site-packages/mysql/connector/connection_cext.py", line 630, in cmd_change_user
    self._cmysql.change_user(username, password, database)
_mysql_connector.MySQLInterfaceError: Access denied for user 'root'@'localhost' (using password: NO)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "experiment/test.py", line 23, in <module>
    conn.reset_session()
  File "/home/administrator/.local/share/virtualenvs/blog-rdDX1Mrc/lib/python3.5/site-packages/mysql/connector/abstracts.py", line 923, in reset_session
    self._database, self._charset_id)
  File "/home/administrator/.local/share/virtualenvs/blog-rdDX1Mrc/lib/python3.5/site-packages/mysql/connector/connection_cext.py", line 633, in cmd_change_user
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[26 Aug 2019 7:42] Umesh Shastry
Thank you, Duong Do.

regards,
Umesh
[26 Aug 2019 12:01] Duong Do
Hi Umesh,

Thanks for your support. Could you reproduce the error on Linux then?

Regards,
Duong.