How to add the timeout function to the MySQLdb module in Python

This article describes how to add the timeout function to the MySQLdb module in Python. The timeout function is very useful in server O & M, for more information, see MySQLdb.

During the development process today, some parameters of MySQLdb. connect cannot be set. On this page, we can see that the options and client_flags that can be set during connect are much worse than those of MySQL c APIs.

A very important parameter, MYSQL_OPT_READ_TIMEOUT, cannot be set. If this parameter is not set, MySQL stays in the hang state, automatically switches the IP address drift, and the client cannot re-connect to the new MySQL.

Adding Option to MySQLdb is simple. You only need to modify _ mysql. c to map the Python object to the MySQL operation file, add parameters, and add a piece of mysql_option.

The modified git diff file is as follows:

?View Code BASHdiff --git a/_mysql.c b/_mysql.cindex d42cc54..61a9b34 100644--- a/_mysql.c+++ b/_mysql.c@@ -489,9 +489,10 @@ _mysql_ConnectionObject_Initialize("named_pipe", "init_command","read_default_file", "read_default_group","client_flag", "ssl",-                 "local_infile",+                 "local_infile", "read_timeout",NULL } ;int connect_timeout = 0;+    int read_timeout = 0;int compress = -1, named_pipe = -1, local_infile = -1;char *init_command=NULL,*read_default_file=NULL,@@ -500,7 +501,7 @@ _mysql_ConnectionObject_Initialize(self->converter = NULL;self->open = 0;check_server_init(-1);-    if (!PyArg_ParseTupleAndKeywords(args, kwargs, "|ssssisOiiisssiOi:connect",+    if (!PyArg_ParseTupleAndKeywords(args, kwargs, "|ssssisOiiisssiOii:connect",kwlist,&host, &user, &passwd, &db,&port, &unix_socket, &conv,@@ -509,7 +510,8 @@ _mysql_ConnectionObject_Initialize(&init_command, &read_default_file,&read_default_group,&client_flag, &ssl,-                    &local_infile /* DO NOT PATCH FOR RECONNECT, IDIOTS+                    &local_infile, &read_timeout+                    /* DO NOT PATCH FOR RECONNECT, IDIOTSIF YOU DO THIS, I WILL NOT SUPPORT YOUR PACKAGES. */))return -1;@@ -540,6 +542,12 @@ _mysql_ConnectionObject_Initialize(mysql_options(&(self->connection), MYSQL_OPT_CONNECT_TIMEOUT,(char *)&timeout);}++    if (read_timeout) {+        unsigned int timeout = read_timeout;+        mysql_options(&(self->connection), MYSQL_OPT_READ_TIMEOUT, (char *)&timeout);+    }+if (compress != -1) {mysql_options(&(self->connection), MYSQL_OPT_COMPRESS, 0);client_flag |= CLIENT_COMPRESS;

After the code is modified, run python setup. py install. If mysql_config cannot be found. You also need to modify the setup_posix.py file.

hoterran@hoterran-laptop:~/Projects/MySQL-python-1.2.3$ git diff setup_posix.pydiff --git a/setup_posix.py b/setup_posix.pyindex 86432f5..f4f08f1 100644--- a/setup_posix.py+++ b/setup_posix.py@@ -23,7 +23,7 @@ def mysql_config(what):     if ret/256 > 1:       raise EnvironmentError("%s not found" % (mysql_config.path,))   return data-mysql_config.path = "mysql_config"+mysql_config.path = "/usr/local/mysql/bin/mysql_config" def get_config():   import os, sys

After compilation is passed, let's try the read_timeout parameter added.

conn = MySQLdb.connect(host = DB_SERVER,user = DB_USERNAME,passwd = DB_PASSWORD,db = DB_NAME, port=int(DB_PORT), client_flag = 2, read_timeout = 10)

Before executing the statement, you try to put mysql in gdb hang for 10 s, and then python will throw an exception

OperationalError: (2013, 'Lost connection to MySQL server during query') >/home/hoterran/Projects/dbaas/trunk/dbtest.py(18)() >mydb.execute_sql(conn, sql)(Pdb)--Return--> /home/hoterran/Projects/dbaas/trunk/dbtest.py(18)()->None> mydb.execute_sql(conn, sql)(Pdb)OperationalError: (2013, 'Lost connection to MySQL server during query')> 
 
  (1)
  
   ()->None