使用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 文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
?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; |
代码修改完毕，python setup.py install 即可，如果出现mysql_config找不到的问题。你还要修改setup_posix.py文件。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 |
编译通过，我们来试试添加的read_timeout这个参数。
|
1
|
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就会异常抛错
|
1
2
3
4
5
6
7
8
9
10
|
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 |
