Hi Michael, Thanks for the quick and detailed reply, but I still have some questions.
2011/11/6 Michael Bayer <mike...@zzzcomputing.com> > > On Nov 6, 2011, at 3:54 AM, AZ (Wei-Ning Huang) wrote: > > > Hi list, > > > > I've been getting ResoruceClosedError with MySQL server, which worked > > fine with SQLite. > > I'am using Celery queue, and those erros always happen within the > > celery tasks. > > For background on this message, there's a post just a day ago talking > about it (there, the issue seems to be occurring in conjunction with the > "update" lockmode: > https://groups.google.com/forum/#!topic/sqlalchemy/MBRcTJzi8Kk/discussion). > If the query executed is an UPDATE or similar, the statement returns > no results. The DBAPI will not provide a cursor.description and thus is > considered to have no rows to return. SQLAlchemy then closes it > automatically. > I'm using MySQL-python for mysql connection. my pop_timeout function actually look like this(I'm going to rewrite this after seeing you reply, but still want to know why): db.query(Table).filter(...).update({'stamp': stamp}) db.commit() results = db.query(Table).filter_by(stamp=stamp).all() # ResourceClosedError raised here and the traceback print out the error at this point. If I'm understanding correctly, you said that the cursor is closed since the update statement does not return any results. Since the cursor is closed so it rendered my next select statement invalid? So in theory if I can reconnect (create a new session) to the database, I can get the next select results without the ResourceClosedError, am I correct? > > If the statement emitted was truly a SELECT statement, and then > cursor.description is None as a result of that statement, it's likely a bug > in MySQL or MySQLdb you're coming across (these kinds of errors are very > common in the MySQL world). I'd suggest looking very closely at the > emitted SQL using echoing, and attempt to create the same SQL conversation > using a plain DBAPI cursor. > > Another thing to be careful of as I see you're talking about "multiple > workers" is to absolutely make sure that an individual MySQL connection is > never ever accessed by multiple threads. I'm not sure what mechanism you > have behind your get_by()/delete_by()/etc. mechanism there but hopefully > you're using a thread-scoped session, and ensuring that all objects > associated with that session are kept local to a single thread. > Thanks for the tip! I'll have to look into how Celery task worker connects to the database. > > When I do a jobqueue type of thing I tend to SELECT a bunch of rows > (ideally with the FOR UPDATE option so that they are locked), then > immediately UPDATE a status flag on those rows which changes it them from > "PENDING" to "QUEUED", thereby preventing concurrent workers from getting > at those rows - I now have their primary keys in memory so I can then > continue working with them. The rows are identified for UPDATE either via > "WHERE status='PENDING'" or based on the subset of PKs I have "WHERE id IN > (x, y, z)". I don't get into things like comparison of timestamps since > that approach can easily produce conflicts. > > The str() call on time() ( is that datetime.time() or time.time()?) seems > unnecessary. If the timestamp is actually the epoch, i.e. time.time(), > I'd probably not store that in the database directly, I'd instead go for a > full date/time value using a DATETIME or TIMESTAMP type (and I'd store it > in UTC). > > If concurrent workers are hitting pop_timeout() its likely that multiple > workers will have the same value for time(), so they'd be hitting the same > row in those cases. Perhaps the row is being deleted at the same moment > another worker is trying to SELECT it, and MySQL's sloppy handling of > things is giving you a broken cursor. Depends on if the error occurs all > the time or only under load. > Thanks for the tip, so I think I should use "with_lockmode('update') " and update the status of selected row, this is a much more nicer approach indeed :P Anyway, thanks so much for your reply! Best Regards, Wei-Ning Huang > > > > @celery.task > > def do_something(): > > records = SomeTable.pop_timeout() > > .... > > > > and for SomeTable.pop_timeout(), the pop_timeout method is as follows: > > > > class SomeTable(DeclarativeBase): > > ... > > def pop_timeout(klass): > > stamp = str(time()) > > now = time() > > klass.query(klass).filter((klass.transtamp == None) & (now - > > klass.timestamp > TIMEOUT)).update({'transtamp': stamp}) > > klass.commit() > > records = klass.get_by(transtamp=stamp, order_by=order_by, > > eager=eager) > > klass.delete_by(transtamp=stamp) > > klass.commit() > > return records > > > > > > > > > > > > And the tracebak is: > > caches = ImageCache.pop_timeout() > > File "/home/aitjcize/Work/shotwill/shotwill/backend/database.py", > > line 1081, i > > n pop_timeout > > now - ImageCache.timestamp >= app.config['IMAGE_CACHE_TIMEOUT'] > > File "/home/aitjcize/Work/shotwill/shotwill/backend/database.py", > > line 226, in > > pop > > records = klass.get_by(transtamp=stamp, order_by=order_by, > > eager=eager) > > File "/home/aitjcize/Work/shotwill/shotwill/backend/database.py", > > line 204, in > > get_by > > return query_object.all() > > File "/home/aitjcize/Work/shotwill/python/SQLAlchemy-0.7.2-py2.7- > > linux-x86_64. > > egg/sqlalchemy/orm/query.py", line 1729, in all > > return list(self) > > File "/home/aitjcize/Work/shotwill/python/SQLAlchemy-0.7.2-py2.7- > > linux-x86_64. > > egg/sqlalchemy/orm/query.py", line 1953, in instances > > fetch = cursor.fetchall() > > File "/home/aitjcize/Work/shotwill/python/SQLAlchemy-0.7.2-py2.7- > > linux-x86_64. > > egg/sqlalchemy/engine/base.py", line 2979, in fetchall > > l = self.process_rows(self._fetchall_impl()) > > File "/home/aitjcize/Work/shotwill/python/SQLAlchemy-0.7.2-py2.7- > > linux-x86_64. > > egg/sqlalchemy/engine/base.py", line 2948, in _fetchall_impl > > self._non_result() > > File "/home/aitjcize/Work/shotwill/python/SQLAlchemy-0.7.2-py2.7- > > linux-x86_64. > > egg/sqlalchemy/engine/base.py", line 2953, in _non_result > > "This result object does not return rows. " > > ResourceClosedError: This result object does not return rows. It has > > been closed > > automatically. > > > > Does anyone has any idea what is going on? It only happen when using > > mysql. > > And another thing, because there maybe multiple worker executing the > > same celery task > > at the same time, so the pop_timeout() function is what I came up > > with. Is there a better > > solution for popping records from a database? > > > > Thanks in advance. > > > > -- > > 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. > > > > -- > 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. > > -- AZ Huang <http://berelent.blogspot.com/> -- 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.
