解決JDBC連接Mysql長時間無動作連接失效的問題
錯誤場景介紹
做的有一個項目使用JDBC手動創建Connection實現瞭一個簡單的自定義數據庫連接池,用來支持Canal解析數據庫Binlog指定業務庫的插入修改SQL來進行數據庫分表備份(按照月份)操作.
但是發現當一個一段時間(較長)沒有進行數據庫操作時,連接都失效瞭,導致SQL執行失敗失效提示為No operations allowed after connection closed
查明原因
經過搜索發現這個問題是由於Mysql默認一個已創建的長連接28800秒(八小時)內沒有任何動作則會斷開連接,該值對應參數為wait_timeout.當超時時間內有執行動作則會重新計時
查驗
查詢Mysql超時連接時長命令show global variables like’wait_timeout’查看當前設置的超時斷開連接時長.
將其改為10,本地服務運行功能發現重現瞭No operations allowed after connection closed錯誤,即確實是連接超時失效
解決方法
1. 修改Mysql配置
該方法不能根治這個問題,因為不能確認服務空閑時長而精確設置timeout並且還會造成多餘連接長時間未斷開而影響性能,所以不建議使用.建議在代碼層面進行解決
通過set global wait_timeout=time(秒)來修改最長連接等待超時時間,但是這樣設置當Mysql重啟失效
可以通過修改my.ini文件永久改動超時時間,如下配置
interactive_timeout=28800000 wait_timeout=28800000
2. 連接丟棄重新創建連接
使用conn.isValid(int timeout)(秒)判斷是否失效返回true表示連接有效,返回false表示連接失效.當失效時則重新獲取一個數據庫連接即可,之前的對象由於引用丟失會被回收掉.
3. 增加自動重連選項
在URL最後添加autoReconnect=true參數,jdbc:mysql://hostaddress:3306/xhb?autoReconnect=true.我這裡對這個沒有效果,可能是對框架連接池有用.
4. 定時執行一個動作進行超時時間刷新
比如默認時間是八小時,則每七小時對連接執行一次select 1語句來刷新該連接在數據庫的超時等待時長
也可以1 2 4一起使用,來防止突然一個流量靜默期間後突發流量高峰而導致獲取連接不及時
補充：連接總是被mysql回收_一般連接池是怎麼處理mysql自動回收長時間
若幹套 MySQL 環境，隻有一套：
行為異常，e5a48de588b63231313335323631343130323136353331333436316239懷疑觸發 bug
性能異常，比其他環境都要低
在這種場景下，我們一般的做法是首先控制變量，查看軟硬件配置，以及 MySQL 的參數配置。關於 MySQL 的參數配置對比，如果我們人工對比的話隻會關註某些重點參數，而缺少瞭整體細節上的的對比。在這裡我們推薦給大傢 Percona Toolkit 中的一個工具 pt-config-diff
更準確的復制延時 pt-heartbeat在 MySQL 中，復制延遲可以理解為由兩部分組成：1. 主庫已經生成瞭 BINLOG，但是還沒有發送給從庫 — 我們在這裡稱之為：日志延遲2. 從庫已經接收到瞭 BINLOG，但是還沒有應用完成 — 我們在這裡稱之為：應用延遲MySQL 原生的查看復制延遲的手段為：show slave status\G中的Seconds_Behind_Master。這種觀測手法隻能觀測出應用延遲。在異步復制或降級的半同步復制下，誤差較大，無法準確的反映出整體復制延時。
1. 在 Master 上循環插入：insert into database.heartbeat (master_now) values(NOW())
2. database.heartbeat 的變更會跟隨主從復制流向從庫
3. 系統當前時間 – 從庫表中的時間 = 從庫實際的復制延時
更簡單的參數配置建議 pt-variable-advisortoolkit 中包含瞭一個簡單的 MySQL 參數優化器，可以對參數配置做簡單的優化建議。
更準確的復制延時 pt-heartbeat在 MySQL 中，復制延遲可以理解為由兩部分組成：1. 主庫已經生成瞭 BINLOG，但是還沒有發送給從庫 — 我們在這裡稱之為：日志延遲2. 從庫已經接收到瞭 BINLOG，但是還沒有應用完成 — 我們在這裡稱之為：應用延遲MySQL 原生的查看復制延遲的手段為：show slave status\G中的Seconds_Behind_Master。這種觀測手法隻能觀測出應用延遲。在異步復制或降級的半同步復制下，誤差較大，無法準確的反映出整體復制延時。
更易用的調試工具 pt-pmp在某些情況下，我們肯定會遇到某些故障無法從日志，以及狀態命令中找到原因，需要深入到程序邏輯級別。又或者我們需要立即通過非常規手段恢復故障數據庫，但是又想保留足夠多的故障信息。來避免我們事後復現問題的頭疼。pt-pmp 便是在這種場景下幫助我們的工具。它會使用 gdb 來打印 mysqld 的堆棧信息，並把調用鏈相同的線程堆棧合並。堆棧合並的功能對於 MySQL 這種多線程的應用非常有幫助，會節省我們大量的時間。
以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。