mysql select some sort of caching

David Sfiligoi sfiligoi at gmail.com
Wed Oct 21 04:41:10 CEST 2009


Hi
I am normally an SQLlite person because it just works... but I decided to 
keep inproving my skills set and do my new project using a MySQL database 
backend because of the architecture decision I made(horizontal scalable 
system via message queues). The project is architected around a rabbitmq 
system with bunch of consumers(potentially large) and publishers... I 
digress, the rabbit has a no part to play in my myunderstanding with 
mysql.

I have a consumer that get data from a queue(a url). This queue is 
checked in a loop. Once a url has been published in the queue, the 
consumer sees it and call a function to do something with it.  Using a 
column in the table I verify if 'today's task' has been done already
(compare the current date to the last task date that I store in the table 
last time I executed the task)

So normally I would open a connection and instentiate a cursor for my 
queries once at a global level(like find out if the current date is > 
than the last task date). Then go in an infinite loop that wait for data 
to arrive in the queue and carry the cursor and connection over.  However 
this is the issue I seem to run into.  I test the code by zapping, using 
mysql query tools, the table's last task date column with an older date 
so that current date is larger than the current date.  Issue is that 
whatever prior date I put in the table's last task date colum somehow 
they are not seen by the SELECT query
sqlcur.execute("SELECT `url`d` from `url_snapshot` WHERE `url` = %s and 
`lastsnapshotdate` < %s",(url,currentdate)) 
that query returns old data.

Just like if the query is cached....yet the data actually changed in the 
database.

However when I recreate a new connection and cursor everytime I get a new 
message in the queue(url), therefore within the message found loop there 
are no issues seeing the last update done via the mysql query tool

I don't think this has to do with mysql caching features since its 
suppose to detect when the fields changed.

Any ideas?

Thanks for your Time,
David



More information about the Python-list mailing list