Description:
Issue:
Calling rest connection will reset the client CLIENT_IGNORE_SPACE.
Looking at the docs for reset connection I'm unsure whether this is expected behavior or a bug.
The docs[1] say calling reset connection will reset session variables to global values when called. In this case, IGNORE_SPACES is a SQL_MODE/session var but here we are setting via a client flag in the connection, so shouldn't reset_connection still honor this flag?
[1] https://dev.mysql.com/doc/c-api/8.0/en/mysql-reset-connection.html
How to repeat:
1. Connect to instance while setting `ClientFlag.IGNORE_SPACE`
2. Run `cnx.isset_client_flag(ClientFlag.IGNORE_SPACE)` to confirm flag is set
3. Run `select curdate ()` to confirm spaces ignored
4. Now reset the connection using cmd_reset_connection()
5. Run `cnx.isset_client_flag(ClientFlag.IGNORE_SPACE)` to confirm flag is still set
6. Run `select curdate ()`. Error will be returned as CLIENT_IGNORE_SPACE was reset.
Sample script:
==========================
import mysql.connector
from mysql.connector.constants import ClientFlag
config = {
'user': 'username',
'password': 'password',
'host': 'host',
'port': 8026,
'database': 'mysql',
'raise_on_warnings': True,
'ssl_disabled': True,
'client_flags': [ClientFlag.IGNORE_SPACE]
}
query_space="select curdate ()"
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
cursor = cnx.cursor()
try:
cursor.execute(query_space)
for row in cursor.fetchall():
print(f' DATE RETURNED SUCCESSFULLY : {row[0]}')
except mysql.connector.Error as err:
print(f'error returned pre reset : {err}')
# Check value of client flag and value of sql_mode.
print(f' Is flag set pre reset : {cnx.isset_client_flag(ClientFlag.IGNORE_SPACE)}')
print(f' PRE-RESET SQL_MODE : {cnx.sql_mode}')
cnx.cmd_reset_connection()
# Check value of client flag and value of sql_mode post connection reset.
print(f' Is flag set post reset : {cnx.isset_client_flag(ClientFlag.IGNORE_SPACE)}')
print(f' POST-RESET SQL_MODE : {cnx.sql_mode}')
try:
cursor.execute(query_space)
for row in cursor.fetchall():
print(f' DATE RETURNED SUCCESSFULLY : {row[0]}')
except mysql.connector.Error as err:
print(f'Error returned post reset: {err}')
cursor.close()
cnx.close()
Sample output:
==========================
$ python resettest.py
DATE RETURNED SUCCESSFULLY : 2021-08-25
Is flag set pre reset : True
PRE-RESET SQL_MODE : IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Is flag set post reset : True
POST-RESET SQL_MODE : ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Error returned post reset: 1630 (42000): FUNCTION mysql.curdate does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
Suggested fix:
If this is expected behavior:
- Could it be documented client flags can be reset?
- could `isset_client_flag()` return the correct value post reset? I understand this may be set on the client side, but it would be useful to return False if the server is not honoring it.
If this is not expected behavior:
- Honor client capability flags post reset.
