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