Chronic stale results using MySQLdb in Python

Question

My Python program queries a set of tables in a MySQL DB, sleeps for 30 seconds, then queries them again, etc. The tables in question are continuously updated by a third-party, and (obviously) I would like to see the new results every 30 seconds.

Let's say my query looks like this:

"select * from A where A.key > %d" % maxValueOfKeyFromLastQuery 

Regularly I will see that my program stops finding new results after one or two iterations, even though new rows are present in the tables. I know new rows are present in the tables because I can see them when I issue the identical query from interactive mysql (i.e. not from Python).

I found that the problem goes away in Python if I terminate my connection to the database after each query and then establish a new one for the next query.

I thought maybe this could be a server-side caching issue as discussed here: Explicit disable MySQL query cache in some parts of program

However:

1. When I check the interactive mysql shell, it says that caching is on. (So if this is a caching problem, how come the interactive shell doesn't suffer from it?)

2. If I explicitly execute SET SESSION query_cache_type = OFF from within my Python program, the problem still occurs.

Creating a new DB connection for each query is the only way I've been able to make the problem go away.

How can I get my queries from Python to see the new results that I know are there?

Answer 1

This website and this website contain information on the same problem. In order to keep your tables up to date, you must commit your transactions. Use db.commit() to do this.

As mentioned by the post below me, you can remove the need for this by enabling auto-commit. this can be done by running db.autocommit(True)

Also, auto-commit is enabled in the interactive shell, so this explains why you didn't have the problem there.