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

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

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時出現以下錯誤:

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

則應該先安裝libmysqlclient-dev:

sudo apt install libmysqlclient-dev
pip3 install mysqlclient

------

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

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

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

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),復現問題:

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

結果如下:

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後臺掛載時無處輸出而報錯。

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