Re: [sqlalchemy] Problem with 'fetchone'
Add some updates here. I found every time I got this problem, the affected
rows is 18446744073709552000.
On Tue, Jul 19, 2016 at 2:13 AM, Tian JiaLin
wrote:
> 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
> 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 sqlalchemy+unsubscr...@googlegroups.com
>>> .
>>> To post to this group, send email to sqlalchemy@googlegroups.com
>>> .
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> Fo
Re: [sqlalchemy] Problem with 'fetchone'
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
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 sqlalchemy+unsubscr...@googlegroups.com
>> .
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> .
>> 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, sen
Re: [sqlalchemy] Problem with 'fetchone'
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 sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
