Pymysql.err.OperationalError - Lost Connection To MySQL Server During Query

I am using Python script to insert records into MySQL database table. The script fails with the following error message. MySQL version is 8.0.17 ,Python version 3.6.5 (pymysql.err

Solution 1:

MySQL automatically closes connections that have been idle for a specific period of time (wait_timeout for non-interactive connections). Therefore it may happen, that your connections are closed if there is too much idle time and connections are not renewed or connections are invalidated because of server restarts.

SQL-Alchemy mentions several strategies on how to tackle the issue of automatic disconnects and database restarts in its documentation on how to deal with pool disconnects.

Two options that you should have a look at are the pool_pre_ping parameter that adds a SELECT 1 before each query to check if the connection is still valid, otherwise the connection will be recycled.

The other option is pool_recycle time that should always be less then your mysql wait_timeout. After this time the connection is automatically recycled to not run in the wait_timeout.

You can check your connections in MySQL using the command

SHOW PROCESSLIST;

where you should see all open connection an the status they are in.