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 7 3513
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
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
Have you tried doing a "connection.com mit()" after each query attempt?
I believe mysqldb also has a connection.auto commit feature.
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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...
|
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...
|
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...
|
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 |
| |
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:
|
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")...
|
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)
|
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'
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
| |
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |