Description:
With the C extension (mysql.connector.connect() returns CMySQLConnection
when it is installed), a prepared cursor (cursor(prepared=True)) stores other
values than the text-protocol cursor of the same connection:
1. Every Python float parameter is narrowed to a C float and sent as
MYSQL_TYPE_FLOAT, whatever the column type (DOUBLE, DECIMAL, VARCHAR ...):
3.141592653589793 is stored as 3.1415927410125732, 0.1 as
0.10000000149011612, 1e15 as 999999986991104, 2.2250738585072014e-308 as 0;
values above 3.4e38 fail with error 1264.
2. A datetime.timedelta parameter is read with the PyDateTime_TIME_GET_*
macros, which are for datetime.time objects; on a timedelta they read bytes
of its days/seconds fields. days, the sign and the microseconds are lost:
timedelta(seconds=5) is stored as 00:00:00.327680,
timedelta(days=1, seconds=7) as 00:00:00.458752; most other values fail
with error 1690 ("TIME value is out of range in 'set_parameter_value'"),
e.g. timedelta(hours=1).
No error or warning is raised for the stored values. The pure-Python
implementation (use_pure=True) of the same version sends floats as
MYSQL_TYPE_DOUBLE (struct.pack("<d")) and stores every float and positive
timedelta correctly.
How to repeat:
# Python 3.12, pip install mysql-connector-python==26.10.0, server 26.10.0
import datetime
import mysql.connector
cnx = mysql.connector.connect(host='127.0.0.1', port=3306, user='root', autocommit=True) # C extension
print(mysql.connector.__version__, type(cnx).__name__)
txt = cnx.cursor()
txt.execute('DROP DATABASE IF EXISTS cpy202'); txt.execute('CREATE DATABASE cpy202'); txt.execute('USE cpy202')
txt.execute('CREATE TABLE t (id INT PRIMARY KEY, via VARCHAR(10), d DOUBLE, n DECIMAL(40,20), tm TIME(6))')
prep = cnx.cursor(prepared=True)
rows = [(3.141592653589793, datetime.timedelta(seconds=5)),
(0.1, datetime.timedelta(days=1, seconds=7)),
(1e15, datetime.timedelta(seconds=3840, microseconds=250000))]
for i, (f, td) in enumerate(rows):
txt.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (10 * i, 'text', f, f, td))
prep.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (10 * i + 1, 'prepared', f, f, td))
try:
prep.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (99, 'prepared', 1.0, 1.0, datetime.timedelta(hours=1)))
except mysql.connector.Error as e:
print('timedelta(hours=1):', e)
txt.execute('SELECT id, via, CAST(d AS CHAR), CAST(n AS CHAR), CAST(tm AS CHAR) FROM t ORDER BY id')
for r in txt.fetchall():
print(r)
Output:
26.10.0 CMySQLConnection
timedelta(hours=1): 1690 (22003): TIME value is out of range in 'set_parameter_value'
(0, 'text', '3.141592653589793', '3.14159265358979300000', '00:00:05.000000')
(1, 'prepared', '3.1415927410125732', '3.14159274101257320000', '00:00:00.327680')
(10, 'text', '0.1', '0.10000000000000000000', '24:00:07.000000')
(11, 'prepared', '0.10000000149011612', '0.10000000149011612000', '00:00:00.458752')
(20, 'text', '1e15', '1000000000000000.00000000000000000000', '01:04:00.250000')
(21, 'prepared', '999999986991104', '999999986991104.00000000000000000000', '00:00:00.003840')
Expected: each "prepared" row equals the "text" row above it (and
timedelta(hours=1) is stored as 01:00:00), as with use_pure=True.
Suggested fix:
MySQLPrepStmt_execute() in src/mysql_capi.c:
https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
pbind->buffer.f = (float)PyFloat_AsDouble(value);
mbind->buffer_type = MYSQL_TYPE_FLOAT;
should bind a double (pbind->buffer.d / MYSQL_TYPE_DOUBLE), like the
pure-Python implementation.
https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
The PyDelta_Check() branch uses PyDateTime_TIME_GET_HOUR/MINUTE/SECOND/
MICROSECOND on the timedelta; it should use PyDateTime_DELTA_GET_DAYS/
SECONDS/MICROSECONDS, put days * 24 + seconds / 3600 into hour (TIME allows
up to 838 hours) and set MYSQL_TIME.neg for negative values (Python
normalises -1 h to days=-1, seconds=82800, so the magnitude has to be
computed from the whole value).
The query-attribute binding in MySQL_query() has the same two code paths
(lines 2161-2164 and 2228-2244).
Unrelated to the above but in the same function: the DECIMAL branch writes
mbind[i].buffer_type although mbind already points at &mbinds[i]
(line 3438), i.e. it writes into mbinds[2*i], past the end of the array when
a Decimal parameter is in the second half of the parameter list.
Description: With the C extension (mysql.connector.connect() returns CMySQLConnection when it is installed), a prepared cursor (cursor(prepared=True)) stores other values than the text-protocol cursor of the same connection: 1. Every Python float parameter is narrowed to a C float and sent as MYSQL_TYPE_FLOAT, whatever the column type (DOUBLE, DECIMAL, VARCHAR ...): 3.141592653589793 is stored as 3.1415927410125732, 0.1 as 0.10000000149011612, 1e15 as 999999986991104, 2.2250738585072014e-308 as 0; values above 3.4e38 fail with error 1264. 2. A datetime.timedelta parameter is read with the PyDateTime_TIME_GET_* macros, which are for datetime.time objects; on a timedelta they read bytes of its days/seconds fields. days, the sign and the microseconds are lost: timedelta(seconds=5) is stored as 00:00:00.327680, timedelta(days=1, seconds=7) as 00:00:00.458752; most other values fail with error 1690 ("TIME value is out of range in 'set_parameter_value'"), e.g. timedelta(hours=1). No error or warning is raised for the stored values. The pure-Python implementation (use_pure=True) of the same version sends floats as MYSQL_TYPE_DOUBLE (struct.pack("<d")) and stores every float and positive timedelta correctly. How to repeat: # Python 3.12, pip install mysql-connector-python==26.10.0, server 26.10.0 import datetime import mysql.connector cnx = mysql.connector.connect(host='127.0.0.1', port=3306, user='root', autocommit=True) # C extension print(mysql.connector.__version__, type(cnx).__name__) txt = cnx.cursor() txt.execute('DROP DATABASE IF EXISTS cpy202'); txt.execute('CREATE DATABASE cpy202'); txt.execute('USE cpy202') txt.execute('CREATE TABLE t (id INT PRIMARY KEY, via VARCHAR(10), d DOUBLE, n DECIMAL(40,20), tm TIME(6))') prep = cnx.cursor(prepared=True) rows = [(3.141592653589793, datetime.timedelta(seconds=5)), (0.1, datetime.timedelta(days=1, seconds=7)), (1e15, datetime.timedelta(seconds=3840, microseconds=250000))] for i, (f, td) in enumerate(rows): txt.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (10 * i, 'text', f, f, td)) prep.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (10 * i + 1, 'prepared', f, f, td)) try: prep.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (99, 'prepared', 1.0, 1.0, datetime.timedelta(hours=1))) except mysql.connector.Error as e: print('timedelta(hours=1):', e) txt.execute('SELECT id, via, CAST(d AS CHAR), CAST(n AS CHAR), CAST(tm AS CHAR) FROM t ORDER BY id') for r in txt.fetchall(): print(r) Output: 26.10.0 CMySQLConnection timedelta(hours=1): 1690 (22003): TIME value is out of range in 'set_parameter_value' (0, 'text', '3.141592653589793', '3.14159265358979300000', '00:00:05.000000') (1, 'prepared', '3.1415927410125732', '3.14159274101257320000', '00:00:00.327680') (10, 'text', '0.1', '0.10000000000000000000', '24:00:07.000000') (11, 'prepared', '0.10000000149011612', '0.10000000149011612000', '00:00:00.458752') (20, 'text', '1e15', '1000000000000000.00000000000000000000', '01:04:00.250000') (21, 'prepared', '999999986991104', '999999986991104.00000000000000000000', '00:00:00.003840') Expected: each "prepared" row equals the "text" row above it (and timedelta(hours=1) is stored as 01:00:00), as with use_pure=True. Suggested fix: MySQLPrepStmt_execute() in src/mysql_capi.c: https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357... pbind->buffer.f = (float)PyFloat_AsDouble(value); mbind->buffer_type = MYSQL_TYPE_FLOAT; should bind a double (pbind->buffer.d / MYSQL_TYPE_DOUBLE), like the pure-Python implementation. https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357... The PyDelta_Check() branch uses PyDateTime_TIME_GET_HOUR/MINUTE/SECOND/ MICROSECOND on the timedelta; it should use PyDateTime_DELTA_GET_DAYS/ SECONDS/MICROSECONDS, put days * 24 + seconds / 3600 into hour (TIME allows up to 838 hours) and set MYSQL_TIME.neg for negative values (Python normalises -1 h to days=-1, seconds=82800, so the magnitude has to be computed from the whole value). The query-attribute binding in MySQL_query() has the same two code paths (lines 2161-2164 and 2228-2244). Unrelated to the above but in the same function: the DECIMAL branch writes mbind[i].buffer_type although mbind already points at &mbinds[i] (line 3438), i.e. it writes into mbinds[2*i], past the end of the array when a Decimal parameter is in the second half of the parameter list.