Don't forget the COMMIT in MySQL

Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you’ll have to commit (or roll back) your inserts, deletes or updates.

I’ve seen it a few times now with people being surprised that no data is going into the tables. It’s not so a silly problem in the end. If you are used to the defaults in MySQL you don’t have to commit anything since it is automatically done for you.

Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off. You could turn it back on, but it’s good practice to be explicit and commit in your code. Remember the Zen of Python! Here is just a small example to show it. Uses MySQL Connector/Python, but it should also work with the other MySQL database interfaces:

import mysql.connector
cnx = mysql.connector.connect(db='test')
cur = cnx.cursor()

cur.execute("""CREATE TABLE innodb_t1 (
id INT UNSIGNED NOT NULL,
c1 VARCHAR(128),
PRIMARY KEY (id)
) ENGINE=InnoDB""")

ins = "INSERT INTO innodb_t1 (id,c1) VALUES (%s,%s)"
cur.execute(ins,
(1,'MySQL Support Team _is_ already the best',))
cnx.commit()
cur.close()
cnx.close()

Comments

gerva

Wed Jun 22 2011, 13:10

Hi Geert, you use COMMIT since the CREATE TABLE statements requires an implicit commit or would you use the commit also after an INSERT?

Thanks,

Mattia

Geert Vanderkelen

Fri Jun 24 2011, 06:36

The CREATE TABLE statement is just there to complete the example. You have to commit after an INSERT or any DML (like my post says).

gerva

Sun Jun 26 2011, 19:50

Thanks for the reply. I’ve aslo seen that using cnx.set_autocommit(True) can be another option to use instead of repeatedly use the commit() function. Btw since InnoDB is a transactional engine it means that for every DML it appends ‘START TRANSACTION’ or am I missing something?

Thanks,

Mattia

Geert Vanderkelen

Mon Jul 11 2011, 08:32

@gerva MySQL by default does autocommit, regardless of Storage engine used. For example, if you do an INSERT INTO.. It will commit it once you execute the statement. The Python DB API 2.0 requires to have auto-commit turned off, so you need to commit every time (which is OK!). You can indeed force auto-commit back on in the Connector/Python, that’s up to you. If you use InnoDB, you probably want transactions. BEGIN; UPDATE..; INSERT..; INSERT..; DELETE..; COMMIT; .. If you need that, you should not turn on auto-commit, and do an explicit commit.

Remember Python Zen: “Explicit is better than implicit.”

Jeffrey McManus

Tue Apr 2 2013, 06:44

But more importantly, remember database Zen: “Don’t lose data.” And then there’s user experience Zen: “Follow the principle of least astonishment.” These principles are far, far more important than “being Pythonic” in this context.

I just wasted 90 minutes trying to figure out why an insert into an InnoDB table was failing silently; turns out that this was the reason. I’ve programmed MySQL in four or five different programming languages and Python is the only one that works this way. I understand where you’re coming from, but that doesn’t make it any less frustrating.

Geert Vanderkelen

Wed Apr 3 2013, 06:29

@Jeffrey McManus Using an explicit COMMIT is not something specific to or dictated by Python. If you are concerned about “Don’t lose data”, thus ACID, you would/should have used COMMIT in any language for any (transactional) DBMS.

Note that MySQL server can be configured with “autocommit=0”, and lots of projects out there will not work when using InnoDB because these “forgot” committing.

gerva: Wed Jun 22 2011, 13:10

Hi Geert, you use COMMIT since the CREATE TABLE statements requires an implicit commit or would you use the commit also after an INSERT?

Thanks,

Mattia
Geert Vanderkelen: Fri Jun 24 2011, 06:36

The CREATE TABLE statement is just there to complete the example. You have to commit after an INSERT or any DML (like my post says).
gerva: Sun Jun 26 2011, 19:50

Thanks for the reply. I’ve aslo seen that using cnx.set_autocommit(True) can be another option to use instead of repeatedly use the commit() function. Btw since InnoDB is a transactional engine it means that for every DML it appends ‘START TRANSACTION’ or am I missing something?

Thanks,

Mattia
Geert Vanderkelen: Mon Jul 11 2011, 08:32

@gerva MySQL by default does autocommit, regardless of Storage engine used. For example, if you do an INSERT INTO.. It will commit it once you execute the statement. The Python DB API 2.0 requires to have auto-commit turned off, so you need to commit every time (which is OK!). You can indeed force auto-commit back on in the Connector/Python, that’s up to you. If you use InnoDB, you probably want transactions. BEGIN; UPDATE..; INSERT..; INSERT..; DELETE..; COMMIT; .. If you need that, you should not turn on auto-commit, and do an explicit commit.

Remember Python Zen: “Explicit is better than implicit.”
Jeffrey McManus: Tue Apr 2 2013, 06:44

But more importantly, remember database Zen: “Don’t lose data.” And then there’s user experience Zen: “Follow the principle of least astonishment.” These principles are far, far more important than “being Pythonic” in this context.

I just wasted 90 minutes trying to figure out why an insert into an InnoDB table was failing silently; turns out that this was the reason. I’ve programmed MySQL in four or five different programming languages and Python is the only one that works this way. I understand where you’re coming from, but that doesn’t make it any less frustrating.
Geert Vanderkelen: Wed Apr 3 2013, 06:29

@Jeffrey McManus Using an explicit COMMIT is not something specific to or dictated by Python. If you are concerned about “Don’t lose data”, thus ACID, you would/should have used COMMIT in any language for any (transactional) DBMS.

Note that MySQL server can be configured with “autocommit=0”, and lots of projects out there will not work when using InnoDB because these “forgot” committing.