Thanks for the reply, Mike. Actually there is no obvious errors, furthermore with a lower percentage occurrences. That's why I feel this is pretty hard to debug.
And I did the similar thing like the snippets you provided to invalidate the broken connections. I'm not using any session variables in the code. I didn't try the NullPool implementation yet, because I think it should work like the "No Pool Version", which is working properly on my side. But I can try, maybe it will bring some clues. On Tue, Jul 19, 2016 at 1:47 AM, Mike Bayer <[email protected]> wrote: > > > On 07/18/2016 12:15 PM, Tian JiaLin wrote: > >> Hi Everyone, >> >> I have been using MySQL-Python for a long time. Recently I tried to >> integrated a connection pool which is based on SQLAlchemy, In terms of >> the legacy code, I'm using the raw_connection from the engine. >> >> Here is the sample code of two implementations: >> >> >> *No Pool Version:* >> >> * >> * >> >> connection = MySQLdb.connect(...) >> >> connection.autocommit(True) >> try: >> cursor = db.cursor() >> if not cursor.execute(sql, values) > 0: >> return None >> row = cursor.fetchone() >> finally: >> connection.close() >> return row[0] >> >> | >> | >> >> * >> * >> >> *Pool Version:* >> >> * >> * >> >> pool = create_engine("mysql+mysqldb://...") >> connection = pool.raw_connection() >> >> connection.autocommit(True) >> try: >> cursor = db.cursor() >> if not cursor.execute(sql, values) > 0: >> return None >> row = cursor.fetchone() >> finally: >> connection.close() >> return row[0] >> >> | >> | >> >> * >> *The codes look similar except the way to obtain the connection. After >> using the pool version, sometimes(not every time, actually in my >> situation, it occurs with 0.01% of all db queries), the return value >> of |execute| method is great than 0 and the |fetchone| method will >> return None. I guess it may related to the connection reuse, but I have >> no idea of which part is going wrong. This will be happened with any >> kind of SQL, I don't think it related to any specific one, but I can put >> some examples here. >> > > I assume by "db.cursor" you meant, "connection.cursor". > > Are there any critical exceptions being thrown, like deadlock errors, > disconnect errors, etc. for which the connection is not being invalidated? > SQLAlchemy's engine will invalidate the connection and the pool if we > encounter any of these error codes: > > if isinstance(e, (self.dbapi.OperationalError, > self.dbapi.ProgrammingError)): > return self._extract_error_code(e) in \ > (2006, 2013, 2014, 2045, 2055) > elif isinstance(e, self.dbapi.InterfaceError): > # if underlying connection is closed, > # this is the error you get > return "(0, '')" in str(e) > > when you use engine.raw_connection(), none of the above checking occurs. > If you get any of the above and continue using the connection, it may fail > to function properly afterwards. You would need to invalidate() that > connection (you can call this on the wrapper returned by raw_connection). > > Is there any use of SESSION level variables ? (e.g. SET SESSION). > > Using pool_class=NullPool resolves ? > > > > > > > > > >> >> SQL Examples: >> >> >> 1. SELECT uid FROM bookmarks WHERE object_id=?; >> >> 2. SELECT last_activity_time FROM categories WHERE uid=? LIMIT 1; >> >> >> Here is my server setups: >> >> >> Apache + mod_wsgi (hybrid multi-process multi-threaded) >> >> >> Pool Settings: >> >> >> pool_size: 3 >> >> max_overflow: 20 >> >> pool_reset_on_return: none (also tried rollback, but still got the >> errors) >> >> pool_recycle: 3600 >> >> >> MySQL: >> >> >> version 5.7.11 >> >> >> I'm using AWS RDS. Basically I'm using the default parameter groups from >> the RDS with some small changes like max_connections and sync_binlog. No >> sure which part is helpful to diagnose the problem. >> >> >> I have been working on this problem for one week without any >> progress. Does anyone have some ideas what gonna be the potential reason >> of this problem? >> >> >> Thanks! >> >> -- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send >> an email to [email protected] >> <mailto:[email protected]>. >> To post to this group, send email to [email protected] >> <mailto:[email protected]>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/T6EXkR96oU0/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- kenshin http://kenbeit.com Just Follow Your Heart -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
