Solve the problem of MySQL server actively disconnecting when there is no operation timeout

Solve the problem of MySQL server actively disconnecting when there is no operation timeout

When we use the MySQL service, under normal circumstances, the timeout setting of MySQL is 8 hours (28800 seconds). That is to say, if a connection has no operation for 8 hours, MySQL will actively disconnect the connection. When the connection tries to query again, it will report an error "MySQL server has gone away". However, sometimes, due to some settings on the MySQL server, in many cases the connection timeout will be shortened to ensure that more connections are available. Sometimes the setting is abnormal, very short, 30 seconds, so the client needs to do some operations to ensure that MySQL does not actively disconnect.

View mysql timeout

Use the client tool or the Mysql command line tool to enter show global variables like '%timeout%'; and the properties related to timeout will be displayed. Here I use docker to simulate a test environment.

mysql> show variables like '%timeout%'; 
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 30 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 30 |
+-----------------------------+----------+
13 rows in set

wait_timeout: The number of seconds the server waits for activity before closing a non-interactive connection, which is the time you call the program in your project.

interactive_timeout: The number of seconds the server waits for activity before closing an interactive connection, which is the time you open the MySQL client on your local machine, such as cmd.

Querying with pymysql

I created a random table in the database and inserted two pieces of data

mysql> select * from person;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | yang | 18 |
| 2 | fan | 16 |
+----+------+-----+
2 rows in set

I use the pymysql library to query it, it's very simple

#coding:utf-8
import pymysql

def mytest():
  connection = pymysql.connect(
  host='localhost',
  port=3306,
  user='root',
  password='123456',
  db='mytest',
  charset='utf8')

  cursor = connection.cursor()
  cursor.execute("select * from person")
  data = cursor.fetchall()
  cursor.close()
  for i in data:
    print(i)
  cursor.close()
  connection.close()

if __name__ == '__main__':
  mytest()

The correct result can be obtained

(1, 'yang', 18)

(2, 'fan', 16)

Query after connection timeout

The above results can be obtained normally because after a link is created, a query is performed immediately, and its timeout period has not been exceeded. If I sleep for a while, I will see what the effect is.

#coding:utf-8

import pymysql
import time


def mytest():
  connection = pymysql.connect(
  host='localhost',
  port=3306,
  user='root',
  password='123456',
  db='mytest',
  charset='utf8')
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data = cursor.fetchall()
  for i in data:
    print(i)
  cursor.close()

  time.sleep(31)
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data2 = cursor.fetchall()
  for i in data2:
    print(i)
  cursor.close()
  connection.close()

if __name__ == '__main__':
  mytest()

Two queries were performed here. Because I set the wait_timeout of mysql to 30 seconds, I stopped for 31 seconds after the first query to let the mysql service actively disconnect from the connection I just created. The result was

(1, 'yang', 18)
(2, 'fan', 16)
Traceback (most recent call last):
 File "F:/python/python3Test/mysqltest.py", line 29, in <module>
  mytest()
 File "F:/python/python3Test/mysqltest.py", line 22, in mytest
  cursor.execute("select * from person")
 ...
 ...
 File "C:\Python35\lib\site-packages\pymysql\connections.py", line 702, in _read_bytes
  CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

Process finished with exit code 1

You can see that after 31 seconds of pausing, using the connection again to query will throw a 2013, 'Lost connection to MySQL server during query' error.

Solution

There are two solutions. Since the timeout here is due to no operation within the specified time, MySQL actively closes the connection. The pymysql connection object has a ping() method to check whether the connection is valid. Execute the ping() method before each query operation. This method has a reconnect parameter by default, which is True by default. If the connection is lost, it will reconnect.

#coding:utf-8

import pymysql
import time


def mytest():
  connection = pymysql.connect(
  host='localhost',
  port=3306,
  user='root',
  password='123456',
  db='mytest',
  charset='utf8')
  connection.ping()
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data = cursor.fetchall()
  for i in data:
    print(i)
  cursor.close()
  
  time.sleep(31)
  connection.ping()
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data2 = cursor.fetchall()
  for i in data2:
    print(i)
  cursor.close()
  connection.close()

if __name__ == '__main__':
  mytest()

I have tried using another thread to continuously perform the ping() operation, but when I do this the connection is lost and subsequent operations cannot be performed. I will study this issue further.

#coding:utf-8

import pymysql
import time
import threading
import traceback

def ping(conn):
  while True:
    try:      
      conn.ping()
    except:
      print(traceback.format_exc())
    finally:
      time.sleep(1)

def mytest():
  connection = pymysql.connect(
  host='localhost',
  port=3306,
  user='root',
  password='123456',
  db='mytest',
  charset='utf8')
  cursor = connection.cursor()
  # It won't work here. You must wait for an execute of cursor before running. # th = threading.Thread(target=ping, args=(connection,))
  # th.setDaemon(True)
  # th.start()
  cursor.execute("select * from person")
  data = cursor.fetchall()
  for i in data:
    print(i)
  cursor.close()

  # The thread can be started here th = threading.Thread(target=ping, args=(connection,))
  th.setDaemon(True)
  th.start()
  
  time.sleep(31)
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data2 = cursor.fetchall()
  for i in data2:
    print(i)
  cursor.close()
  connection.close()

