Python MySQLdb Insert fails on MariaDB: Doesn't persist to new connection

Question

The assert statement in the following code fails in an environment running mariadb, while it runs fine in an (older) environment running mysql. The mariadb environment seems to not persist the result of the insert statement. Connecting with a command line client confirms that the insert was not persisted.

import MySQLdb

def newCon():
    return MySQLdb.connect('localhost', 'root', '', 'test')

def testStatement(con, aStatement):
    # print "---"
    # print aStatement
    cur = con.cursor()
    cur.execute(aStatement)
    result = cur.fetchall()
    cur.close()
    # print result
    return result

def test():
    con = newCon()

    testStatement(con, "DROP TABLE IF EXISTS TestTable")
    testStatement(con, """CREATE TABLE TestTable (
            id INT NOT NULL AUTO_INCREMENT,
            AccountId VARCHAR(50),
            PRIMARY KEY(id))""")
    testStatement(con, "INSERT INTO TestTable (AccountId) VALUES ('XYZ')")
    myCount1 = testStatement(con, "SELECT count(*) from TestTable")[0][0]

    con.close()
    con = newCon()

    myCount2 = testStatement(con, "SELECT count(*) from TestTable")[0][0]

    con.close()

    assert myCount1 == myCount2, \
      "count1 = " + str(myCount1) + " != count2 = " + str(myCount2)

test()

The environment in which it runs fine is: CentOS 6.7 python 2.6.6 mysql-python 1.2.3 mysql server 5.1.73

The environment in which it fails is: Fedora 21 python 2.7.8 mysql-python 1.2.3 mariadb server 10.0.21

The failed output reads:

Test2.py:10: Warning: Table 'mysql.table_stats' doesn't exist
  cur.execute(aStatement)
Traceback (most recent call last):
  File "Test2.py", line 37, in <module>
    test()
  File "Test2.py", line 35, in test
    "count1 = " + str(myCount1) + " != count2 = " + str(myCount2)
AssertionError: count1 = 1 != count2 = 0

I don't know what the table_stats warning is about.

Question: Am I using the api correctly? Can anybody make this test pass with a mariadb environment?

score 1 · Accepted Answer · answered Apr 27 '16 at 17:19

1

You haven't committed the transaction.

answered Apr 27 '16 at 17:19

Daniel Roseman

588,541
66
880
895

How do I commit the transaction? Is that specific to mariadb, or should I have done that with mysql too? – Olivier Apr 27 '16 at 17:24
Thanks, that worked! Surrounded the INSERT with BEGIN - COMMIT – Olivier Apr 27 '16 at 17:30
If `autocommit` had been on, the problem would not exist. I wonder why it was off. See http://stackoverflow.com/questions/12059424/about-mysqldb-conn-autocommittrue – Rick James Apr 30 '16 at 19:21
autocommit=off is a stupid default for Python. – Rick James Apr 30 '16 at 19:27

Python MySQLdb Insert fails on MariaDB: Doesn't persist to new connection

1 Answers1