#115 InnoDB+auto_increment broken in MySQLdb 1.1.7

MySQLdb-1.1
closed
Andy Dustman
MySQLdb (285)
1
2012-09-19
2004-11-24
Michael Klatt
No

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

Discussion

  • Andy Dustman
    Andy Dustman
    2004-11-24

    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
    Andy Dustman
    2004-11-24

    Logged In: YES
    user_id=71372

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

     
  • Michael Klatt
    Michael Klatt
    2004-11-29

    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