if __name__ == '__main__':
  mytest()

Another method is to use a connection pool, which keeps a specified number of available connections and re-acquires a valid connection each time for query operations. Pymysql itself does not have a connection pool function, so you need to use DBUtils

#coding:utf-8
import pymysql
import time
from DBUtils.PooledDB import PooledDB, SharedDBConnection


def mytest():
  pool = PooledDB(
    creator=pymysql,
    # When initializing, the connection pool creates at least idle connections, 0 means no creation maxconnections=3, 
    # The maximum number of idle connections in the connection pool. 0 and None indicate no limit. mincached=2,
    # The maximum number of shared connections in the connection pool. 0 and None mean all are shared (which is actually useless)
    maxcached=5,    
    maxshared=3,
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    db='mytest',
    charset='utf8'
  )
  connection = pool.connection()
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data = cursor.fetchall()
  for i in data:
    print(i)

  time.sleep(40)
  cursor.execute("select * from person")
  data2 = cursor.fetchall()
  for i in data2:
    print(i)
  cursor.close()
  connection.close()

if __name__ == '__main__':
  mytest()

Although this method can correctly obtain the results, it is not used in actual projects. Instead, the connection should be closed after the query statement is executed. Note that the closing here is not a real closing, but just returning the connection to the connection pool for others to use.

#coding:utf-8
import pymysql
import time
from DBUtils.PooledDB import PooledDB, SharedDBConnection


def mytest():
  pool = PooledDB(
    creator=pymysql,
    maxconnections=3,
    # When initializing, the connection pool creates at least idle connections, 0 means no mincached=2,
    # The maximum number of idle connections in the connection pool. 0 and None indicate no limit. maxcached=5,
    # The maximum number of shared connections in the connection pool. 0 and None mean all are shared (which is actually useless)
    maxshared=3,
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    db='mytest',
    charset='utf8'
  )
  connection = pool.connection()
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data = cursor.fetchall()
  for i in data:
    print(i)
  cursor.close()
  # Closing the connection does not actually close it, it just returns the connection to the connection pool connection.close()

  time.sleep(40)
  connection = pool.connection()
  cursor = connection.cursor()
  cursor.execute("select * from person")
  data2 = cursor.fetchall()
  for i in data2:
    print(i)
  cursor.close()
  connection.close()

if __name__ == '__main__':
  mytest()

The above article on how to solve the problem of MySQL server actively disconnecting when there is no operation timeout is all the content that the editor shares with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • Solution to the timeout of the mini program server requesting the WeChat server
  • Solution to the timeout problem of curl and soap request service in php
  • A brief discussion on service exceptions caused by asynchronous multithreading timeout in Java
  • Detailed explanation of how to configure timeout in Nginx server
  • The Win7 system log prompts that the name resolution timeout of the name "domain name" has expired after no configured DNS server responds.
  • Solution for ORA-12170 TNS: Connection timeout when connecting to Oracle remote server
  • Timeout when connecting to the server using FileZilla
  • Solution to SNMP4J server connection timeout problem

<<:  A brief discussion on the solution to excessive data in ElementUI el-select

>>:  Use MySQL to open/modify port 3306 and open access permissions in Ubuntu/Linux environment

Blog    

Recommend

Detailed explanation of 8 ways to pass parameters in Vue routing components

When we develop a single-page application, someti...

Detailed explanation of MySQL installation and new password authentication method in MySQL 8.0

1. Introduction Oracle has released MySQL 8.0GA. ...

How to get form data in Vue

Table of contents need Get data and submit Templa...

Implementation of MySQL select in subquery optimization

The following demonstration is based on MySQL ver...

Detailed explanation of tcpdump command examples in Linux

Preface To put it simply, tcpdump is a packet ana...

Mysql master/slave database synchronization configuration and common errors

As the number of visits increases, for some time-...

Detailed explanation of Linux rpm and yum commands and usage

RPM package management A packaging and installati...

Implementation of MySQL custom list sorting by specified field

Problem Description As we all know, the SQL to so...

Solution to the conflict between nginx and backend port

question: When developing the Alice management sy...

IE8 provides a good experience: Activities

Today I had a sneak peek at IE8 beta 1 (hereafter...

Solve the problem that the time zone cannot be set in Linux environment

When changing the time zone under Linux, it is al...

Install two MySQL5.6.35 databases under win10

Record the installation of two MySQL5.6.35 databa...

Implementation of React configuration sub-routing

1. The component First.js has subcomponents: impo...

How to delete node_modules and reinstall

Table of contents Step 1: Install node_modules in...

XHTML Web Page Tutorial

<br />This article is mainly to let beginner...