tutorial on adding timeout functionality to the MySQLdb module in Python

Summary
This article mainly introduces a tutorial to add a timeout function to the MySQLdb module in Python. The timeout function is very useful in the operation and maintenance of the server. Students who need it can refer to it.


 
The MySQLdb module is often used when operating a MySQL database using Python.

During the development process today, I found that some parameters of MySQLdb.connect could not be set. Through this page, we can see that when connecting, the options and client_flags that can be set are much worse than MySQL c api.

A very important parameter, MYSQL_OPT_READ_TIMEOUT, cannot be set. If this parameter is not set, the extreme situation is that MySQL is in a state of hang, automatically switching IP drift, and the client cannot reconnect to the new MySQL.

Adding Option to MySQLdb is as simple as modifying _mysql.c, a file that maps Python objects to MySQL operations, adding parameters, and then adding a section of mysql_option.

The following is the modified git diff file

? View Code BASH
 
diff --git a / _mysql.c b / _mysql.c
index 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, IDIOTS
IF 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, python setup.py install is enough. If there is a problem that 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.py
diff --git a / setup_posix.py b / setup_posix.py
index 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
 
Compiled, let's try to add the read_timeout parameter.

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)
 
Then before executing the statement, you try to hang mysql with gdb for 10s, python will throw an error

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')
> <string> (1) <module> ()-> None
Article source: https://www.tah1986.com/104701.html