release 0.7.11 soon? (with 0e01158fb8a204144c5adddde983bea2b3e4ff93)

[Rich Fromm]

I encountered the following stack trace today, using PyMySQL with an AWS/RDS/Aurora backend (which is supposed to be mysql compatible, see https://aws.amazon.com/rds/aurora/details/):

2017-04-05T00:22:26.755657477Z Wed Apr  5 00:22:26 2017 - SIGPIPE: writing to a closed pipe/socket/fd (probably the client disconnected) on request /api/v0/scheduleChecks?groupId=aid-2197 (ip 10.45.64.4) !!!
2017-04-05T00:22:26.757489074Z 2017-04-05 00:22:26,755 sqlalchemy.pool.QueuePool ERROR Exception closing connection <pymysql.connections.Connection object at 0x7ff46df10810>
2017-04-05T00:22:26.757502478Z Traceback (most recent call last):
2017-04-05T00:22:26.757506638Z   File "/usr/lib/python2.7/site-packages/sqlalchemy/pool.py", line 300, in _close_connection
2017-04-05T00:22:26.757510272Z     self._dialect.do_close(connection)
2017-04-05T00:22:26.757513403Z   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 446, in do_close
2017-04-05T00:22:26.757516995Z     dbapi_connection.close()
2017-04-05T00:22:26.757520099Z   File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 734, in close
2017-04-05T00:22:26.757523092Z     sock.close()
2017-04-05T00:22:26.757526501Z AttributeError: 'NoneType' object has no attribute 'close' {"asctime": "2017-04-05 00:22:26,755"}

Fwiw, while this is not directly relevant to my problem, I do not see this behavior pointing at a docker container using the image mysql:5.7

Anyway, investigating further, I saw what looked like a bug in close() in pymysql/connections.py. I was going to try a fix, and open an issue and pull request if it worked. However, looking further, I see that someone already fixed the issue (in a slightly different way), in a recent commit that, along with 3 others, is more recent than the 0.7.10 release:

$ git show 0e01158fb8a204144c5adddde983bea2b3e4ff93
commit 0e01158fb8a204144c5adddde983bea2b3e4ff93
Author: Wybe van der Ham <wybe@snth.eu>
Date:   Thu Mar 2 13:33:28 2017 +0100
 
    fix calling None.close()
 
diff --git a/pymysql/connections.py b/pymysql/connections.py
index 08475a5..31dd85a 100644
--- a/pymysql/connections.py
+++ b/pymysql/connections.py
@@ -735,10 +735,7 @@ class Connection(object):
         except Exception:
             pass
         finally:
-            sock = self._sock
-            self._sock = None
-            self._rfile = None
-            sock.close()
+            self._force_close()
 
     @property
     def open(self):

As far as I can tell, there was no issue nor any pull request associated with this change.

Are there any plans to release 0.7.11 soon? I really need this fixed, and it does not appear that unreleased dev builds are being uploaded to pypi (see https://pypi.python.org/simple/pymysql/).

If I have to, I could build from the HEAD of master myself, and use that, but it would be nice (and far easier) if I could point to an official build.

Thanks for considering this request.

[Naoki INADA]

OK.

But this issue reveals your configuration issues too.

This only happens when _write_bytes().  This issue won't happen on sound system.

Maybe, you keep connection too long and server (or OS, router, etc...) killed the connection.

[David Multer]

I have a similar issue with one of my Flask/gunicorn/RDS containerized services. I only see the issue with one of a dozen of these services, and then only about once a day that hits all my gunicorn workers at once. This is not a configuration issue on the server, and it is not an issue with client connections. This close() issue is real in PyMySQL and I'm in need of a release with this fix.

[Naoki INADA]

On Thursday, April 6, 2017 at 12:17:19 AM UTC+9, David Multer wrote:

I have a similar issue with one of my Flask/gunicorn/RDS containerized services. I only see the issue with one of a dozen of these services, and then only about once a day that hits all my gunicorn workers at once. This is not a configuration issue on the server, and it is not an issue with client connections. This close() issue is real in PyMySQL and I'm in need of a release with this fix.

 >  This is not a configuration issue

Why did you think so, even when it only happens rarely?

Actually speaking, this issue only happens **after** failed to send COM_CLOSE packet.

> This close() issue is real in PyMySQL

Yes.  And this is error handling issue.  The error will be caused by configuration.

[Rich Fromm]

On Wednesday, April 5, 2017 at 10:58:37 AM UTC-7, Naoki INADA wrote:

On Thursday, April 6, 2017 at 12:17:19 AM UTC+9, David Multer wrote:

 >  This is not a configuration issue

Why did you think so, even when it only happens rarely?

> This close() issue is real in PyMySQL

Yes.  And this is error handling issue.  The error will be caused by configuration.

For me it's not rare. It's happening on every write. (Admittedly, testing has been very limited, but that's partly b/c I can't write to the DB.)

Do you have any thoughts about what kind of configuration might cause this? To be clear I do get the error when the backend is AWS/RDS/Aurora. I do not get the error when the backend is a mysql:5.7 docker container. This is my first time using RDS, so I suppose there might be something misconfigured, but I don't know what that might be.

Regarding client configuration, my code is abstracted way higher than the actual db writes. I'm using SQLAlchemy 1.1.5 and Flask-SQLAlchemy 2.1. I have not tried to recreate the issue with lower level operations.

[INADA Naoki]

On Thu, Apr 6, 2017 at 3:14 AM, Rich Fromm <rich....@gmail.com> wrote:
> On Wednesday, April 5, 2017 at 10:58:37 AM UTC-7, Naoki INADA wrote:
>>
>>
>> On Thursday, April 6, 2017 at 12:17:19 AM UTC+9, David Multer wrote:
>> > This is not a configuration issue
>>
>> Why did you think so, even when it only happens rarely?
>
>
>> > This close() issue is real in PyMySQL
>>
>> Yes. And this is error handling issue. The error will be caused by
>> configuration.
>
>
> For me it's not rare. It's happening on every write. (Admittedly, testing
> has been very limited, but that's partly b/c I can't write to the DB.)
>
> Do you have any thoughts about what kind of configuration might cause this?
> To be clear I do get the error when the backend is AWS/RDS/Aurora. I do not
> get the error when the backend is a mysql:5.7 docker container. This is my
> first time using RDS, so I suppose there might be something misconfigured,
> but I don't know what that might be.

There are may way to break it, and very limited way to configure properly.
In other words, I don't know what's your case.
But very common mistakes are:

* Using multithreading, wihtout knowing how it works.
* easy way to fix: using only process worker. don't create any
connection before forking.

* Using connection pooling without house keeping (closing connection when idle).
* easy way to fix: Closing pool at the end of each request.
Connections can be reused
while processing one HTTP request, but it won't reused among may requests.
It's efficient enough for most cases

Connection pooling is hard to configure right way.
Long living connection is root of evil.
See also: https://github.com/go-sql-driver/mysql/issues/529#issuecomment-290032952

I've tired to describe it again and again.
Writing English is much harder to me than writing Python or Go....

>
> Regarding client configuration, my code is abstracted way higher than the
> actual db writes. I'm using SQLAlchemy 1.1.5 and Flask-SQLAlchemy 2.1. I
> have not tried to recreate the issue with lower level operations.
>

In SQLAlchemy, you can close pool by:

session.get_bind().dispose()

You can call it from gunicorn:
http://docs.gunicorn.org/en/latest/settings.html#post-request


I read manual of SQLAlchemy and guncorn for you, while I'm not free
tech support.
I don't want to pay my time and energy for this type of question.
It leads me to burn-out. I hope some volunteer reply to this type of
question...
(see also: https://medium.com/@methane/why-you-must-not-ask-questions-on-github-issues-51d741d83fde)

[Naoki INADA]

BTW, I released 0.7.11 to PyPI.

You won't see any error even when failed to send COM_CLOSE.

[Rich Fromm]

On Wednesday, April 5, 2017 at 11:50:36 AM UTC-7, Naoki INADA wrote:

There are may way to break it, and very limited way to configure properly.
In other words, I don't know what's your case.

My usage pattern is fairly simple. A request comes in, and I do one or more operations like:
* Session.query()
* Session.add()
* Session.delete()

Then, when done, I call Session.commit()
 

But very common mistakes are:

* Using multithreading, wihtout knowing how it works.
  * easy way to fix: using only process worker.  don't create any
connection before forking.

* Using connection pooling without house keeping (closing connection when idle).
  * easy way to fix: Closing pool at the end of each request.
Connections can be reused
     while processing one HTTP request,  but it won't reused among may requests.
     It's efficient enough for most cases

I'm not explicitly choosing to do or not do any of that. As a first pass, I am interacting with the database in the way the frameworks I am using (SQLAlchemy and Flask-SQLAlchemy) recommend, and using their defaults. I understand that it's possible those are buggy, don't conform to best practices, and/or don't agree with your opinions.

As far as I can tell, I am following the guidelines shown here:
* http://docs.sqlalchemy.org/en/latest/orm/session_basics.html
* http://flask-sqlalchemy.pocoo.org/2.1/queries/

Connection pooling is hard to configure right way.
Long living connection is root of evil.
See also:  https://github.com/go-sql-driver/mysql/issues/529#issuecomment-290032952 

In SQLAlchemy, you can close pool by:

  session.get_bind().dispose()

As far as I can tell, that is not what SQLAlchemy recommends. From http://docs.sqlalchemy.org/en/latest/core/connections.html#engine-disposal

The Engine is intended to normally be a permanent fixture established up-front and maintained throughout the lifespan of an application. It is not intended to be created and disposed on a per-connection basis; it is instead a registry that maintains both a pool of connections as well as configurational information about the database and DBAPI in use, as well as some degree of internal caching of per-database resources.

 

You can call it from gunicorn:
http://docs.gunicorn.org/en/latest/settings.html#post-request


I read manual of SQLAlchemy and guncorn for you, while I'm not free
tech support.

If you don't want to give any more help, that's fine. The above information is provided also for anyone else reading this thread.

I have read what I thought were the relevant parts of the SQLAlchemy manual.

I had never heard of gunicorn before your post. For the record, I am installing the latest uwsgi packages for Alpine linux via:
apk add uwsgi uwsgi-python

 

I don't want to pay my time and energy for this type of question.
It leads me to burn-out.  I hope some volunteer reply to this type of
question...

Then don't reply any more. That's fine.

(see also: https://medium.com/@methane/why-you-must-not-ask-questions-on-github-issues-51d741d83fde)

I thought the point you are making there is not to ask questions in an issue tracker, use a forum instead. I was under the impression that this *was* the forum for such questions about PyMySQL.

[Naoki INADA]

> As far as I can tell, I am following the guidelines shown here:> * http://docs.sqlalchemy.org/en/latest/orm/session_basics.html

> * http://flask-sqlalchemy.pocoo.org/2.1/queriIes/

Who talk about session?

http://flask-sqlalchemy.pocoo.org/2.1/config/#configuration-keys
Flask-SQLAlchemy uses `SQLALCHEMY_POOL_RECYCLE` configuration option.

pool_recycle option works only when checking connection out from pool.

So it doesn't prevent very old connection is kept when application is idle.
That's why I recommend closing pool after each request.

But if you don't care about it, you can set small pool_recyle (e.g. 600 (= 10min)).

Only COM_CLOSE sent after the timeout.  So PyMySQL 0.7.11 fixes all problem.

I feel default SQLALCHEMY_POOL_RECYCLE (2h) is too large.

Some netowrk (router, iptalbles, etc...) drops idle connection.  The idle timeout of network

can be very small (e.g. 1 hour) in cloud environment.  So I recommend at most 30min (1800).

But smaller is safer, and 3min (180) is efficient enough for most environment.

> As far as I can tell, that is not what SQLAlchemy recommends. From http://docs.sqlalchemy.org/en/latest/core/connections.html#engine-disposal
>
>> The Engine is intended to normally be a permanent fixture established up-front and maintained throughout the lifespan of an application. It is not intended to be created and disposed on a per-connection basis; it is instead a registry that maintains both a pool of connections as well as configurational information about the database and DBAPI in use, as well as some degree of internal caching of per-database resources.

You're comparing apple and orange.

SQLAlchemy recommends not dispose Engine per each "database" connection.
I recommended dispose Engine per each "HTTP request".

> I had never heard of gunicorn before your post. For the record, I am installing the latest uwsgi packages for Alpine linux via:
apk add uwsgi uwsgi-python

I used gunicorn by example because David said using it.
uwsgi has nice options which can be used closing entire worker including DB connection

when application is idle.  (e.g. `max-worker-lifetime`, `cheaper-idle`)

> Then don't reply any more. That's fine.

If you think so, you shouldn't reply comment refusing my comment like above.

> I thought the point you are making there is not to ask questions in an issue tracker, use a forum instead. I was under the impression that this *was* the forum for such questions about PyMySQL.

Yes.  I just meant I hope someone other than me answers such configuration question here.

[Rich Fromm]

On Wednesday, April 5, 2017 at 11:58:17 AM UTC-7, Naoki INADA wrote:

BTW, I released 0.7.11 to PyPI.

You won't see any error even when failed to send COM_CLOSE.

Oh, and thanks for releasing. Things work fine for me now.