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 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.
