#115 InnoDB+auto_increment broken in MySQLdb 1.1.7

[Michael Klatt]

MySQLdb 1.1.7
MySQL 4.1.7.
RH Linux 9.0, 7.3 using RPM install
Python 2.3.4

Create a table like this:

CREATE TABLE test_table (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) Type=InnoDB;

Then use code like this to (try to) add new records:

import MySQLdb
db = MySQLdb.connect(...)
c = db.cursor()
c.execute("insert into test_table values ()")
c.execute("SELECT LAST_INSERT_ID()")
print c.fetchall()

The last_insert_id value will increment, but no records
will actually be created. If you change the table type
from InnoDB to MyISAM, the code works just as expected.

I suspect that this is a problem with MySQLdb,
because when I use the "mysql" command-line client, I
don't encounter any problem.

Unfortunately I can't revert to MySQLdb 1.0.0 since it
won't compile with MySQL 4.1.7.

-Michael

[Andy Dustman]

Logged In: YES
user_id=71372

The problem is almost certainly that the 1.1 series does not
operate in autocommit mode by default, to comply with the DB
API standard. Try either of these:

import MySQLdb
db = MySQLdb.connect(...)
db.autocommit(True)
c = db.cursor()
c.execute("insert into test_table values ()")
print c.lastrowid
print c.fetchall()

...or...

import MySQLdb
db = MySQLdb.connect(...)
c = db.cursor()
c.execute("insert into test_table values ()")
print c.lastrowid
db.commit()
print c.fetchall()

Note use of c.lastrowid; this is the portable way to do it.

http://www.python.org/peps/pep-0249.html

[Andy Dustman]

Logged In: YES
user_id=71372

(actually you don't need those c.fetchall()s in my examples)

[Michael Klatt]

Logged In: YES
user_id=1127829

Thank you for your quick and very accurate response. As
soon as I turned auto-commit on by default, the problem is
fixed.

I've looked around the documentation and I couldn't find any
reference to this change in behavior. I just found a couple
posts on the mailing list, but I'm certain many more
people will encounter this problem when updating MySQLdb on
their systems and won't see that mailing list post. Could
you please document this somewhere so people updating their
system will be aware of it?

Thanks!

Michael