為什麼 innodb 表這麼容易損壞?
我正在將執行 MySQL 5.1 的舊系統升級到 MariaDB 10。我們在一個全新的 Windows 伺服器上,執行 XAMPP。在我的時間裡,我做了很多數據庫遷移,但這只是 MySQL / InnoDB 地獄的一周。
表是 MyISAM 和 InnoDB 的混合體。每次 MySQL 因任何原因(任何原因)崩潰時,一個或多個 InnoDB 表都會嚴重損壞,我必須手動刪除它們並重新導入它們。“手動刪除它們”是指:
- mysqld 在啟動時崩潰,除非我這樣做
innodb_force_recovery=6
,當我這樣做時,它會將數據庫置於只讀模式,因此 DROP TABLE 只會給我一個表是只讀錯誤。2)所以我必須停止mysqld,刪除有問題的表的.ibd文件,重新啟動mysqld
innodb_force_recovery=0
(現在可以工作),然後最後DROP TABLE工作。
- 然後我必須從 SQL 轉儲中重新導入 3-4gb 表,這需要很長時間。
有趣的是,它從來不是 MyISAM 表,而是 InnoDB 表。
如果我檢查 mysql_error.log,我看到的幾乎總是這樣的:
2015-12-19 1:39:41 6580 [Warning] InnoDB: Allocated tablespace 234, old maximum was 0 InnoDB: Error: trying to access page number 2601 in space 234, InnoDB: space name example/foobar, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 2015-12-19 01:39:41 19b4 InnoDB: Assertion failure in thread 6580 in file fil0fil.cc line 5821 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 151219 1:39:41 [ERROR] mysqld got exception 0x80000003 ;
重新創建和導入表後一切正常,只要 mysqld 不崩潰。
可悲的是,我可以重現這些步驟來很容易地造成這種情況。我所要做的就是執行一個非常慢的查詢——例如,我們的系統生成了一個特定的報告,它背後的查詢很容易執行 30 秒(這是另一個故事)。如果我碰巧在它完成之前停止了 mysqld 服務,那麼下次我啟動 mysqld 時它會立即崩潰,我會重新處理損壞的 InnoDB 表並重複上述所有步驟。
我試過
innodb_fast_shutdown = 0
了,但沒有幫助。你可能會說,“只是不要在查詢執行時停止 mysqld”……你是對的,除了我們系統中的實時使用者可以隨時執行該報告,這意味著任何時候我都需要重新啟動mysqld 出於任何原因,我都希望它不會重新啟動,我將不得不重新創建表。
我在這裡做錯了什麼?
更新 1 長時間執行的查詢就是這樣(更改了表的實際名稱,否則這是逐字記錄):
SELECT * FROM `foobar` ORDER BY `daterun` DESC
該表大約有 130,000 行,大小約為 4GB。
Innodb 是比 myisam 更先進和復雜的引擎。由於某些原因,它是預設儲存引擎…
您說系統從 5.1 升級到 mariadb 10(eqv 到 5.6)。那麼升級路徑是mysql 5.1 -> maria 5.5 -> maria 10?
- 可以分享一下查詢嗎?
- 如果您執行以下操作,問題是否會重複: 終止查詢然後重新啟動?
- 除了在您關閉時執行查詢之外還有什麼其他的嗎?
- 我會嘗試找出是否有相關的錯誤報告。
甚至在我們在這裡確定確切錯誤之前考慮的解決方案: - 用於報告的單獨實例。- 優化查詢。
多年來,我一直在 localhost 中使用 Xampp 5.6 for Windows,我注意到這個包附帶的 MariaDB 總是會破壞我的 WordPress InnoDB 表。
因此,我決定用 MySQL 5.6.44 替換 MariaDB,結果令人驚訝:我的 InnoDB 表再也沒有損壞!我猜這是這個 Xampp 版本附帶的這個特定版本的 MariaDB 的一個錯誤。
我必須將my.ini文件以及MariaDB 文件夾中的數據和備份文件夾複製到 MySQL 文件夾。我還按照此處的建議刪除了MySQL 文件夾中的lib文件夾,以節省安裝空間。