when db server kill connection,how to reconnect db server

[ziven1987]

when the client connect mysql timeout, mysql will kill the connection,happend MySQL server has gone away.how to reconnect db server when the connect lose.

[dgilland]

You should be able to avoid this situation by ensuring that the MySQL wait_timeout and sqlservice SQL_POOL_RECYCLE values are compatible. Basically, you need SQL_POOL_RECYCLE to be less than wait_timeout.

For example, if you had the following my.cnf:

[mysqld]
wait_timeout = 600

Then, you'd want SQL_POOL_RECYCLE to be less than 600 (e.g. 300).

from sqlservice import SQLClient

db = SQLClient({'SQL_POOL_RECYCLE': 300, ...}, ...)

That way SQLAlchemy will recycle the connection before MySQL closes it due to exceeding its own timeout.

But if you still get error and want to reconnect, you can just call db.remove() to remove the session and obtain a new connection.

There's also "pessimistic disconnect handling" which can also be used to pre-ping a connection before it's checked out from the pool to ensure it is usable: http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic

[dgilland]

Another thing to consider is if you are getting the "gone away", it may be that you are keeping an open connection for too long instead of closing it after use (e.g. in a long running process). Just be sure to call db.remove() once you've finished processing so that if the next usage isn't for awhile, a fresh connection will be established instead of trying to use one that remained open for too long.

[ziven1987]

Thank you for the quick response！ I'm actually using an single connection, and when i finished the request, i did not close the connection.I have fixed it by call db.remove() and i have added pre-ping to ensure the connection is usable.

By the way,can sqlservice help me to automatically handle session commit?I didn't call db.commit() when I was using it local test,but the result save to the db success.

Whether I should call db.commit() before db.remove() when i finished a request?

[dgilland]

What method did you use to save the data? Using db.save() automatically calls session.commit(). Otherwise, you'll need to call commit() at some point to persist the data.

You want to commit before removing the session since remove() includes a call to rollback().

[ziven1987]

Thank you very much. I got it.