Description:
In the pure-Python implementation (use_pure=True, and the only implementation
where the C extension is not installed), the binary-protocol TIME conversion
applies the sign to the day count only, in both directions:
- write: a negative datetime.timedelta parameter of a prepared statement that
is not a whole number of days is stored with the wrong magnitude:
timedelta(hours=-1) is stored as -47:00:00, timedelta(minutes=-30) as
-47:30:00, timedelta(days=-2, hours=3) (= -45:00:00) as -51:00:00;
- read: a negative TIME value read through a prepared cursor loses its sign or
gets the wrong magnitude: -01:00:00 is returned as timedelta(seconds=3600)
(+1 h), -00:00:00.5 as +0.5 s, -183:00:34 as -7 days + 15:00:34
(= -152:59:26).
So a table copied with prepared statements has 01:00:00 where the source has
-01:00:00. No error or warning is raised. The text-protocol cursor of the same
connection is correct in both directions, and the C extension reads negative
TIME values correctly (its write path has a separate defect with timedelta
parameters).
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, use_pure=True)
print(mysql.connector.__version__, type(cnx).__name__)
txt = cnx.cursor()
txt.execute('DROP DATABASE IF EXISTS cpy204'); txt.execute('CREATE DATABASE cpy204'); txt.execute('USE cpy204')
txt.execute('CREATE TABLE w (id INT PRIMARY KEY, via VARCHAR(10), v TIME(6))')
prep = cnx.cursor(prepared=True)
for i, td in enumerate([datetime.timedelta(hours=-1), datetime.timedelta(minutes=-30), datetime.timedelta(days=-2, hours=3)]):
txt.execute('INSERT INTO w VALUES (%s, %s, %s)', (10 * i, 'text', td))
prep.execute('INSERT INTO w VALUES (%s, %s, %s)', (10 * i + 1, 'prepared', td))
txt.execute('SELECT id, via, CAST(v AS CHAR) FROM w ORDER BY id')
print('write:'); [print(' ', r) for r in txt.fetchall()]
# read side
txt.execute("CREATE TABLE r (id INT PRIMARY KEY, v TIME(6))")
txt.execute("INSERT INTO r VALUES (1, '-01:00:00'), (2, '-00:00:00.5'), (3, '-183:00:34'), (4, '12:34:56')")
txt.execute('SELECT id, v FROM r ORDER BY id'); a = txt.fetchall()
prep.execute('SELECT id, v FROM r WHERE id > %s ORDER BY id', (0,)); b = prep.fetchall()
print('read (text cursor / prepared cursor):')
for (i, x), (_, y) in zip(a, b):
print(f' {i}: {x!r} / {y!r}')
Output:
26.10.0 MySQLConnection
write:
(0, 'text', '-01:00:00.000000')
(1, 'prepared', '-47:00:00.000000')
(10, 'text', '-00:30:00.000000')
(11, 'prepared', '-47:30:00.000000')
(20, 'text', '-45:00:00.000000')
(21, 'prepared', '-51:00:00.000000')
read (text cursor / prepared cursor):
1: datetime.timedelta(days=-1, seconds=82800) / datetime.timedelta(seconds=3600)
2: datetime.timedelta(days=-1, seconds=86399, microseconds=500000) / datetime.timedelta(microseconds=500000)
3: datetime.timedelta(days=-8, seconds=32366) / datetime.timedelta(days=-7, seconds=54034)
4: datetime.timedelta(seconds=45296) / datetime.timedelta(seconds=45296)
Expected: each "prepared" row equals the "text" row above it, and the
prepared cursor reads the same timedelta values as the text cursor.
Suggested fix:
lib/mysql/connector/protocol.py:
- MySQLProtocol.prepare_binary_time() sets is_negative when value.days < 0
but encodes abs(value.days) days plus the hours/minutes/seconds of
value.seconds. Python normalises a negative timedelta to days < 0 and a
positive seconds remainder (-1 h is days=-1, seconds=82800), so the value
sent is -(1 day 23:00:00) = -47 h. The magnitude should be computed from
the whole value, e.g. abs(value) (or -value when negative), and then split
into days/hours/minutes/seconds/microseconds:
https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
- MySQLProtocol._parse_binary_time() negates only days
(if data[0] == 1: days *= -1) and adds hours, minutes, seconds and
microseconds with a positive sign, so -(0 days 01:00:00) becomes +01:00:00
and -(7 days 15:00:34) becomes -7 days + 15:00:34. It should build the
positive timedelta from all fields and negate the result:
https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
The text paths (MySQLConverter._timedelta_to_mysql in conversion.py and the
text TIME parser) handle the sign correctly. Related, different path:
Bug #72493 (negative TIME read through the text protocol, fixed in 2014).
Description: In the pure-Python implementation (use_pure=True, and the only implementation where the C extension is not installed), the binary-protocol TIME conversion applies the sign to the day count only, in both directions: - write: a negative datetime.timedelta parameter of a prepared statement that is not a whole number of days is stored with the wrong magnitude: timedelta(hours=-1) is stored as -47:00:00, timedelta(minutes=-30) as -47:30:00, timedelta(days=-2, hours=3) (= -45:00:00) as -51:00:00; - read: a negative TIME value read through a prepared cursor loses its sign or gets the wrong magnitude: -01:00:00 is returned as timedelta(seconds=3600) (+1 h), -00:00:00.5 as +0.5 s, -183:00:34 as -7 days + 15:00:34 (= -152:59:26). So a table copied with prepared statements has 01:00:00 where the source has -01:00:00. No error or warning is raised. The text-protocol cursor of the same connection is correct in both directions, and the C extension reads negative TIME values correctly (its write path has a separate defect with timedelta parameters). 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, use_pure=True) print(mysql.connector.__version__, type(cnx).__name__) txt = cnx.cursor() txt.execute('DROP DATABASE IF EXISTS cpy204'); txt.execute('CREATE DATABASE cpy204'); txt.execute('USE cpy204') txt.execute('CREATE TABLE w (id INT PRIMARY KEY, via VARCHAR(10), v TIME(6))') prep = cnx.cursor(prepared=True) for i, td in enumerate([datetime.timedelta(hours=-1), datetime.timedelta(minutes=-30), datetime.timedelta(days=-2, hours=3)]): txt.execute('INSERT INTO w VALUES (%s, %s, %s)', (10 * i, 'text', td)) prep.execute('INSERT INTO w VALUES (%s, %s, %s)', (10 * i + 1, 'prepared', td)) txt.execute('SELECT id, via, CAST(v AS CHAR) FROM w ORDER BY id') print('write:'); [print(' ', r) for r in txt.fetchall()] # read side txt.execute("CREATE TABLE r (id INT PRIMARY KEY, v TIME(6))") txt.execute("INSERT INTO r VALUES (1, '-01:00:00'), (2, '-00:00:00.5'), (3, '-183:00:34'), (4, '12:34:56')") txt.execute('SELECT id, v FROM r ORDER BY id'); a = txt.fetchall() prep.execute('SELECT id, v FROM r WHERE id > %s ORDER BY id', (0,)); b = prep.fetchall() print('read (text cursor / prepared cursor):') for (i, x), (_, y) in zip(a, b): print(f' {i}: {x!r} / {y!r}') Output: 26.10.0 MySQLConnection write: (0, 'text', '-01:00:00.000000') (1, 'prepared', '-47:00:00.000000') (10, 'text', '-00:30:00.000000') (11, 'prepared', '-47:30:00.000000') (20, 'text', '-45:00:00.000000') (21, 'prepared', '-51:00:00.000000') read (text cursor / prepared cursor): 1: datetime.timedelta(days=-1, seconds=82800) / datetime.timedelta(seconds=3600) 2: datetime.timedelta(days=-1, seconds=86399, microseconds=500000) / datetime.timedelta(microseconds=500000) 3: datetime.timedelta(days=-8, seconds=32366) / datetime.timedelta(days=-7, seconds=54034) 4: datetime.timedelta(seconds=45296) / datetime.timedelta(seconds=45296) Expected: each "prepared" row equals the "text" row above it, and the prepared cursor reads the same timedelta values as the text cursor. Suggested fix: lib/mysql/connector/protocol.py: - MySQLProtocol.prepare_binary_time() sets is_negative when value.days < 0 but encodes abs(value.days) days plus the hours/minutes/seconds of value.seconds. Python normalises a negative timedelta to days < 0 and a positive seconds remainder (-1 h is days=-1, seconds=82800), so the value sent is -(1 day 23:00:00) = -47 h. The magnitude should be computed from the whole value, e.g. abs(value) (or -value when negative), and then split into days/hours/minutes/seconds/microseconds: https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357... - MySQLProtocol._parse_binary_time() negates only days (if data[0] == 1: days *= -1) and adds hours, minutes, seconds and microseconds with a positive sign, so -(0 days 01:00:00) becomes +01:00:00 and -(7 days 15:00:34) becomes -7 days + 15:00:34. It should build the positive timedelta from all fields and negate the result: https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357... The text paths (MySQLConverter._timedelta_to_mysql in conversion.py and the text TIME parser) handle the sign correctly. Related, different path: Bug #72493 (negative TIME read through the text protocol, fixed in 2014).