Problem with 'fetchone'

[Tian JiaLin]

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!

[Mike Bayer]

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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Tian JiaLin]

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.

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@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

[Tian JiaLin]

Add some updates here. I found every time I got this problem, the affected rows is 18446744073709552000.