最近我們的Django項目供Java Sofa應用進行tr調用時, 經常會出現一個異常: django.db.utils.OperationalError: (2006, 'MySQL server has gone away'). 本文記錄了分析, 本地重制與解決此問題的全過程.
原因分析:
Django在1.6引入長連結(Persistent connections)的概念, 可以在一個HTTP請求中一直用同一個連接配接對資料庫進行讀寫操作.
但我們的應用對資料庫的操作太不頻繁了, 兩次操作資料庫的間隔大于MySQL配置的逾時時間(預設為8個小時), 導緻下一次操作資料庫時的connection過期失效.
Our databases have a 300-second (5-minute) timeout on inactive connections. That means, if you open a connection to the database, and then you don’t do anything with it for 5 minutes, then the server will disconnect, and the next time you try to execute a query, it will fail.
重制問題:
設定mysql wait_timeout為10s
在macOS上的mysql配置檔案路徑: /usr/local/etc/my.cnf
1
2
3
4
5
6
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
wait_timeout = 10
interactive_timeout = 10
重新開機mysql:
1
2
3
4
➜ ~ brew services restart mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
檢查wait_timeout的值是否已被更新.
1
2
3
4
5
6
7
8
9
mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
| wait_timeout | 10 |
+--------------------------+----------+
3 rows in set (0.00 sec)
重制exception:
1
2
3
4
5
6
7
8
9
10
11
12
>>> XXX.objects.exists()
True
>>> import time
>>> time.sleep(15)
>>> XXX.objects.exists()
True
>>> XXX.objects.exists()
...
django.db.utils.OperationalError: (2013, 'Lost connection to MySQL server during query')
>>> XXX.objects.exists()
...
django.db.utils.OperationalError: (2006, 'MySQL server has gone away')
有意思的一個點是, sleep 10s 之後, 第一次操作資料庫, 會出現(2013, 'Lost connection to MySQL server during query’)異常. 之後再操作資料庫, 才會抛出(2006, 'MySQL server has gone away’)異常.
解決問題:
第一個最暴力的方法就是增加mysql的wait_timeout讓mysql不要太快放棄連接配接. 感覺不太靠譜, 因為不能杜絕這種Exception的發生.
第二個辦法就是手動把connection直接關閉:
1
2
3
4
5
6
7
8
>>> Alarm.objects.exists()
True
>>> from django.db import connection
>>> connection.close()
>>> time.sleep(10)
>>> Alarm.objects.exists()
True
>>>
發現不會出現(2006, 'MySQL server has gone away’)異常了, 但總感覺還是不夠優雅.
最終決定在用戶端(Django), 設定逾時時間(CONN_MAX_AGE: 5)比mysql服務端(wait_timeout = 10)小:
1
2
3
4
5
6
7
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'CONN_MAX_AGE': 5,
}
}
但很奇怪沒有生效??? 看了源代碼, 發現隻有在request_started(HTTP request)和request_finished的時候, 在close_if_unusable_or_obsolete才用到CONN_MAX_AGE并去驗證時間關閉connection.
具體代碼見: python3.6/site-packages/django/db/__init__.py#64
1
2
3
4
5
6
7
8
9
# Register an event to reset transaction state and close connections past
# their lifetime.
def close_old_connections(**kwargs):
for conn in connections.all():
conn.close_if_unusable_or_obsolete()
signals.request_started.connect(close_old_connections)
signals.request_finished.connect(close_old_connections)
而我的代碼是處理一個任務而不是HTTP請求, 是以不會觸發這個signal. 于是我寫了一個裝飾器, 在任務的開始和結束的時候, 關閉所有資料庫連接配接.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from django.db import connections
# ref: django.db.close_old_connections
def close_old_connections():
for conn in connections.all():
conn.close_if_unusable_or_obsolete()
def handle_db_connections(func):
def func_wrapper(request):
close_old_connections()
result = func(request)
close_old_connections()
return result
return func_wrapper
# ------割-------
@handle_db_connections
def process_trsbrain_request(request):
...
ps. CONN_MAX_AGE預設其實為0, 意味着預設在http請求和結束時會關閉所有資料庫連接配接.
其他:
django.db中connection和connections的差別???
connection對應的是預設資料庫的連接配接, 用代碼表示就是connections[DEFAULT_DB_ALIAS]
connections對應的是setting.DATABASES中所有資料庫的connection