Python MySQL 失聯及解決辦法

2020/11/27附註：
本文闡述的條件是：

而非MySQL-python。對於MySQL-python，本身僅支持Python 2而在Python 3下安裝和運行不便（需修改配置），且已事實（de facto）停止維護。
事實上，建議使用新的
PyMySQL，並在Django項目的settings.py添加：

1
2
3
4
import pymysql
# Avoid mysqlclient low version error
# pymysql.version_info = (1, 4, 0, "final", 0)
pymysql.install_as_MySQLdb()

其中第3行只是在未安裝mysqlclient包的前提下的權宜之計。
如果在Linux終端執行pip3 install mysqlclient時出現以下錯誤：

1
2
3
/bin/sh: 1: mysql_config: not found
/bin/sh: 1: mariadb_config: not found
/bin/sh: 1: mysql_config: not found

則應該先安裝libmysqlclient-dev：

1
2
sudo apt install libmysqlclient-dev
pip3 install mysqlclient

-—–

服務器掛載一個利用uwsgi+Django+MySQL的項目。
其中MySQL連接一直未關閉。
想要查表，網頁卻報錯了：

1
2
OperationalError at /
(2006, 'MySQL server has gone away')

查閱了相關資料，發現是MySQL設置了非活動連接超時：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 (0.00 sec)

於是爲了一探究竟，直接執行set global interactive_timeout=1;，再用Jupyter Notebook運行了下列代碼，通過conn.ping()檢查連接狀態，設置time.sleep(4)（4s>2s），復現問題：

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
import MySQLdb
import time

conn = MySQLdb.connect("localhost", "app", "password", "app", charset='utf8mb4' )
curs=conn.cursor()
curs.execute("show variables like '%timeout%';")
res=curs.fetchall()
time.sleep(4)
try:
    conn.ping()
    print('OK 1')
except Exception as e:
    print("MySQL connection error 1")
    print(str(e))
    while True:
        try:
            conn = MySQLdb.connect("localhost", "app", "password", "app", charset='utf8mb4' )
            break
        except Exception as e:
            print("Fail to reconnect")
            time.sleep(2)
            continue
            
curs=conn.cursor()
curs.execute("show tables;")
res=curs.fetchall()
time.sleep(4)
try:
    conn.ping()
    print('OK 2')
except Exception as e:
    print("MySQL connection error 2")
    print(str(e))
    while True:
        try:
            conn = MySQLdb.connect("localhost", "app", "password", "app", charset='utf8mb4' )
            break
        except Exception as e:
            print("Fail to reconnect")
            time.sleep(2)
            continue

curs=conn.cursor()
curs.execute("show tables;") 
res=curs.fetchall()
conn.close()
try:
    conn.ping()
    print('OK 3')
except Exception as e:
    print("MySQL connection error 3")
    print(str(e))
    while True:
        try:
            conn = MySQLdb.connect("localhost", "app", "password", "app", charset='utf8mb4' )
            break
        except Exception as e:
            print("Fail to reconnect")
            time.sleep(2)
            continue

curs=conn.cursor()
curs.execute("show tables;") 
res=curs.fetchall()
try:
    conn.ping()
    print('OK 4')
except Exception as e:
    print("MySQL connection error 4")
    print(str(e))
    while True:
        try:
            conn = MySQLdb.connect("localhost", "app", "password", "app", charset='utf8mb4' )
            break
        except Exception as e:
            print("Fail to reconnect")
            time.sleep(2)
            continue

結果如下：

1
2
3
4
5
6
7
MySQL connection error 1
(2006, 'MySQL server has gone away')
MySQL connection error 2
(2006, 'MySQL server has gone away')
MySQL connection error 3
(2006, '')
OK 4

我們可以如此分析：

  • 前兩次ping，是在超過了interactive_timeout之後連接，勢必報錯。
  • 第三次ping，由於之前關閉了conn，也勢必報錯。
  • 第四次ping，由於緊承上面的代碼且未超過interactive_timeout，故而不報錯。

當然，實際部署時，調試用的控制檯輸出應刪去，防止uwsgi後臺掛載時無處輸出而報錯。

如果不是頻繁查詢，我們更推薦一次查詢一開一關。