max_allowed_packet引起MySQL遷移丟失資料的問題

Mr-houzi發表於2021-06-18
MySql

起因

最近遷移了伺服器，運維同學給導了資料庫，資料庫也跟著一起遷移到了新機房。今天，有使用者發現自己建立的一條記錄不見了，我去新庫查詢確實沒有，去舊庫查詢到了這條記錄，確定了是資料丟失。

排查過程

起初，懷疑遷移到新庫已經有幾天了，是否是這幾天使用者手動刪除了這條記錄？還好對使用者的改動做了記錄，經過查詢發現，使用者並沒有刪除動作。

所以，大概率是資料庫遷移引起的資料庫丟失，具體是哪個環節出錯，還要繼續排查。

運維同學給出了當時從老庫裡匯出的sql，發現是存在丟失的這條記錄的，這證明了從老庫匯出沒有問題，問題出現在往新庫匯入的時候。

找到了這條丟失記錄的匯入sql，由於有一個欄位用了 longtext 型別，這個欄位記憶體入了大量的json資料，將這條sql儲存成txt後，竟然有1.7M大小。把這條語句通過命令列單獨匯入，報錯 MySQL server has gone away。

問題

通過上面的排查，基本上可以確定是由於sql語句過大，導致插入失敗，引起遷移時資料丟失的問題。

解決

MySQL 中有一個 max_allowed_packet 引數，用來控制一次插入語句的大小，像Blob、longtext型別的欄位很容易導致sql語句過長，而達到 max_allowed_packet 的限制。

max_allowed_packet 當前大小是 1048576 （1024 X 1024 X 1），也就是 1M 大小，而我那條語句竟然達到了1.7M ，顯然超過了上限。


mysql> show global variables like 'max_allowed_packet';

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

| Variable_name      | Value   |

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

| max_allowed_packet | 1048576 |

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

1 row in set (0.01 sec)

調大 max_allowed_packet 值


mysql> set global max_allowed_packet=1024*1024*16;

Query OK, 0 rows affected (0.00 sec)

對比新老兩庫後，發現了總共丟失了兩條記錄，在調大 max_allowed_packet 值後，分別重新插入，恢復了丟失的資料。

反思

經歷了這次事件後的幾個小反思：

1. Text 欄位是個坑。

Text對資料庫效能就已經有明顯影響了。更何況了是LongText。LongText 最大能存 (2^32 -1)個位元組，即 4GiB。使用LongText欄位便是給自己和運維同學留下了一個坑。由於這個欄位儲存的是大文字 json ，日後可以考慮將此欄位放入 MongoDB 。

  • TEXT | 65,535（2 16 -1）個位元組= 64個KiB

  • MEDIUMTEXT | 16,777,215（2 24 -1）位元組= 16 MiB

  • LONGTEXT | 4,294,967,295（2 32 -1）個位元組= 4個GiB

2. 資料遷移失敗日誌。

運維同學說使用 mysqldump 導得資料，如果資料遷移時能有一個失敗日誌，那麼就能及時發現這個問題。另外，遷移庫後，如果我立即比較一下表的記錄數，或許也能早點發現這個問題。

[參考]

MySQL server has gone away 問題的解決方法

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章