mysql的wait timeout_Mysql的wait_timeout引发的问题

Mysql的wait_timeout引发的问题

环境：Ubuntu16.04 + Tornado + Peewee

写了一个在线笔记的小Demo，测试运行都没问题，但总是第二天打开网站就500错误，一开始根据错误提示，修改了连接数据库的方式，测试，没问题，第二天，依旧。

接连好几天，我都怀疑人品了。既然测试没问题，没道理第二天就挂了呀。

以下是报错信息：

Traceback (most recent call last):

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3768, in execute_sql

cursor.execute(sql, params or ())

File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 166, in execute

result = self._query(query)

File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 322, in _query

conn.query(q)

File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 855, in query

self._execute_command(COMMAND.COM_QUERY, sql)

File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1071, in _execute_command

raise err.InterfaceError("(0, '')")

pymysql.err.InterfaceError: (0, '')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "/usr/local/lib/python3.5/dist-packages/tornado/web.py", line 1467, in _execute

result = method(*self.path_args, **self.path_kwargs)

File "/www/ANoteLink/anote/handlers.py", line 63, in get

if q:

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3257, in __len__

return len(self.execute())

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3233, in execute

self._qr = ResultWrapper(model_class, self._execute(), query_meta)

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 2912, in _execute

return self.database.execute_sql(sql, params, self.require_commit)

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3775, in execute_sql

self.commit()

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3598, in __exit__

reraise(new_type, new_type(*exc_args), traceback)

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 135, in reraise

raise value.with_traceback(tb)

File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3768, in execute_sql

cursor.execute(sql, params or ())

File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 166, in execute

result = self._query(query)

File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 322, in _query

conn.query(q)

File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 855, in query

self._execute_command(COMMAND.COM_QUERY, sql)

File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1071, in _execute_command

raise err.InterfaceError("(0, '')")

peewee.InterfaceError: (0, '')

锁定是操作数据库时发生的问题。检查了代码逻辑，问题依旧。我想应该是数据库本身的问题，猜测是不是连接数据库时间太久没关闭超时了。

因为数据库是用Docker MySQL，我基本就没管过，进容器查看MySQL有关连接超时的设置项：

# docker exec -ti mysqld /bin/bash

# mysql -uroot -p

mysql> show variables like '%timeout';

如下：

+-----------------------------+----------+

| Variable_name | Value |

|-----------------------------+----------|

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| have_statement_timeout | YES |

| innodb_flush_log_at_timeout | 1 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| rpl_stop_slave_timeout | 31536000 |

| slave_net_timeout | 60 |

| wait_timeout | 28800 |

+-----------------------------+----------+

13 rows in set

Time: 0.007s

果然，wait_timeout的值是28800，换算成小时，就是8小时。也就算是说等待8小时就把我的连接给关了。。。我哩个去。怪不得总是第二天就挂了。怪就怪我网站访问人数太少了吧。。

找到问题，接下来是解决方案。

直接进入容器修改，发现重启容器后设置就消失了。所以需要配置文件。

之前的Docker创建MySQL数据库脚本：

docker run --name mysqld --restart always /DATA/mysql/:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=****** -p 3306:3306 mysql

还好数据库是放在容器外的。

修改后：

docker run --name mysqld --restart always -v /DATA/mysql/:/var/lib/mysql -v /DATA/mysql/my.conf:/etc/mysql/conf.d/me.cnf -e MYSQL_ROOT_PASSWORD=****** -p 3306:3306 mysql

在本地创建文件/DATA/mysql/my.conf。添加内容：

[mysqld]

interactive_timeout=28800000

wait_timeout=28800000

需要同时修改interactive_timeout才会生效。

重启Mysql：

docker restart mysqld

重启网站服务。正常。先看看第二天什么情况。