tutorial on adding timeout functionality to the MySQLdb module in Python

Source: Internet
Author: User
Tags diff mysql database 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.