MySQL 非交易表回滾失敗：原因、檢查與解決方案

執行 ROLLBACK 出現 Warning: Some non-transactional changed tables couldn't be rolled back，表示交易中混入非交易型表（如 MyISAM）。本文說明原因、如何檢查表引擎、以及解決策略。

錯誤訊息解讀

Warning: Some non-transactional changed tables couldn't be rolled back.

意味著：交易內修改的表中，有不支援交易的引擎（MyISAM、MEMORY、CSV 等），這些表的變更**無法回滾**，已永久生效。

常見原因

• 以為建立的是 InnoDB 表，實際預設引擎是 MyISAM
• CREATE TABLE 未指定 ENGINE=InnoDB
• 伺服器啟動參數停用 InnoDB（--skip-innodb）
• 匯入 SQL 檔時未帶引擎定義

檢查表引擎

-- 方法一
SHOW TABLE STATUS LIKE 'tbl_name'\G
-- 關注 Engine 欄位

-- 方法二
SHOW CREATE TABLE tbl_name\G
-- 結尾會顯示 ENGINE=...

-- 方法三（資訊架構）
SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name'

檢查伺服器支援引擎

SHOW ENGINES\G
-- Support 欄位：YES/DEFAULT/NO/DISABLED

-- 或檢查變數
SHOW VARIABLES LIKE 'have_%';
-- have_innodb = YES 表示可用

解決方案

方案一：轉換表引擎（需鎖表，大表慎重）

ALTER TABLE tbl_name ENGINE=InnoDB

方案二：重建表（大表推薦，可線上操作）

CREATE TABLE tbl_name_new (LIKE tbl_name) ENGINE=InnoDB;
INSERT INTO tbl_name_new SELECT * FROM tbl_name;
RENAME TABLE tbl_name TO tbl_name_old, tbl_name_new TO tbl_name;
DROP TABLE tbl_name_old;

💡 MySQL 5.6+ 可用 ALTER TABLE ... ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE 線上轉換。

方案三：調整應用邏輯（不修改資料庫）

• 將非交易表操作移出交易區塊
• 或接受該表操作不可回滾，設計補償邏輯

最佳實務

• ✅ CREATE TABLE **永遠顯式指定 ENGINE=InnoDB**
• ✅ 設定伺服器預設引擎：default-storage-engine=InnoDB
• ✅ 定期掃描非 InnoDB 表：SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE <> 'InnoDB' AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
• ✅ 遷移專案時檢查 SQL 檔引擎定義