"Mysql has gone away" several possible

Phenomenon:

After debugging without local use of Flask's own WSGI service, deploy through Gunicorn.

However, after a night of no visit, the next day of visits will appear in Internal error.

Reason:

By tracing the log file, it was found that sqlalchemy connected to MySQL disconnected problem

2006, "MySQL Server has gone away (Brokenpipeerror (+, ' broken pipe ')"

This issue comes from MySQL connection (Session) over a period of time (the default is 8 hours, so the next day I am represented here) is automatically disconnected after no response. The sqlalchemy does not monitor the session disconnection, so this error occurs when trying to use the old session. Check the MySQL log, and you can see that it does actively discard the expired session:

Aborted connection 112225 to db ....., ..... (Got an error reading communication packets)

Solution:

Since SQLAlchemy is due to the expiration of the session, so of course to resolve the expiration, Stack overflow found a different solution, mainly divided into several:

1. Add MySQL wait_timeout so that MySQL does not abandon the connection too soon.

If your service is frequently accessed, it is unlikely that there will be long periods of no connectivity. It is possible to increase this variable to avoid a short time without connection, but if it is out of time, it will still crash. Specific to your own MySQL This setting is how much can be viewed through this command (wait_timeout):

show variables like ‘%timeout%‘;
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+

2. When the engine is created by settingPool_recycle let SQLAlchemy's session pool periodically recycle expired session

The session may have been abandoned by MySQL, so it should be smaller than your MySQL wait_timeout to ensure the validity of the session.

Create_engine("Mysql://scott:[email protected]/test"pool_recycle=3600)   

‘‘‘

Pool_recycle=-1:this setting causes the pool to recycle
    Connections after the given number of seconds have passed. It
    Defaults to-1, or no timeout. For example, setting to 3600
    means connections is recycled after one hour. Note that
    MySQL in particular'll disconnect automatically if no
    Activity is detected on a connection for eight hours (although
    This was configurable with the MYSQLDB connection itself and the
    Server configuration as well).
‘‘‘

！ Note that if you are a cloud service provider, you may have different settings for this disconnect time limit, such as the Pythonanywhere setting is 5 minutes:

Using SQLAlchemy with MySQL

SQLAlchemy needs to some extra arguments to work on Pythonanywhere:

Create_engine(' mysql+mysqldb://... 'pool_recycle=280)     

The RDS service disconnects clients after 5 minutes (300s), so we need to set thepool_recycleto something lower than that, or You'll occasionally see disconnection errors in your logs.

The Sina cloud is set to be shorter, with only 30 seconds:

MySQL Gone Away problem

The MySQL connection time-out is 30s, not the default 8 hours, so you need to check the code to see if it's timed out and you need to reconnect.

For users using SQLAlchemy, you need to callDb.session.close ()at the end of the request process, close the current session, and return the MySQL connection to the connection pool. and recyle the connection pool connection to a smaller time (60s recommended).

3. Reconstruction session?? (from StackOverflow)

The highest-ticket answer in this question says that you instantiate a session in every place where you need to use SQLAlchemy.

But in my actual operation, by looking at the session ID, two instances of the ID of the same, that is, the session () only from the session pool to take back the old session, Then this session will expire sooner or earlier. The fact also proves that this method does not solve the problem of MySQL gone away in my service. So I have reservations about the answer.

4.SQlAlchemy official: Set Pool_pre_ping=true

Official documents in fact for the MYSLQ broken chain problem, the official in addition to set up Pool_recycle also recommended in the creation of the engine when the setting pool_pre_ping=true that is, every time before the session will be a simple query check, Determines whether the session expires.

Engine = Create_engine ("mysql+pymysql://user:[email protected]/db", Pool_pre_ping=true)

Detailed visibility of the reference site.

But after I've tried all of these methods, I'm still going to reproduce the problem, and finally see this document saying:

To use SQLAlchemy in a declarative-your application, you just has to put the following code into your Applicatio N module. Flask'll automatically remove database sessions at the end of the request or when the application shuts down:

 
from yourapplication.database import db_session

@app.teardown_appcontext def shutdown_session(exception=None):
    db_session.remove()

Finally, MySQL has gone away disappeared! That is, each time the request is over, Flask will close the session. (TODO: Test Whether this method is inefficient.) )

Reference:

Avoiding "MySQL server has gone away" on infrequently used Python/flask server with SQLAlchemy

Connection Pooling?

SQLAlchemy in Flask

Http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects

https://mofanim.wordpress.com/2013/01/02/sqlalchemy-mysql-has-gone-away/

"Mysql has gone away" several possible