[CONPY-112] commands out of sync error w/ simple rollback if cursor is not cloed Created: 2020-09-12  Updated: 2020-09-13  Resolved: 2020-09-13

Status: Closed
Project: MariaDB Connector/Python
Component/s: DBAPI 2.0
Affects Version/s: 1.0.1
Fix Version/s: 1.0.2, N/A

Type: Bug Priority: Major
Reporter: Mike Bayer Assignee: Georg Richter
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

fedora 31, mariadb server 10.3.22


 Description   

the following program throws the above mentioned error:

import mariadb
 
 
 
conn = mariadb.connect(user="scott", password="tiger", db="test", host="localhost")
 
 
 
cursor = conn.cursor()
 
cursor.execute("select 1")
 
row = cursor.fetchone()
 
 
 
conn.rollback()
 
 
 
result:
 
 
 
$ python test3.py 
Traceback (most recent call last):
 
  File "test3.py", line 9, in <module>
 
    conn.rollback()
 
mariadb.InterfaceError: Commands out of sync; you can't run this command now

if the cursor is closed first, no error is raised.

using other DBAPI drivers such as mysqlclient, mysql-connector, and pymysql, no error is raised with the identical program, generally it's not standard behavior that rollback() fails if a cursor is open, and also if this were part of mariadb-connector's contract, it should raise an informative error that the cursor was not closed when it should have been.

I'm also getting segfaults in some similar cases however i dont have a reproducer for that right now.

 Comments   
Comment by Georg Richter [ 2020-09-13 ]

Hi Mike,

thanks for your bug report.

By default MariaDB Connector/C uses unbuffered result sets (like pymysql.cursor.SSCursor), in SQLAlchemy mariadbconnector.py dialect I added therefore in do_execute a buffered=True (looks like it was removed?!).

When using unbuffered cursor in pymysql, I'm getting also an error:, and rollback wasn't executed correctly:

Python 3.8.2 (default, Jul 16 2020, 14:00:26) 
[GCC 9.3.0] on linux
 
Type "help", "copyright", "credits" or "license" for more information.
 
>>> import pymysql
 
>>> 
>>> conn= pymysql.connect(unix_socket="/tmp/mysql.sock", db="test")
 
>>> cursor= pymysql.cursors.SSCursor(conn)
 
>>> conn.autocommit= False
 
>>> cursor.execute("CREATE TABLE bug(a int)")
 
0
 
>>> cursor.execute("SELECT 1")
 
18446744073709551615
 
>>> cursor.fetchone()
 
(1,)
 
>>> conn.rollback()
 
/home/georg/.local/lib/python3.8/site-packages/pymysql/connections.py:748: UserWarning: Previous unbuffered result was left incomplete
 
  warnings.warn("Previous unbuffered result was left incomplete")
 
>>> cursor.execute("CREATE TABLE bug(a int)")
 
Traceback (most recent call last):
 
  File "<stdin>", line 1, in <module>
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 163, in execute
 
    result = self._query(query)
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 426, in _query
 
    conn.query(q, unbuffered=True)
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/connections.py", line 505, in query
 
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/connections.py", line 717, in _read_query_result
 
    result.init_unbuffered_query()
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/connections.py", line 1086, in init_unbuffered_query
 
    first_packet = self.connection._read_packet()
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/connections.py", line 676, in _read_packet
 
    packet.raise_for_error()
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/protocol.py", line 223, in raise_for_error
 
    err.raise_mysql_exception(self._data)
 
  File "/home/georg/.local/lib/python3.8/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
 
    raise errorclass(errno, errval)
 
pymysql.err.OperationalError: (1050, "Table 'bug' already exists")

Comment by Mike Bayer [ 2020-09-13 ]

ah there you go, the buffered=True got taken out. then there's no bug here (though IMO buffered should be the default), sorry for the noise.

Comment by Mike Bayer [ 2020-09-13 ]

we do the "buffered / unbuffered" thing at the cursor() level , looks like mariadb connector supports that as well, so that's a better hook for us. thanks!

Comment by Georg Richter [ 2020-09-13 ]

You're welcome - closing the issue

Generated at Thu Feb 08 03:30:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.