给Python中的MySQLdb模块添加超时功能的教程

使用Python操作MySQL数据库的时候常使用MySQLdb这个模块。

今天在开发的过程发现MySQLdb.connect有些参数没法设置。通过这个页面我们可以看到在connect的时候，可以设置的option和client_flags和MySQL c api相比差不少。

一个很重要的参数 MYSQL_OPT_READ_TIMEOUT没法设置，这个参数如果不设置，极致状况MySQL处于hang住，自动切换IP漂移，客户端无法重连到新MySQL。

给MySQLdb加Option很简单，只要修改_mysql.c这个把Python对象映射到MySQL操作的文件，添加参数，再加一段mysql_option即可。

下面是修改后的git diff 文件

    ?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;

代码修改完毕，python setup.py install 即可，如果出现mysql_config找不到的问题。你还要修改setup_posix.py文件。

    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

编译通过，我们来试试添加的read_timeout这个参数。

    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)

然后执行语句前，你试着把mysql用gdb hang住10s后，python就会异常抛错

    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