【MySQL】部分5.6版本罕見覆制報錯 ERROR 1837
場景:
1、簡單的一主一從,版本MySQL-5.6.20
2、master_auto_position=0
3、開啟gtid
報錯如下:
Last_SQL_Errno: 1837
Last_SQL_Error: Error 'When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'c44bd915-440d-11e6-8ea0-6c92bf24b8c0:71844624'.' on query. Default database: '$db'. Query: 'DELETE FROM `db2`.`tb2`'
看完報錯一臉懵逼,莫非主庫在做什麼騷操作?
檢查一下主庫binlog對應的GTID點,可以發現點什麼:
(已做資料脫敏,如上兩張表分別用db1.tb1和db2.tb2來區分)
發現到GTID為【c44bd915-440d-11e6-8ea0-6c92bf24b8c0:71844624】和【71844625】之間做了如下操作:
-
USE db3;
-
DELETE FROM `db1`.`tb1`;
- DELETE FROM `db2`.`tb2`;
DELETE FROM `db_1`.`t1`與DELETE FROM `db_2`.`t2`之間並沒有更多的:SET @@SESSION.GTID_NEXT。
這似乎就違反了GTID的限制,一個事務應該對應一個GTID號才對。
結合報錯資訊,懷疑此時在執行到第二個DELETE時,因為第二個DELETE沒有對應的GTID_NEXT,就報錯了:
- Last_SQL_Errno: 1837 …… Default database: '$db'. Query: 'DELETE FROM `db2`.`tb2`'
可能與這個有關。
此處,為了修復這個複製故障,在從庫上做如下操作:
-
〇 SET SESSION sql_log_bin=0;
-
〇 手動執行未執行的事務,此處為:DELETE FROM `db2`.`tb2`;
-
〇 SET SESSION sql_log_bin=1;
- 〇 STOP SLAVE sql_thread; SET @@SESSION.GTID_NEXT= 'AUTOMATIC'; START SLAVE sql_thread;
至於為什麼在ENFORCE_GTID_CONSISTENCY為ON的情況下,產生這樣違反GTID的events,我搜了一下bug庫:
更多討論如下:
並在5.6.21以後的版本修復了這個問題。
在文件中找到:
- Replication: When mysqlbinlog processed multiple binary log files into a single output file, this file was not in a useful
- state for point-in-time recovery, when it failed with the error, When @@SESSION.GTID_NEXT is set to a GTID, you must
- explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for
- detailed explanation. Current @@SESSION.GTID_NEXT is 'xyz'. When mysqlbinlog processes a binary log containing GTIDs,
- it outputs SET gtid_next statements, but gtid_next is set to undefined whenever a commit occurs; this left gtid_next
- undefined when the server had finished processing the output from mysqlbinlog. When the next binary log file started
- with one or more anonymous statements or transactions, the combination of gtid_next being left undefined at the end
- of the first binary log and the second binary log containing anonymous transactions to the error described previously
-
(Error 1837, ER_GTID_NEXT_TYPE_UNDEFINED_GROUP).
-
- To fix this issue, now, whenever mysqlbinlog encounters this situation, it inserts SET gtid_next = AUTOMATIC
-
if required to avoid leaving the previous binary log with gtid_next undefined.
-
- In addition, as a result of this fix, mysqlbinlog no longer outputs session variable information for every binary log;
- now, this value is printed only once unless it changes. (Bug #18258933, Bug #71695)
大致原因是:
當mysqlbinlog處理包含GTID的binlog時,它會輸出gtid_next,但是當提交時,gtid_next會被設定為“undefined”。
當伺服器處理完來自mysqlbinlog的輸出後,就留下了binlog undefined。
簡單的來說:
因為gtid_next可能會被設定為undefined,導致複製出現1837。
為了修復這個問題,在MySQL5.6.21版本中,做出瞭如下修復:
每當mysqlbinlog遇到這種情況,會自動加入如下語句:
- “SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;”
(這個可以在開啟GTID時,輕易測試得出)
後來又搜了一下,在使用INSERT DELAYED語法時,也可能出現這個問題,更多可以參考2014年9月的淘寶核心月報:
http://blog.csdn.net/longxibendi/article/details/39297025
(在上已經找不到2014年9月的月報了
不知道阿里的同學是不是偷偷把之前的藏起來了。)
雖然文件描述好像和這個case不太像,但總之也是有收穫的:
〇 升級到更高版本的MySQL。
〇 儘量使用事務引擎,避免在一個事務中同時操作事務表和非事務表的可能性。
(雖然enforce_gtid_consistency開啟,但也有可能出現突破GTID限制的語句,儘量從業務上限制)
〇 儘量避免使用INSERT DELAYED語法。
參考:
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-21.html
話說某雲端計算廠商控制檯上建立的MySQL 5.6就是5.6.20……
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29773961/viewspace-2147627/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 5.6複製報錯Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;MySqlError
- MySQL5.6:mysql_secure_installation 報錯ERROR 2002 (HY000)MySqlError
- mysql5.6 mysqldump備份報錯MySql
- MYSQL SOURCE報錯 ERROR: ASCIIMySqlErrorASCII
- MySQL 主從複製錯誤1837MySql
- mysql報錯ERROR 1093MySqlError
- Percona MySQL 5.6 語句加鎖報錯"ERROR 1665 (HY000): Cannot execute statement"MySqlError
- MySQL報錯 Error_code: 1045MySqlError
- BCG罕見病產業報告: 產業發展篇產業
- BCG:罕見病產業報告 –產業價值篇產業
- 【MySQL】老版本mysql奇怪的報錯:ERROR 1046 (3D000): No database selected(BUG)MySqlError3DDatabase
- MySQL 5.6 GTID常見錯誤解決一例MySql
- Percona MySQL 5.6 主主複製環境報錯"Got fatal error 1236 from master.."MySqlGoErrorAST
- MySQL RC隔離級別下罕見的gap lockMySql
- MySQL 5.6執行DDL報錯ERROR 1050 (42S01): Table 'sms/#sql-ib752' already existMySqlError
- mysql8.0 部分sql語法報錯問題MySql
- Mysql報錯Fatal error:Can't open and lock privilege tablesMySqlError
- 【MySQL】5.6.x InnoDB Error Table mysql.innodb_table_stats not foundMySqlError
- 阿里健康升級“罕見病中心” 為2000萬罕見病患者找藥阿里
- MySQL error 錯 誤 碼MySqlError
- vipca報錯 Error 0PCAError
- 【Mysql】閃回--mysqlbinlog flashback 5.6版本MySql
- ubuntu mysql5.6二進位制安裝UbuntuMySql
- MySQL 5.6修復從庫複製時報錯'ERROR 1872 (HY000): Slave failed to initialize'MySqlErrorAI
- MySQL 5.6初始化資料庫時報錯FATAL ERROR: Could not find ./bin/my_print_defaultsMySql資料庫Error
- Mysql資料庫報ERROR 1045 (28000)報錯及MySQL忘記密碼找回MySql資料庫Error密碼
- ERROR 1045 (28000): ProxySQL Error: 報錯ErrorSQL
- MySQL 5.7.17 安裝報錯SSL error: Unable to get private key fromMySqlError
- MySQL 5.5 原始碼安裝報錯"[ERROR] Can't start server"MySql原始碼ErrorServer
- cacti匯入模板報錯Error: XML:雜湊版本不存在.ErrorXML
- MySQL5.6版本的新特性介紹MySql
- MySQL 8.0版本連線報錯:Could not create coMySql
- 關於Mysql5.7高版本group by新特性報錯MySql
- MySQL 常見錯誤MySql
- MySQL報錯Table 'plugin' is read only [ERROR] Can't open the mysql.plugin table.MySqlPluginError
- 蛋殼研究院:中國罕見病行業研究報告(附下載)行業
- MySQL 啟動報錯 error while loading shared librariesMySqlErrorWhile
- TypeScript常見報錯TypeScript