MySQL Bugs: #114236: Cursor using prepared=True ignores connection's custom converter class

Bug #114236	Cursor using prepared=True ignores connection's custom converter class
Submitted:	6 Mar 2024 5:13	Modified:	13 Sep 2024 16:35
Reporter:	Magnus Oxlund	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	8.3.0	OS:	MacOS (12.7.3)
Assigned to:		CPU Architecture:	x86

Description:
When creating a cursor object with the argument `prepared=True`, any custom converter class configured for the connection object is ignored. Instead, Connector/Python's built-in MySQLConverter class is used as if no custom converter class were configured.

Tested using:

macOS 12.7.3
Python 3.12.2
Connector/Python (pip distribution) 8.3.0
MySQL Community Server for macOS x86_64 8.2.0

How to repeat:
# This example uses a custom MySQLConverter subclass to add a tzinfo object to
# Python datetime objects converted from DATETIME MySQL columns.

# Whether tzinfo is present on retrieved DATETIME columns indicates whether
# the custom converter is actually used.

import mysql.connector
from datetime import datetime, timezone

class TZAwareConverter(mysql.connector.conversion.MySQLConverter):
	@classmethod
	def _datetime_to_python(cls, value, dsc=None):
		datetime_val = super()._datetime_to_python(value, dsc)
		tz_aware_dateimte_val = datetime_val.replace(tzinfo=timezone.utc)
		return tz_aware_dateimte_val

connection = mysql.connector.connect(
	database = 'DEBUG',
	host = 'localhost',
	user = 'DEBUG',
	password = 'secret',
	converter_class = TZAwareConverter
)

cursor = connection.cursor(prepared=True) # Toggle between: prepared=True | prepared=False

cursor.execute('DROP TABLE IF EXISTS DEBUG;')
cursor.execute('CREATE TABLE IF NOT EXISTS DEBUG ( c1 DATETIME NOT NULL );')
connection.commit()

cursor.execute('INSERT INTO DEBUG SET c1 = %(c1)s', {'c1': datetime.now()})
connection.commit()

cursor.execute('SELECT c1 FROM DEBUG;')
result = cursor.fetchall()

print(result)
# Output for prepared=True:
# [(datetime.datetime(2024, 3, 6, 11, 56, 21),)]

# Output for prepared=False:
# [(datetime.datetime(2024, 3, 6, 11, 56, 30, tzinfo=datetime.timezone.utc),)]

print(cursor._connection._converter_class)
# Always outputs the custom converter class regardless of cursor's prepared value:
# <class '__main__.TZAwareConverter'>

Hello Magnus Oxlund,

Thank you for the report and test case.

regards,
Umesh

Hello Magnus,

Thanks for reaching us. 

We believe it does not make much sense (is not very practical) to support custom converters when working with prepared statements; writing them would not be a trivial job, and the use cases quite limited.

In this regard, prepared cursors do not support custom converters.

At the protocol level, communications with the server are based on a very particular protocol for prepared statements. To put you in perspective, the prepared statements operate over the [binary protocol](https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_binary_resultset.html) while the others, over the [text protocol](https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_query_response_text_re....

If we supported custom converters for prepared statements, the built-in conversion layer would be skipped; your converter would be getting the values as bytes, as in the text protocol case, but a different parsing process would be required.

For the text protocol, this is practical since it returns values that are easily processable/understandable by users. The binary protocol is not that intuitive, we believe it would be less practical as parsing the values would require to understand the binary protocol.

NOTE: mind that `mysql.connector.conversion.MySQLConverter` is a converter that assumes the values come from the text protocol. This converter would not work for the "prepared" case.

Regards,
Oscar.

Posted by developer:
 
It is not a bug, but an enhancement.

We believe it does not make much sense (is not very practical) to support custom converters when working with prepared statements; writing them would not be a trivial job, and the use cases quite limited.

If we supported custom converters for prepared statements, the built-in conversion layer would be skipped; custom converters would be getting the values as bytes, as in the text protocol case, but a different parsing process would be required.

For the text protocol, this is practical since it returns values that are easily processable/understandable by users. The binary protocol is not that intuitive, we believe it would be less practical as parsing the values would require to understand the binary protocol.