473,692 Members | 2,412 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Jan 18 '06 #1
7 3532
Il Wed, 18 Jan 2006 14:39:09 -0500, Bernard Lebel ha scritto:

[cut]

1) It would be great if you didn't post four messages in less than an hour
^_^
2) Your code is very long! You can't expect many people to run and read it
all! You should post a very small demo program with the very same problem
as your main software. It'll help us a lot.
3) IMHO your problem looks like something related to isolation levels. You
should check with your DB-adapter docs about the issue. You may need to
manually sync/commit the connection or the cursor. Instead of re-creating
the connection, have you tried just creating a new cursor object at every
query?

If you want to do a quick-test, try any ORM, like sqlalchemy or sqlobject,
and check the results.

--
Alan Franzoni <al************ ***@gmail.com>
-
Togli .xyz dalla mia email per contattarmi.
To contact me, remove .xyz from my email address.
-
GPG Key Fingerprint:
5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E
Jan 18 '06 #2
Hi Alan,

On 1/18/06, Alan Franzoni <al************ ***@gmail.com> wrote:
Il Wed, 18 Jan 2006 14:39:09 -0500, Bernard Lebel ha scritto:
1) It would be great if you didn't post four messages in less than an hour
^_^
Yeah I know :-)
But like I said, I've been stuck for 3 days on that, so, I need to get
things off my chest. Sorry to anyone that got a smell of spam :-)

2) Your code is very long! You can't expect many people to run and read it
all! You should post a very small demo program with the very same problem
as your main software. It'll help us a lot.
Okay thanks for the advice. I have done what you have suggested.
In this first example, I fetch an integer value from the database
table. So far so good. However, if I change this value, the script
keeps printing the same value over and over, eternally. Notic that
everytime the while loop performs an iteration, a new cursor object is
created, as you suggested.

if __name__ == '__main__':

oConnection = MySQLdb.connect ( host = '192.168.10.101 ', user =
'render', passwd = 'rnrender', db = 'RenderFarm_BET A' )
sQuery = "SELECT LogLevel FROM TB_RENDERNODES WHERE ID = 108"

while 1:

oCursor = oConnection.cur sor()
oCursor.execute ( sQuery )
oResult = oCursor.fetchon e()
print oResult
oCursor.close()

print 'waiting 5 seconds'
time.sleep( 5 )


In the next one, I close the connection and create a new one. At that
point, the script prints the right value when I change it in the
database.
if __name__ == '__main__':

sQuery = "SELECT LogLevel FROM TB_RENDERNODES WHERE ID = 108"

while 1:
oConnection = MySQLdb.connect ( host = '192.168.10.101 ', user =
'render', passwd = 'rnrender', db = 'RenderFarm_BET A' )
oCursor = oConnection.cur sor()
oCursor.execute ( sQuery )
oResult = oCursor.fetchon e()
print oResult
oCursor.close()
oConnection.clo se()

print 'waiting 5 seconds'
time.sleep( 5 )


So I suspected that it had something to do with the threaded queue,
but I can see it's not, since the examples above are not using it at
all.

Btw I did not expect anyone to run through the code, but just in case
someone spotted something fishy... :-)


3) IMHO your problem looks like something related to isolation levels. You
should check with your DB-adapter docs about the issue. You may need to
manually sync/commit the connection or the cursor. Instead of re-creating
the connection, have you tried just creating a new cursor object at every
query?

If you want to do a quick-test, try any ORM, like sqlalchemy or sqlobject,
and check the results.

Okay I'll check these out.
Thanks
Bernard
Jan 18 '06 #3
Have you tried doing a "connection.com mit()" after each query attempt?
I believe mysqldb also has a connection.auto commit feature.
Jan 18 '06 #4
I'm absolutely flabbergasted.

Your suggestion worked, the loop now picks up the changed values, and
without the need to reconnect.

It's the first time I have to commit after a query, up until I wrote
this program, I used commit() was for UPDATE/INSERT types of commands
only, and always got proper fetch results.
Thanks
Bernard

On 1/18/06, Stephen Prinster <pr******@mail. com> wrote:
Have you tried doing a "connection.com mit()" after each query attempt?
I believe mysqldb also has a connection.auto commit feature.
--
http://mail.python.org/mailman/listinfo/python-list

Jan 18 '06 #5
Bernard Lebel on comp.lang.pytho n said:
I'm absolutely flabbergasted.


As I told you, i think this is related to the isolation level - I really
think it's a feature of the DB you're using - this way, until you commit,
your database lies in in the very same state on the same connection.
Suppose you make one query, and then you make another one, and you then
decide, on these result, to take a certain action on your DB; in the
meantime (between Q1 and Q2) another user/program/thread/connection might
have done a different operation on the very same db, and so you would be
getting a Q1 results from a certain state in the DB, while Q2 from a
different state, thus misleading you to take an inopportune action.

committing the connection syncs the state you're looking at with the actual
DB state.

