MySQLdb - Query/fetch don't return result when it *theorically* should

[Bernard Lebel]

Hello,

I'm stumbled at a serious problem, and quite frankly getting
desparate. This is a rather long-winded one so I'll try to get
straight to the point.

I have this Python program, that performs MySQL queries to a database.
These queries are performed at regular intervals. Basically it is
looking for fields that match certain criterias. Such fields are not
present at all time. When the program finds such field, it then takes
an action, if nothing is found, just keep this query-fetch cycle
forever.

I don't know where the source of the problem is, but the symptoms go as follow:

When the program starts, the first time it performs a certain
query/fetch, a result is fetched. Now, later on, when the action done
after the first fetched is completed, the program starts
querying/fetching again. But it seems unable to the fetch anything
else with the same query.

If an entry that the query should return is inserted in the database,
the program just can't get it once it has started.

A more schematic representation of the symptoms.

What it should do:
1. program starts
2. enter query-fetch cycle
3. find specific entry, take action
4. action done
5. resume query-fetch cycle
6. find specific entry, take action
7. action done
8. resume query-fetch cyle
9. there was nothing to be found, continue cycle
10. find specific entry, take action
11. action done
12. resume query-fetch cycle...........

What it does now:
1. program starts
2. enter query-fetch cycle
3. find specific entry, take action
4. action done
5. resume query-fetch cycle
6. no more entry fetched despite valid entries being in the database

What is does now also:
1. program starts
2. enter query-fetch cycle
3. there was nothing to be found, continue cycle
4. valid entry added my myself, manually, and successfully committed
5. query-cycle continues, entry just added never found.......

I have looked at connection objects, cursor objects, and I just can't
seem to find the cause of that behavior.

In parallel, if I run individual queries in a command line shell, I
fetch the entries as expected.
The only way I have found to force the program to find the new entry,
is to close the connection and create a new one, every time it
performs a transaction. Not very efficient.



To give a little more details about the implementation.... when the
program starts, it starts a little queue server in a separate thread.
This queue server is nothing more than a list. Each time a query has
to be performed, it is added to the queue.
The queue server checks the queue to find if it has something to do.

When if finds something, it carries the entire operation, from query
to fetch/commit. It then stores the result in a dictionary, using a
unique ID as the key. At that point, the list element is removed from
it.

The function that submitted the query to the queue, during that times,
checks the dictionary until the result of the operation shows up, and
then checks the actual result. The result is then returned to the
original function who submitted a database transaction.



I don't know what other details to provide, other than the sources
themselves.......
farmclient_2.0_beta05.py is the top program file
The other py files are the imported modules
- fcSql is the actual database transaction management module
- fcJob has a function called readyGetJob(), wich is at the origin of
the MySQL query. The actual query being used is located on line 202.
The sql file is used to create the database

Thanks for any help, let me know if you need more details

Bernard