使用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’)
> (1)()->none