--
Alan Franzoni <al************ ***@gmail.com>
-
Togli .xyz dalla mia email per contattarmi.
Rremove .xyz from my address in order to contact me.
-
GPG Key Fingerprint:
5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E
Jan 19 '06 #6
Thanks a lot for the explanations Alan. That really helped.
Bernard


On 1/19/06, Alan Franzoni <al************ ***@gmail.com> wrote:
Bernard Lebel on comp.lang.pytho n said:
I'm absolutely flabbergasted.


As I told you, i think this is related to the isolation level - I really
think it's a feature of the DB you're using - this way, until you commit,
your database lies in in the very same state on the same connection.
Suppose you make one query, and then you make another one, and you then
decide, on these result, to take a certain action on your DB; in the
meantime (between Q1 and Q2) another user/program/thread/connection might
have done a different operation on the very same db, and so you would be
getting a Q1 results from a certain state in the DB, while Q2 from a
different state, thus misleading you to take an inopportune action.

committing the connection syncs the state you're looking at with the actual
DB state.

--
Alan Franzoni <al************ ***@gmail.com>
-
Togli .xyz dalla mia email per contattarmi.
Rremove .xyz from my address in order to contact me.
-
GPG Key Fingerprint:
5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E
--
http://mail.python.org/mailman/listinfo/python-list

Jan 19 '06 #7
On Wed, 18 Jan 2006 18:31:39 -0500, Bernard Lebel <3d*******@gmai l.com> wrote:
I'm absolutely flabbergasted.

Your suggestion worked, the loop now picks up the changed values, and
without the need to reconnect.

It's the first time I have to commit after a query, up until I wrote
this program, I used commit() was for UPDATE/INSERT types of commands
only, and always got proper fetch results.

I found a similar problem occurred when I upgrade MySQL to some of the
4.1.x versions and the newest 5.x. The default table type now seems to
be InnoDB which activates transactions, so now the autocommit has to be
turned on in mysqldb or explicit commit's have to be placed into the
code.

--Mark
Jan 26 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2458
by: Börni | last post by:
Hi, I have an sql query like this: SELECT column FROM table WHERE column1="3" AND column2="1" This query works perfectly if i run it in the command line, to be exactly it return two results. But if i run it from php i just get the first of the two results. Any ideas? Mysql 4.1.8 php 5.0.3
5
5084
by: Chris Stromberger | last post by:
When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using MySQLdb, but is there any way to get the number of rows matched? I want to find out, when rows updated = 0, if there were no updates because the row wasn't found (rows matched will = 0) or because the update would not have changed any data (rows matched =...
1
6822
by: Sheila King | last post by:
I have searched for an answer to this question on the comp.lang.python archives at Google Groups, and also looked through the (sparse) MySQLdb documentation, and cannot confirm for a FACT what I think I know. Looking for affirmation before I go "assuming" something and getting myself into trouble somewhere along the line... Here is what I *think* I know...let me know if I'm wrong, for crying out loud!!!
1
2582
by: Peter Nikolaidis | last post by:
Greetings, I am attempting to get MySQLdb 0.9.2 installed on Mac OS 10.2 with a Fink distribution of Python 2.2.2. I have seen only a few posts on the subject, some of them relate to "conflicting header files," but I don't know what to do about conflicting header files, or where I would find them, and once I found them, which ones to remove. I have compiled MySQL 4.1 and installed into /usr/local/mysql, but since have moved to a Fink...
2
5045
by: Tim Williams | last post by:
I'm trying to write a simple python program to access a MySQL database. I'm having a problem with using MySQLdb to get the results of a SQL command in a cursor. Sometimes the cursor.execute works, sometimes not. From mysql: mysql> show databases; +-----------+ | Database |
0
1469
by: Wesley Kincaid | last post by:
I'm attempting to run a simple query through MySQLdb's cursor.execute(). However, when the request includes a timestamp field, I'm getting "ValueError: invalid literal for int(): 9-." Could someone please explain what I'm doing wrong? The table is served off of MySQL 4.0.20 and contains the following fields:
2
2192
by: ws Wang | last post by:
MySQLdb is working fine at command line, however when I tried to use it with mod_python, it give me a "server not initialized" error. This is working fine: ----------------------- testmy.py ------------------------------- #!/usr/bin/python import MySQLdb db = MySQLdb.connect(host="localhost", user="root", passwd="mypass", db="my_db") cursor = db.cursor()
2
6287
by: dave.dex | last post by:
Hi all, I've been searching the docs like mad and I'm a little new to python so apologies if this is a basic question. I would like to extract the results of the following query into a list - SELECT columnname FROM tablename. I use the following code. # Create a connection object and create a cursor db = MySQLdb.Connect(<my-db-info)
2
4316
by: Nikhil | last post by:
I am using the MySQLdb python module. I have a table named 'testing' with few columns, under the 'test' database, what is hosted on a remote mysql server. I want to run the following query to get a comma-seperated information from the table LOCK TABLES foo READ; SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
0
8610
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8544
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8968
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8810
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7633
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5821
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4559
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2242
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1961
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.