Does MySQLdb rollback on control-C? Maybe not.

[John Nagle]

I would expect MySQLdb to rollback on a control-C, but it doesn't
seem to have done so. I have a program which does a thousand or
so INSERT operations in one transaction on an InnoDB table.

I kill it with a control-C on Windows, and it aborts. But
it looks like some of the INSERT operations took place. Here's
the abort, from the middle of an INSERT operation.

Filing index item already in database under another date:
edgar/data/1455650/0000950123-10-062814.txt
Traceback (most recent call last):
....
File "C:\projects\sitetruth\edgar\edgarfilingindex.py", line 93, in
dbinsert
cursor.execute(sql, values) # do the insert
File "C:\python26\lib\site-packages\MySQLdb\cursors.py", line 166, in
execute
self.errorhandler(self, exc, value)
File "C:\python26\lib\site-packages\MySQLdb\connections.py", line 35,
in defaulterrorhandler
raise errorclass, errorvalue
KeyboardInterrupt
Terminate batch job (Y/N)? y

Checking with the manual MySQL client, looking at the table
before and after the run, the table did change when the program ran.
Not good.

The INSERT looks like this:

INSERT INTO edgarfilingindex
(cik, company_name, form_type,
date_filed, file_name, date_indexed, confidence)
VALUES (%s,%s,%s,%s,%s,%s,%s)

SHOW CREATE TABLE returns this:

CREATE TABLE `edgarfilingindex` (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

so it's definitely an InnoDB table.

I have print statements at all commit calls, and there don't seem to
be any unwanted commits happening. I'm not doing anything that
forces a commit, like RENAME or ALTER or CREATE; it's just
simple INSERT operations. I'm not doing an explicit START
TRANSACTION, but I shouldn't have to.

I put a SHOW VARIABLES LIKE "autocommit" in the program, and
it comes back with ("autocommit", "OFF"). So that's not the problem.

I even did an explicit START TRANSACTION at the beginning of the
update, and it doesn't help.

Something is broken.

Python 2.6, MySQLdb 1.2.2-py2.6, MySQL 5.1.47-com, Windows 7.

John Nagle

 

[Mike Kent]

    I would expect MySQLdb to rollback on a control-C, but it doesn't
seem to have done so.  

Something is broken.

I wouldn't expect it to, I'd expect to roll back on an exception, or
commit if not. Perhaps this will help you. I use it in production
code.

##
# This is a transaction context manager. It will ensure that the code
in
# the context block will be executed inside a transaction. If any
exception
# occurs, the transaction will be rolled back, and the exception
reraised.
# If no exception occurs, the transaction will be committed.
# db is a database connection object.

from contextlib import contextmanager

@contextmanager
def transaction(db):
db.begin()
try:
yield None
except:
db.rollback()
raise
else:
db.commit()

 

[John Nagle]

I wouldn't expect it to, I'd expect to roll back on an exception, or
commit if not.

MySQL does rollback properly on connection failure or program
exit. It turns out I had a commit on that database handle in logging
code.

John Nagle