Re: [sqlalchemy] Session problems
On Oct 7, 2010, at 12:53 AM, Warwick Prince wrote: Hi Michael I'm still having a couple of issues with the sessions, but I'm now starting to suspect mysqlconnector.. For completeness, could you please let me know if there is anything specific I need to do to close down a session / connection / engine etc if I want to completely release it. I see session.close(), but that appears to be more about committing etc, and I can't see anything specific I need to do to an engine etc. Basically, I just want to ensure that I'm doing everything correctly when I drop a session and kill the thread that it was in. close() releases any open connections to the connection pool, and since the pool has a limit, if you weren't returning things to the pool that would be apparent once the pool raises an error. Also, If I'm NOT creating lots of sessions for short periods of time (i.e. web services) (which I'm not) do I need to consider pools for any specific reason? They appear to be more about scaling that sort of situation. I'm basically creating a session and hanging on to it - doing lots of queries, updates etc with lots of commit/roll back, then dropping the session and exiting some time later -- Is my interpretation of session use correct? you pretty much dont need to consider pools at all, just create an engine, use it. If you only use one connection at a time, the pool would only have one connection. I'll play with other avenues of investigation before bringing the current session issues to the table. ;-) Cheers Warwick On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote: I can't quite get my head around the scope of sessionmaker() yet.. I've tried putting Session = sessionmaker() as a global to the entire codebase (works but has same problem) do you mean scoped_session here ? sessionmaker is just a constructor for a new Session.It doesn't hold onto anything. scoped_session OTOH is nothing more than a thread local variable. Background on this concept is available here: http://docs.python.org/library/threading.html#threading.local .The remove() call removes the current thread local's context. If your app were single threaded, you could replace it with a single Session object, where you just call close() instead of remove() - it wouldn't be very different. Within the context of a multiprocess, single-threaded application, threading.local doesn't have any effect - there's just one thread. Importantly.. I can completely close all my processes and cold start my code - and this error continues until I restart mySQL ! i.e. NO session will work again, but I can do basic queries. What the?! I've seen this happen with PG when we are testing out two-phase transactions.You might want to ask on some MySQL forums what queries you might do to look at current lock/transaction state. BTW: It someone says You should be using scoped_session.. Please explain how to have either more than one session in the same context (I use three for a possible three different binds) yeah I actually have an app with a couple of scoped sessions, since there are two different databases and operations generally proceed with one or the other. or how to create one session that I can bind to more than one engine, and not have to know in advance all possible tables I might want to use on each engine.. If the table metadata is bound to an engine, then the session doesn't need to be bound. I.e. if tables A, B, C on metadata X are bound to engine P, tables D, E, F on metadata Y are bound to engine Q, you just use the Session, and it will handle the two engines as needed. If you really want total control, using some ruleset that's not quite as simple as table-metadata-engine, you can subclass Session and override get_bind(). I've never recommended that to anyone, but I put it out there just to help de-mystify the situation. Its just one call that takes in a mapper, returns an engine. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email
[sqlalchemy] Session problems
Dear All I'm having a very strange issue with Sessions that I'm hoping someone can guide me on; I have a situation where a large body of code spawns new processes (multiprocessing.process). The core design manages 3 (possible) database connections, and I've used a unique session for each. The session is create thus within the new process (i.e. not handed in) Session = sessionmaker()# This is global class myManager(object): # I use a manager class to manage the engines, MetaData and Sessions of the 3 possible DB's I create new sessions in here like this self.session1 = Session(bind = engine1) self.session2 = Session(bind = engine2) self.session3 = Session(bind = engine3) All this works fine and all testing up to this point has been perfect. Now, when I start to load test and create more than one concurrent process, I'm getting some form of corruption of my connections to the Database (mysql+mysqlconnector) !? All NON session based access to the server still work, but after I start the second process, BOTH processes lost the ability to use the sessions create (ones that where running and working stop immediately the second process starts) with this; (InterfaceError) 2055: Lost connection to MySQL server at '192.168.50.2:3306', system error: 10054 u'SELECT products.. The connection is NOT lost really, as I can still do NON session based queries using the same engine that the session is bound to. As far as I can see, since the code is running in a completely different process, how can they be interacting with each other and breaking the connection to mySQL? I've tried using scoped_session but could not work out how to have the 3 sessions as above all in the same context - so gave up. As far as I can see though on my limited understanding of the Session process, I have isolated everything so there should be no issues. Any ideas? Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session problems
On 06/10/2010 10:46, Warwick Prince wrote: (InterfaceError) 2055: Lost connection to MySQL server at '192.168.50.2:3306 http://192.168.50.2:3306', system error: 10054 u'SELECT products.. ...it would be interesting to see the rest of that error message... Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Session problems
Hi All
More details on the session issue;
Please note, the test I just did was this;
Restart the mySQL server.
Start Process #1 and create a session and use it (works fine)
Start Process #2 (Identical code to #1, just another instance).
Go back to #1 and attempt to do another query and I get this;
File Z:\warwickprince On My Mac\Desktop\Code Developement\MSI\Clear
Enterprise\DAP2 Python\trunk\DAPForm.py, line 2556, in _moveToRow
self._formInstance._currentRowProxy = self._query[self._currentPosition]
# Get the one at that position
File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1446,
in __getitem__
return list(self[item:item+1])[0]
File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1444,
in __getitem__
return list(res)
File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1604,
in __iter__
return self._execute_and_instances(context)
File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1609,
in _execute_and_instances
mapper=self._mapper_zero_or_none())
File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 701,
in execute
clause, params or {})
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1194,
in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1272,
in _execute_clauseelement
parameters=params
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1380,
in __create_execution_context
connection=self, **kwargs)
File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line
381, in __init__
self.cursor = self.create_cursor()
File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line
523, in create_cursor
return self._connection.connection.cursor()
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 920,
in connection
Can't reconnect until invalid
InvalidRequestError: Can't reconnect until invalid transaction is rolled
back
BTW: self._query is self._Session.Query(Product)
I can't quite get my head around the scope of sessionmaker() yet.. I've
tried putting Session = sessionmaker() as a global to the entire codebase
(works but has same problem) I've put it inside the process code so that it
can not share any state or memory with the other one(s) (This is the current
config) and yet it's immediately trashing the first session as soon as I
create a second. I'm only *reading* from either of the sessions at this
stage, so there is actually no pending data or writes being done
what-so-ever, so I don't know what transaction should be rolled back or why
it's invalid.
The connection (created in the same myManager class) used for direct
table.select() operations still works fine even after the above error
happens.
Importantly.. I can completely close all my processes and cold start my
code - and this error continues until I restart mySQL ! i.e. NO session
will work again, but I can do basic queries. What the?!
Hope someone can shed some light on this one :-S
BTW: It someone says You should be using scoped_session.. Please explain
how to have either more than one session in the same context (I use three
for a possible three different binds) or how to create one session that I
can bind to more than one engine, and not have to know in advance all
possible tables I might want to use on each engine..This is a generic
session that I want to be able to use for all tables in the bound engine -
thus three sessions given that is the maximum possible choices of DB in this
scenario. What if I don't bind a session to any engine.. Does it then
follow the bind on the mapped table class for a given query?
Cheers
Warwick
On 6 October 2010 20:02, Warwick Prince warwi...@mushroomsys.com wrote:
Hi Chris
It's simply trapped as a
except Exception as message:
I'll see what I can do - Just a mo..
Cheers
Warwick
On 6 October 2010 19:54, Chris Withers ch...@simplistix.co.uk wrote:
On 06/10/2010 10:46, Warwick Prince wrote:
(InterfaceError) 2055: Lost connection to MySQL server at
'192.168.50.2:3306 http://192.168.50.2:3306', system error: 10054
u'SELECT products..
...it would be interesting to see the rest of that error message...
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session problems
On Oct 6, 2010, at 5:46 AM, Warwick Prince wrote: Dear All I'm having a very strange issue with Sessions that I'm hoping someone can guide me on; I have a situation where a large body of code spawns new processes (multiprocessing.process). The core design manages 3 (possible) database connections, and I've used a unique session for each. are you using a unique Engine for each process ? this part is critical. DBAPI connections don't travel across process boundaries very well nor do connection pools. When a new child begins, you need to call create_engine() again for all engines, and use those new binds within that child and only there. You also should be careful that any objects from the parent passed to child are immediately merged() into the session local to the child - preferably, the child would just re-query its own Session for all the data it needs. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session problems
On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote: I can't quite get my head around the scope of sessionmaker() yet.. I've tried putting Session = sessionmaker() as a global to the entire codebase (works but has same problem) do you mean scoped_session here ? sessionmaker is just a constructor for a new Session.It doesn't hold onto anything. scoped_session OTOH is nothing more than a thread local variable. Background on this concept is available here: http://docs.python.org/library/threading.html#threading.local .The remove() call removes the current thread local's context. If your app were single threaded, you could replace it with a single Session object, where you just call close() instead of remove() - it wouldn't be very different. Within the context of a multiprocess, single-threaded application, threading.local doesn't have any effect - there's just one thread. Importantly.. I can completely close all my processes and cold start my code - and this error continues until I restart mySQL ! i.e. NO session will work again, but I can do basic queries. What the?! I've seen this happen with PG when we are testing out two-phase transactions. You might want to ask on some MySQL forums what queries you might do to look at current lock/transaction state. BTW: It someone says You should be using scoped_session.. Please explain how to have either more than one session in the same context (I use three for a possible three different binds) yeah I actually have an app with a couple of scoped sessions, since there are two different databases and operations generally proceed with one or the other. or how to create one session that I can bind to more than one engine, and not have to know in advance all possible tables I might want to use on each engine.. If the table metadata is bound to an engine, then the session doesn't need to be bound. I.e. if tables A, B, C on metadata X are bound to engine P, tables D, E, F on metadata Y are bound to engine Q, you just use the Session, and it will handle the two engines as needed. If you really want total control, using some ruleset that's not quite as simple as table-metadata-engine, you can subclass Session and override get_bind(). I've never recommended that to anyone, but I put it out there just to help de-mystify the situation. Its just one call that takes in a mapper, returns an engine. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session problems
Hi All Just incase anyone was wondering.. I found the cause of my session issues. (Hooray!) The Application is served by a home grown python HTTP server which works just fine - however, I found that it had an implementation of threading POOL to handle requests, rather than creating a new thread for each request. As it is a dedicated and task specific HTTP server, I had added some DB work inside the code that handles POST data (i.e. before it despatched the job to the waiting processes to deal with). I had forgotten that the POST was being handled by a thread that was not torn down at the end - and therefore the next POST request ran in the same context! Hence broken transactions on the MySQL side, and then the subsequent inability to serve further requests. Changed one line of code in the HTTP server to change it back to non pooled and it all worked perfectly :-) Thanks Michael to your invaluable insights into the workings of SA, as it was only a few minutes after reading your response that the answer was obvious. :-) Cheers Warwick On 7 October 2010 00:20, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote: I can't quite get my head around the scope of sessionmaker() yet.. I've tried putting Session = sessionmaker() as a global to the entire codebase (works but has same problem) do you mean scoped_session here ? sessionmaker is just a constructor for a new Session.It doesn't hold onto anything. scoped_session OTOH is nothing more than a thread local variable. Background on this concept is available here: http://docs.python.org/library/threading.html#threading.local .The remove() call removes the current thread local's context. If your app were single threaded, you could replace it with a single Session object, where you just call close() instead of remove() - it wouldn't be very different. Within the context of a multiprocess, single-threaded application, threading.local doesn't have any effect - there's just one thread. Importantly.. I can completely close all my processes and cold start my code - and this error continues until I restart mySQL ! i.e. NO session will work again, but I can do basic queries. What the?! I've seen this happen with PG when we are testing out two-phase transactions.You might want to ask on some MySQL forums what queries you might do to look at current lock/transaction state. BTW: It someone says You should be using scoped_session.. Please explain how to have either more than one session in the same context (I use three for a possible three different binds) yeah I actually have an app with a couple of scoped sessions, since there are two different databases and operations generally proceed with one or the other. or how to create one session that I can bind to more than one engine, and not have to know in advance all possible tables I might want to use on each engine.. If the table metadata is bound to an engine, then the session doesn't need to be bound. I.e. if tables A, B, C on metadata X are bound to engine P, tables D, E, F on metadata Y are bound to engine Q, you just use the Session, and it will handle the two engines as needed. If you really want total control, using some ruleset that's not quite as simple as table-metadata-engine, you can subclass Session and override get_bind(). I've never recommended that to anyone, but I put it out there just to help de-mystify the situation. Its just one call that takes in a mapper, returns an engine. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Session problems
Hi Michael I'm still having a couple of issues with the sessions, but I'm now starting to suspect mysqlconnector.. For completeness, could you please let me know if there is anything specific I need to do to close down a session / connection / engine etc if I want to completely release it. I see session.close(), but that appears to be more about committing etc, and I can't see anything specific I need to do to an engine etc. Basically, I just want to ensure that I'm doing everything correctly when I drop a session and kill the thread that it was in. Also, If I'm NOT creating lots of sessions for short periods of time (i.e. web services) (which I'm not) do I need to consider pools for any specific reason? They appear to be more about scaling that sort of situation. I'm basically creating a session and hanging on to it - doing lots of queries, updates etc with lots of commit/roll back, then dropping the session and exiting some time later -- Is my interpretation of session use correct? I'll play with other avenues of investigation before bringing the current session issues to the table. ;-) Cheers Warwick On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote: I can't quite get my head around the scope of sessionmaker() yet.. I've tried putting Session = sessionmaker() as a global to the entire codebase (works but has same problem) do you mean scoped_session here ? sessionmaker is just a constructor for a new Session.It doesn't hold onto anything. scoped_session OTOH is nothing more than a thread local variable. Background on this concept is available here: http://docs.python.org/library/threading.html#threading.local .The remove() call removes the current thread local's context. If your app were single threaded, you could replace it with a single Session object, where you just call close() instead of remove() - it wouldn't be very different. Within the context of a multiprocess, single-threaded application, threading.local doesn't have any effect - there's just one thread. Importantly.. I can completely close all my processes and cold start my code - and this error continues until I restart mySQL ! i.e. NO session will work again, but I can do basic queries. What the?! I've seen this happen with PG when we are testing out two-phase transactions. You might want to ask on some MySQL forums what queries you might do to look at current lock/transaction state. BTW: It someone says You should be using scoped_session.. Please explain how to have either more than one session in the same context (I use three for a possible three different binds) yeah I actually have an app with a couple of scoped sessions, since there are two different databases and operations generally proceed with one or the other. or how to create one session that I can bind to more than one engine, and not have to know in advance all possible tables I might want to use on each engine.. If the table metadata is bound to an engine, then the session doesn't need to be bound. I.e. if tables A, B, C on metadata X are bound to engine P, tables D, E, F on metadata Y are bound to engine Q, you just use the Session, and it will handle the two engines as needed. If you really want total control, using some ruleset that's not quite as simple as table-metadata-engine, you can subclass Session and override get_bind(). I've never recommended that to anyone, but I put it out there just to help de-mystify the situation. Its just one call that takes in a mapper, returns an engine. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
