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

[Bernard Lebel]

Btw I have tried running much simpler versions of the query, like
"SELECT * FROM TB_CURRENT_JOBS WHERE Status = 'Pending'", but yet
again I get the same results. *sigh*


Bernard



On 1/18/06, Bernard Lebel <3dbernard at gmail.com> wrote:
> Oops, sorry: link to sources
> http://www.bernardlebel.com/scripts/nonxsi/help/
>
>
> Thanks again
> Bernard
>
>
>
> On 1/18/06, Bernard Lebel <3dbernard at gmail.com> wrote:
> > 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
> >
>