[sqlalchemy] Re: invalid transaction rollback etc and making a join join
I removed SQLSoup and the caching/stale results issues are gone now. Have to wait a little longer to see if there is any bearing on the MySQL has gone away/invalid transaction thing. Changing the default beaker timeout to something small, like 15 secs, doesn't seem to fix things. Thanks for all the help everyone. On Sat, Oct 17, 2009 at 8:08 PM, cd34 wrote: > > On Oct 17, 6:49 pm, Jeff Cook wrote: >> Unfortunately, from a support-seeker point of view, such rhetoric is >> often necessary. How many times have you written a mailing list or hit > > sqlalchemy.pool_recycle = 10 > > I had odd issues -- even though mysql was set to > > | interactive_timeout | 60 | > | net_read_timeout | 30 | > | net_write_timeout | 60 | > | slave_net_timeout | 3600 | > | table_lock_wait_timeout | 50 | > | wait_timeout | 60 | > > A request that was valid and executed once before had problems when > pool_recycle was >= 30 with pylons. > > In project/model/meta.py > > I set > > Session = scoped_session(sessionmaker(autocommit=True)) > > which eliminated the situation where the transaction error required > restarting paster. Setting this is a bad thing, but then you can see > if the issue is within the sql and your translation to sqlalchemy, or, > whether it is something external. This prevents the inevitable pylons > 'hangup' when you get the sqlalchemy error reported in pylons and have > to restart the server to continue debugging. > > As for caching, you might doublecheck your beaker settings to turn it > off while testing. I haven't seen pylons excessively cache with their > default site template. > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
On Oct 17, 6:49 pm, Jeff Cook wrote: > Unfortunately, from a support-seeker point of view, such rhetoric is > often necessary. How many times have you written a mailing list or hit sqlalchemy.pool_recycle = 10 I had odd issues -- even though mysql was set to | interactive_timeout | 60| | net_read_timeout| 30| | net_write_timeout | 60| | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50| | wait_timeout| 60| A request that was valid and executed once before had problems when pool_recycle was >= 30 with pylons. In project/model/meta.py I set Session = scoped_session(sessionmaker(autocommit=True)) which eliminated the situation where the transaction error required restarting paster. Setting this is a bad thing, but then you can see if the issue is within the sql and your translation to sqlalchemy, or, whether it is something external. This prevents the inevitable pylons 'hangup' when you get the sqlalchemy error reported in pylons and have to restart the server to continue debugging. As for caching, you might doublecheck your beaker settings to turn it off while testing. I haven't seen pylons excessively cache with their default site template. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
Unfortunately, from a support-seeker point of view, such rhetoric is often necessary. How many times have you written a mailing list or hit up an IRC channel and had everyone write you off as incompetent just by default when they can't figure something out? It's important to establish that I _do_ know what's going on generally to avoid that kind of crap. It happens almost every time the instant solutions don't work. It seemed to me that guy was trying to infer that it's nothing involving SQLAlchemy when it obviously is. I obviously believe that SQLAlchemy is worth learning and that coming here could help correct any latent problems in my code, so please don't be offended, I didn't mean it a bad or offensive way. Thanks for your response, though. On Fri, Oct 16, 2009 at 5:57 PM, empty wrote: > > On Oct 15, 11:50 pm, Jeff Cook wrote: >> >> So, SQLAlchemy is doing something here. There probably is some >> incorrect code in my program, which is why I am writing this list, to >> figure out what that is. I'm an experienced developer and I don't >> appreciate your disrespect. I can follow tutorials and documentation >> and I did so here, and it's always ended up with lots of errors. Your >> docs need work. I'm sorry if this idea offends you. : ( I still like >> you and I still like SQLAlchemy. Let's cultivate an environment of >> mutual professional respect here. : ) >> > > I'm sorry but I have to jump in here. This sort of talk is completely > inappropriate. Mike Bayer spends day and night work on SQLAlchemy, and > as far as SQL toolkits in the Python world, and most other languages > for that matter, it is the best of the best. In addition to coding on > it he spends significant time and energy answering almost every > question on this list with thoughtful and helpful responses. This sort > of level of support is just completely unheard of. He does it for you, > me and everyone else that benefits from SQLAlchemy every day. Even in > the face of being ridiculed he continued to help you think through > things, because that's who Mike is. > > Regarding the docs, they are incredible. The problem often with the > docs is not that they aren't any good it is that a lot of people don't > have the terminology framework to understand this stuff. It takes time > to because there's so much there, but often folks are not willing to > spend that time. That's not a deficiency in the documentation, but in > people hoping to get a "quick fix". > > I understand being frustrated; it happens to us all. It's especially > frustrating when you are expected to embrace new technologies and be > up to speed and productive in no time. It's just a fact in our > industry. But do remember that the people on this list, those writing > the docs, those slugging through test cases, and those on IRC day and > night are doing it because they love technology, they love helping > folks, and they're committed to creating something great. I wish that > all open source projects were led by a "Mike Bayer". > > Michael > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
On Oct 15, 11:50 pm, Jeff Cook wrote: > > So, SQLAlchemy is doing something here. There probably is some > incorrect code in my program, which is why I am writing this list, to > figure out what that is. I'm an experienced developer and I don't > appreciate your disrespect. I can follow tutorials and documentation > and I did so here, and it's always ended up with lots of errors. Your > docs need work. I'm sorry if this idea offends you. : ( I still like > you and I still like SQLAlchemy. Let's cultivate an environment of > mutual professional respect here. : ) > I'm sorry but I have to jump in here. This sort of talk is completely inappropriate. Mike Bayer spends day and night work on SQLAlchemy, and as far as SQL toolkits in the Python world, and most other languages for that matter, it is the best of the best. In addition to coding on it he spends significant time and energy answering almost every question on this list with thoughtful and helpful responses. This sort of level of support is just completely unheard of. He does it for you, me and everyone else that benefits from SQLAlchemy every day. Even in the face of being ridiculed he continued to help you think through things, because that's who Mike is. Regarding the docs, they are incredible. The problem often with the docs is not that they aren't any good it is that a lot of people don't have the terminology framework to understand this stuff. It takes time to because there's so much there, but often folks are not willing to spend that time. That's not a deficiency in the documentation, but in people hoping to get a "quick fix". I understand being frustrated; it happens to us all. It's especially frustrating when you are expected to embrace new technologies and be up to speed and productive in no time. It's just a fact in our industry. But do remember that the people on this list, those writing the docs, those slugging through test cases, and those on IRC day and night are doing it because they love technology, they love helping folks, and they're committed to creating something great. I wish that all open source projects were led by a "Mike Bayer". Michael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
Thanks for the help. I wasn't assigning the session to SQLSoup's session, but I am now, and I'm still getting stale data and I don't know yet if I'll still be getting the invalid transaction/MySQL has gone away thing. I think I'll try just taking SQLSoup out of the thing all together and try SQLAlchemy's reflection. I'll let you know if it fixes things. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
On Oct 15, 2009, at 11:50 PM, Jeff Cook wrote: > > So, SQLAlchemy is doing something here. There probably is some > incorrect code in my program, which is why I am writing this list, to > figure out what that is. I'm an experienced developer and I don't > appreciate your disrespect. I can follow tutorials and documentation > and I did so here, and it's always ended up with lots of errors. Your > docs need work. I'm sorry if this idea offends you. : ( I've made no implications that you haven't read documentation and everything I've written has consisted of advice and help towards your problem, so I am sorry you feel that way. On the other hand every email you've written here is prefaced with (if not entirely consisted of) some kind of dig about how SQLAlchemy is an inadequate tool. Its a funny way to get people to help you. I also happen to think our docs are great and most people will agree with me on that. I've stated that your problem is caused by a very specific pattern, and I'm asking you to look for this pattern in your code, rather than attempting to find flags and more boilerplate (autoflush, pool_recycle, more calls to close()) that would fix it - the issue can't be fixed without isolating why it is occurring. That is all which is implied by "buggy", it is not any reflection on code quality. After all I haven't seen your code, only its bugs. As for SQLSoup, that extension, while handy and clever, was not written by me and is not very well maintained, and I have considered moving it out to examples/ only because I do not have time to write new docs/unit tests for it. I do not use it personally so I am not generally aware of any gotchas. But let me note that the extension itself is barely 100 lines of code - its well worth taking a stroll through its source. Which leads me to the only advice I can offer you, which is that if you look at SQLSoup, almost the very first line of actual code it sets up its own ScopedSession. If you're running in Pylons, you should be replacing that ScopedSession with the one that Pylons sets up (meaning, sqlsoup.session = meta.session). So at least any queries you making using SqlSoup are on the same Session as the one which Pylons is maintaining and (assumedly) properly closing out after each request.That is a plausible cause of stale data from previous requests as well as stale transactional state causing your error (though it would need a previous error to start off). I'm very much hoping that's your issue here, so good luck. > > Love > Jeff > > On Thu, Oct 15, 2009 at 6:13 PM, Michael Bayer > wrote: >> >> >> On Oct 15, 2009, at 3:32 PM, Jeff Cook wrote: >> >>> >>> And when things _do_ work, there are serious caching problems. >>> Sometimes it gives me the transaction rollback error, sometimes it >>> gives me an old version of the page, and sometimes it gives me a >>> current version of the page. I assume this has something to do with >>> what connection is getting used. How can I remove these problems? >>> Would pool_recycle be of any use? >> >> there's no flag that is going to make your program work. your app is >> buggy, plain and simple. You need to do a code review, ensure no >> exceptions are being squashed, do some ab testing, and watch your >> logs. its likely theres just one activity in your app, maybe two, >> screwing everything up. the longer you just try to guess the cause >> and try random things the more confused things will seem.SQLSoup >> doesn't have anything to do with connection pools or transactions, >> its >> basically a thin layer over creating mappers and Query objects. It >> uses the same Session as everyone else, using the threaded >> ScopedSession to keep things local. And a default setup of Pylons >> does catch errors and roll the session back. There's also little to >> no "caching" used by the session and certainly not across requests >> unless you've worked very hard to store everything in global >> variables >> across requests. >> >> >> >> >> >>> >>> On Thu, Oct 15, 2009 at 1:27 PM, Jeff Cook >>> wrote: I see. So Pylons should handle this by default, but it's not doing so? That's highly disappointing. Clearly, something is quite incorrect here. Is my usage of SQLSoup causing rollback not to run? On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer wrote: > > Jeff Cook wrote: >> I don't fully understand what you're talking about. I have this >> error >> and I need to make it stop. I just want SQLAlchemy to connect, >> run the >> query I instructed, and give me the results back and do this >> reliably >> without necessitating consistent server restarts. Thus far, it's >> been >> a serious pain managing connection errors and so on. SQLSoup may >> complicate this because they have no mention anywhere in their >> docs >> explaining the necessi
[sqlalchemy] Re: invalid transaction rollback etc and making a join join
OK, man, well, I have one function that has the calls to perform the listing. I refresh and sometimes get old data and sometimes don't. There is no clustering and no other databases, there is no possibility that the server is retrieving old data. I haven't changed the base Pylons classes at all, so, according to you, I shouldn't be getting that error at all. But I am. How would you advise me to continue? Scorch earth and start over? That's pretty much the same; there's not much SQLAlchemy code in use right now, it's only a few queries ... yet we're still having these issues. So, SQLAlchemy is doing something here. There probably is some incorrect code in my program, which is why I am writing this list, to figure out what that is. I'm an experienced developer and I don't appreciate your disrespect. I can follow tutorials and documentation and I did so here, and it's always ended up with lots of errors. Your docs need work. I'm sorry if this idea offends you. : ( I still like you and I still like SQLAlchemy. Let's cultivate an environment of mutual professional respect here. : ) Love Jeff On Thu, Oct 15, 2009 at 6:13 PM, Michael Bayer wrote: > > > On Oct 15, 2009, at 3:32 PM, Jeff Cook wrote: > >> >> And when things _do_ work, there are serious caching problems. >> Sometimes it gives me the transaction rollback error, sometimes it >> gives me an old version of the page, and sometimes it gives me a >> current version of the page. I assume this has something to do with >> what connection is getting used. How can I remove these problems? >> Would pool_recycle be of any use? > > there's no flag that is going to make your program work. your app is > buggy, plain and simple. You need to do a code review, ensure no > exceptions are being squashed, do some ab testing, and watch your > logs. its likely theres just one activity in your app, maybe two, > screwing everything up. the longer you just try to guess the cause > and try random things the more confused things will seem. SQLSoup > doesn't have anything to do with connection pools or transactions, its > basically a thin layer over creating mappers and Query objects. It > uses the same Session as everyone else, using the threaded > ScopedSession to keep things local. And a default setup of Pylons > does catch errors and roll the session back. There's also little to > no "caching" used by the session and certainly not across requests > unless you've worked very hard to store everything in global variables > across requests. > > > > > >> >> On Thu, Oct 15, 2009 at 1:27 PM, Jeff Cook >> wrote: >>> I see. So Pylons should handle this by default, but it's not doing >>> so? >>> That's highly disappointing. Clearly, something is quite incorrect >>> here. Is my usage of SQLSoup causing rollback not to run? >>> >>> On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer >> > wrote: Jeff Cook wrote: > I don't fully understand what you're talking about. I have this > error > and I need to make it stop. I just want SQLAlchemy to connect, > run the > query I instructed, and give me the results back and do this > reliably > without necessitating consistent server restarts. Thus far, it's > been > a serious pain managing connection errors and so on. SQLSoup may > complicate this because they have no mention anywhere in their docs > explaining the necessity to close your connections, and all > methods I > tried (explicit session.close()s at the end of each query, explicit > db.close()s, and now autoflush=True) to make sure that the > resources > are being returned correctly to the pooler have failed and caused > other blow-up problem attacks. none of the statements regarding SQLA in that paragraph are accurate. close() is not needed, autoflush=True is the default setting (did you mean autocommit? that's a feature better left off), SQLAlchemy always returns resources to their original pooled state when a transaction is not in progress. What is necessary, however, is that you must call rollback() when an exception is raised. I see you're using Pylons, the default Pylons template establishes this pattern within the BaseController. unfortunately there is no feature within SQLAlchemy that can "fix" your issue. Your application needs to get a handle on transaction failures. A transaction is only "invalid" if an error were already raised in a previous operation within the same transaction, and you haven't attached any stack trace for that. >> >>> >> >> > > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroup
[sqlalchemy] Re: invalid transaction rollback etc and making a join join
On Oct 15, 2009, at 3:32 PM, Jeff Cook wrote: > > And when things _do_ work, there are serious caching problems. > Sometimes it gives me the transaction rollback error, sometimes it > gives me an old version of the page, and sometimes it gives me a > current version of the page. I assume this has something to do with > what connection is getting used. How can I remove these problems? > Would pool_recycle be of any use? there's no flag that is going to make your program work. your app is buggy, plain and simple. You need to do a code review, ensure no exceptions are being squashed, do some ab testing, and watch your logs. its likely theres just one activity in your app, maybe two, screwing everything up. the longer you just try to guess the cause and try random things the more confused things will seem.SQLSoup doesn't have anything to do with connection pools or transactions, its basically a thin layer over creating mappers and Query objects. It uses the same Session as everyone else, using the threaded ScopedSession to keep things local. And a default setup of Pylons does catch errors and roll the session back. There's also little to no "caching" used by the session and certainly not across requests unless you've worked very hard to store everything in global variables across requests. > > On Thu, Oct 15, 2009 at 1:27 PM, Jeff Cook > wrote: >> I see. So Pylons should handle this by default, but it's not doing >> so? >> That's highly disappointing. Clearly, something is quite incorrect >> here. Is my usage of SQLSoup causing rollback not to run? >> >> On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer > > wrote: >>> >>> Jeff Cook wrote: I don't fully understand what you're talking about. I have this error and I need to make it stop. I just want SQLAlchemy to connect, run the query I instructed, and give me the results back and do this reliably without necessitating consistent server restarts. Thus far, it's been a serious pain managing connection errors and so on. SQLSoup may complicate this because they have no mention anywhere in their docs explaining the necessity to close your connections, and all methods I tried (explicit session.close()s at the end of each query, explicit db.close()s, and now autoflush=True) to make sure that the resources are being returned correctly to the pooler have failed and caused other blow-up problem attacks. >>> >>> none of the statements regarding SQLA in that paragraph are >>> accurate. >>> close() is not needed, autoflush=True is the default setting (did >>> you mean >>> autocommit? that's a feature better left off), SQLAlchemy always >>> returns >>> resources to their original pooled state when a transaction is not >>> in >>> progress. >>> >>> What is necessary, however, is that you must call rollback() when an >>> exception is raised. I see you're using Pylons, the default Pylons >>> template establishes this pattern within the BaseController. >>> >>> unfortunately there is no feature within SQLAlchemy that can "fix" >>> your >>> issue. Your application needs to get a handle on transaction >>> failures. A >>> transaction is only "invalid" if an error were already raised in a >>> previous operation within the same transaction, and you haven't >>> attached >>> any stack trace for that. >>> >>> >>> > >>> >> > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
And when things _do_ work, there are serious caching problems. Sometimes it gives me the transaction rollback error, sometimes it gives me an old version of the page, and sometimes it gives me a current version of the page. I assume this has something to do with what connection is getting used. How can I remove these problems? Would pool_recycle be of any use? On Thu, Oct 15, 2009 at 1:27 PM, Jeff Cook wrote: > I see. So Pylons should handle this by default, but it's not doing so? > That's highly disappointing. Clearly, something is quite incorrect > here. Is my usage of SQLSoup causing rollback not to run? > > On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer > wrote: >> >> Jeff Cook wrote: >>> I don't fully understand what you're talking about. I have this error >>> and I need to make it stop. I just want SQLAlchemy to connect, run the >>> query I instructed, and give me the results back and do this reliably >>> without necessitating consistent server restarts. Thus far, it's been >>> a serious pain managing connection errors and so on. SQLSoup may >>> complicate this because they have no mention anywhere in their docs >>> explaining the necessity to close your connections, and all methods I >>> tried (explicit session.close()s at the end of each query, explicit >>> db.close()s, and now autoflush=True) to make sure that the resources >>> are being returned correctly to the pooler have failed and caused >>> other blow-up problem attacks. >> >> none of the statements regarding SQLA in that paragraph are accurate. >> close() is not needed, autoflush=True is the default setting (did you mean >> autocommit? that's a feature better left off), SQLAlchemy always returns >> resources to their original pooled state when a transaction is not in >> progress. >> >> What is necessary, however, is that you must call rollback() when an >> exception is raised. I see you're using Pylons, the default Pylons >> template establishes this pattern within the BaseController. >> >> unfortunately there is no feature within SQLAlchemy that can "fix" your >> issue. Your application needs to get a handle on transaction failures. A >> transaction is only "invalid" if an error were already raised in a >> previous operation within the same transaction, and you haven't attached >> any stack trace for that. >> >> >> >> >> >> > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
I see. So Pylons should handle this by default, but it's not doing so? That's highly disappointing. Clearly, something is quite incorrect here. Is my usage of SQLSoup causing rollback not to run? On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer wrote: > > Jeff Cook wrote: >> I don't fully understand what you're talking about. I have this error >> and I need to make it stop. I just want SQLAlchemy to connect, run the >> query I instructed, and give me the results back and do this reliably >> without necessitating consistent server restarts. Thus far, it's been >> a serious pain managing connection errors and so on. SQLSoup may >> complicate this because they have no mention anywhere in their docs >> explaining the necessity to close your connections, and all methods I >> tried (explicit session.close()s at the end of each query, explicit >> db.close()s, and now autoflush=True) to make sure that the resources >> are being returned correctly to the pooler have failed and caused >> other blow-up problem attacks. > > none of the statements regarding SQLA in that paragraph are accurate. > close() is not needed, autoflush=True is the default setting (did you mean > autocommit? that's a feature better left off), SQLAlchemy always returns > resources to their original pooled state when a transaction is not in > progress. > > What is necessary, however, is that you must call rollback() when an > exception is raised. I see you're using Pylons, the default Pylons > template establishes this pattern within the BaseController. > > unfortunately there is no feature within SQLAlchemy that can "fix" your > issue. Your application needs to get a handle on transaction failures. A > transaction is only "invalid" if an error were already raised in a > previous operation within the same transaction, and you haven't attached > any stack trace for that. > > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
Jeff Cook wrote: > I don't fully understand what you're talking about. I have this error > and I need to make it stop. I just want SQLAlchemy to connect, run the > query I instructed, and give me the results back and do this reliably > without necessitating consistent server restarts. Thus far, it's been > a serious pain managing connection errors and so on. SQLSoup may > complicate this because they have no mention anywhere in their docs > explaining the necessity to close your connections, and all methods I > tried (explicit session.close()s at the end of each query, explicit > db.close()s, and now autoflush=True) to make sure that the resources > are being returned correctly to the pooler have failed and caused > other blow-up problem attacks. none of the statements regarding SQLA in that paragraph are accurate. close() is not needed, autoflush=True is the default setting (did you mean autocommit? that's a feature better left off), SQLAlchemy always returns resources to their original pooled state when a transaction is not in progress. What is necessary, however, is that you must call rollback() when an exception is raised. I see you're using Pylons, the default Pylons template establishes this pattern within the BaseController. unfortunately there is no feature within SQLAlchemy that can "fix" your issue. Your application needs to get a handle on transaction failures. A transaction is only "invalid" if an error were already raised in a previous operation within the same transaction, and you haven't attached any stack trace for that. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Re: invalid transaction rollback etc and making a join join
It's not faster. I use pyscopg2 directly on some other projects and
it's definitely a lot faster to just be able to write the query I want
than to try to think of how to convert it to SQLAlchemy's contexts and
functions. Maybe it's just learning curve thing, but as shown, I can't
get that join to actually work, and I was trying different things for
hours; it gives me silly errors of every which-a-kind. If I can just
write something as simple as that in under five minutes, why does it
take so long for SQLAlchemy to let me do it? There's definitely a
problem somewhere in there.
File
'/home/jeff/vspy/lib/python2.6/site-packages/WebError-0.10.1-py2.6.egg/weberror/evalexception.py',
line 431 in respond
app_iter = self.application(environ, detect_start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Beaker-1.4.1-py2.6.egg/beaker/middleware.py',
line 73 in __call__
return self.app(environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Beaker-1.4.1-py2.6.egg/beaker/middleware.py',
line 152 in __call__
return self.wrap_app(environ, session_start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Routes-1.10.3-py2.6.egg/routes/middleware.py',
line 130 in __call__
response = self.app(environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/wsgiapp.py',
line 125 in __call__
response = self.dispatch(controller, environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/wsgiapp.py',
line 324 in dispatch
return controller(environ, start_response)
File '/home/jeff/projecs/projecs/lib/base.py', line 18 in __call__
return WSGIController.__call__(self, environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 221 in __call__
response = self._dispatch_call()
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 172 in _dispatch_call
response = self._inspect_call(func)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 107 in _inspect_call
result = self._perform_call(func, args)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 60 in _perform_call
return func(**args)
File '/home/jeff/projecs/projecs/controllers/viewer.py', line 18 in index
c.assets = assets.list_assets()
File '/home/jeff/projecs/projecs/model/assets.py', line 7 in list_assets
return db.join(db.assets, db.asset_storage_schemes,
db.assets.asset_storage_scheme_id==db.asset_storage_schemes.asset_store_id).order_by(db.assets.asset_id.desc()).all()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1267 in all
return list(self)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1361 in __iter__
return self._execute_and_instances(context)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1364 in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/session.py',
line 755 in execute
clause, params or {})
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 824 in execute
return Connection.executors[c](self, object, multiparams, params)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 872 in _execute_clauseelement
parameters=params
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 938 in __create_execution_context
return dialect.execution_ctx_cls(dialect, connection=self, **kwargs)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/default.py',
line 170 in __init__
self.cursor = self.create_cursor()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/default.py',
line 258 in create_cursor
return self._connection.connection.cursor()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 576 in connection
raise exc.InvalidRequestError("Can't reconnect until invalid
transaction is rolled back")
InvalidRequestError: Can't reconnect until invalid transaction is rolled back
I don't fully understand what you're talking about. I have this error
and I need to make it stop. I just want SQLAlchemy to connect, run the
query I instructed, and give me the results back and do this reliably
without necessitating consistent server restarts. Thus far, it's been
a serious pain managing connection error
[sqlalchemy] Re: invalid transaction rollback etc and making a join join
Jeff Cook wrote:
>
> Dear All People:
>
> I'm using SQLSoup and getting errors like InvalidRequestError: Can't
> reconnect until invalid transaction is rolled back and MySQL Server
> has gone away. I have set autoflush=True and this has helped mitigate
> some errors, but not these, they happen with varying frequency when I
> haven't restarted the servers for a while. I am almost never using
> db.flush(). Will that fix this? How can I make this things work? I
> really want them to work and I hate MySQL but am stuck using it for
> now.
>
> SQLAlchemy thus far has added several hours to development time, which
> I find sad. I have this block of code:
> #this has consumed too much time
> #will come back and fix but for now we have to give up on it
> #ret = Session.query(sets).select_from(orm.outerjoin((slices,
> slices.asset_slice_asset_id == id), (stores, stores.asset_store_id ==
> slices.asset_slice_store_scheme_id))).filter(id == sets.asset_id).all
> ()
> #how I hate sqlalchemy right now
> #the raw query took under five minutes
> #the other thing took more than hour and still doesn't work
> ret = db.bind.execute(""" SELECT * FROM assets a
> LEFT OUTER JOIN asset_slices `as` on as.asset_slice_asset_id =
> a.asset_id
> LEFT OUTER JOIN asset_storage_schemes `astor` ON
> astor.asset_store_id = as.asset_slice_store_scheme_id
> WHERE a.asset_id = {bid}
> ORDER BY asset_slice_row ASC""".format(bid = id))
>
> So I need that actual query to be executable via SQLAlchemy,
> preferably with SQLSoup. It doesn't work right now.
>
These errors suggest operating within a transaction where an error has
already occurred, and was swallowed. It's a general Python practice to
allow exceptions to propagate and be reported as a general failure at some
level where they are explicitly handled and at the very least logged. If
you follow this procedure, and look for these errors occurring, you likely
won't have mysterious exceptions complaining about the invalid state of a
previous operation. There is also no way for anyone to help if you do
not attach the full stack traces to your emails.
I would also challenge your assertion that SQLAlchemy is adding hours to
your development time - unless your application is extremely trivial, it
is saving you dozens versus using raw DBAPI.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
