gentoo 64-bit, python with MySQLdb module

Hi John,

Your script creates a table without specifying the table engine explicitly. In 5.5 (both MariaDB and MySQL) the default storage engine is InnoDB, unless it's set otherwise. InnoDB is a transactional engine.
Upon connection, python sets autocommit = 0. It's not in your script, it's done automatically.
On step 4 from the description, you start a new transaction by running INSERT, and then continue it by SELECT.
But you never execute COMMIT or issue any statement which would do the commit implicitly, such as a DDL. You just close the connection, so the transaction rolls back.
So, when you connect again and try to read from the table, naturally it is empty.

Now, if you are saying it does not work like that with MySQL, it can be on one of two reasons.
1. You are running it on MySQL 5.1, which has MyISAM as a default engine. It's not transactional, so once you executed INSERT, the data is there and stays there.
2. You are running it on MySQL 5.5 with default storage engine set to MyISAM (or anything else that is not transactional).

You can fix it by adding COMMIT before closing the connection.
Alternatively, if you don't mean the table to be transactional, you can add ENGINE=MyISAM to the CREATE statement.
Alternatively, if you don't want InnoDB to be the default engine, you can change it globally in your server.

Please let us know if the explanation is sufficient.
Thanks.

Elena,

Thank you for your quick response and thorough explaination. You were
right I tested the script on a mysql-5.1 server. I didn't know that such
big changes were brewing. I started familiarizing myself with the
differences between the two engines, but for now I switched the default
engine back to MyISAM in my.cnf and the script is working as expected. I
guess maybe I got away with being sloppy for a lot of years with MyISAM by
not issuing the COMMIT statement. Unfortunately I have quite a bit of code
which will require upgrading.

The good news is that I can now go ahead and convert my server over to
mariadb.

Thanks,
John

On Wed, May 8, 2013 at 4:45 PM, Elena Stepanova (JIRA) <

Please be aware that there was another important change in behavior happened in MySQL 5.5 (and consequently in MariaDB 5.5) which might make your current setup work not quite as you expected. I think python scripts are particularly affected due to this automatic 'autocommit = 0' setting on connection.

The problem is discussed with original developers here: http://lists.mysql.com/internals/38580, or if you are interested in more details, you could search for "MySQL MDL". In the essence, if you are using non-transactional tables (and hence naturally don't care to do COMMIT in your scripts), you might get in trouble with nearly-ever-lasting table locks.

As a quick workaround, I would recommend setting lock_wait_timeout to a fairly low value, probably a few seconds, instead of 1 year which is the default. But it won't solve the problem, it will just avoid getting deadlocks. Then you will probably want to consider the whole thing more carefully – possibly there are settings for python that override the 'autocommit = 0' behavior; or, you might need to set it back to 1 at the beginning of your scripts; or, you might want to edit your existing scripts to add COMMIT when it's due (of course it only makes sense if you want to switch to the transactional engine). In any case, it's up to you.