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 <himurakenshi...@gmail.com>
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 <mike...@zzzcomputing.com>
> 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
>>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>>> To post to this group, send email to sqlalchemy@googlegroups.com
>>> <mailto: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, 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.
>>
>
>
>
> --
> kenshin
>
> http://kenbeit.com
> Just Follow Your Heart
>
--
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 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.
