MySQL:關於Bug #81119

gaopengtttt 發表於2019-10-09

修復版本5.7.18,下面是commit,記錄一下後面詳細學習。

故障

image.png

下面是commit

$ git show a43f7d5a6130550619afad3fb513c98e00dc505e
commit a43f7d5a6130550619afad3fb513c98e00dc505e
Author: Daogang Qu <[email protected]>
Date:   Wed Jan 20 09:25:36 2016 +0800
    Bug#22130929  GTID_NEXT AND BEGIN BEHAVIOR IS DIFFERENT B/W BINLOG AND BINLOG-LESS SERVER
    Problem
    =======
    When binlog is enabled, we do not save the gtid specified by
    GTID_NEXT into [email protected]_executed when committing an empty
    transaction caused by a 'BEGIN' command. But we save the gtid
    into [email protected]_executed in the case when binlog is disabled.
    Fix
    ===
    When @@SESSION.GTID_NEXT == 'UUID:NUMBER', the command 'BEGIN' causes
    an error 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET'
    inside an empty/a non-empty transaction regardless of being binlogless
    or not, since it causes an implicit commit like other DDLs. And we do
    not save the gtid specified by GTID_NEXT into [email protected]_executed
    in the case.
diff --git a/mysql-test/extra/binlog_tests/gtid_next_begin_caused_trx.test b/mysql-test/extra/binlog_tests/gtid_next_begin_caused_trx.test
new file mode 100644
index 00000000000..1459bcdf775
--- /dev/null
+++ b/mysql-test/extra/binlog_tests/gtid_next_begin_caused_trx.test
@@ -0,0 +1,76 @@
+# ==== Purpose ====
+#
+# When @@SESSION.GTID_NEXT == 'UUID:NUMBER', verify that the command
+# 'BEGIN' causes an error
+# 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+# an empty/a non-empty transaction, since it causes an implicit
+# commit. We do not save the gtid specified by GTID_NEXT into
+# [email protected]_executed in the case.
+#
+# ==== Implementation ====
+#
+# 1) Set @@SESSION.GTID_NEXT == 'UUID:NUMBER'
+# 2) Execute a 'BEGIN' command to start a transaction.
+# 3) Execute another 'BEGIN' command to check if it causes an error
+#    'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET'
+#    inside an empty transaction, and we do not save the gtid
+#    specified by GTID_NEXT into [email protected]_executed.
+# 4) Execute an 'INSERT' statement.
+# 5) Execute the third 'BEGIN' command to check if it causes an error
+#    'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET'
+#    inside a non-empty transaction, and we do not save the gtid
+#    specified by GTID_NEXT into [email protected]_executed.
+# 6) Check that we save the gtid specified by GTID_NEXT into
+#    [email protected]_executed when committing the transaction.
+#
+# ==== References ====
+#
+# Bug#22130929  GTID_NEXT AND BEGIN BEHAVIOR IS DIFFERENT B/W BINLOG AND BINLOG-LESS SERVER
+# Caller: suite/binlog/t/binlog_gtid_next_begin_caused_trx.test
+# Caller: t/no_binlog_gtid_next_begin_caused_trx.test
+#
+
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
+CREATE TABLE t1 (c1 INT);
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2';
+BEGIN;
+
+--echo #
+--echo # Check that the command 'BEGIN' causes an error
+--echo # 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+--echo # an empty transaction, since it causes an implicit commit. And we
+--echo # do not save the gtid specified by GTID_NEXT into
+--echo # [email protected]_executed in the case.
+--echo #
+--error ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET
+BEGIN;
+--let $assert_text= Dose not commit gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2
+--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1"
+--source include/assert.inc
+
+INSERT INTO t1 VALUES (1);
+--echo #
+--echo # Check that the command 'BEGIN' causes an error
+--echo # 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+--echo # a non-empty transaction, since it causes an implicit commit. And
+--echo # we do not save the gtid specified by GTID_NEXT into
+--echo # [email protected]_executed in the case.
+--echo #
+--error ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET
+BEGIN;
+--let $assert_text= Dose not commit gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2
+--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1"
+--source include/assert.inc
+
+--echo #
+--echo # Check that we save the gtid specified by GTID_NEXT into
+--echo # [email protected]_executed when committing the transaction.
+--echo #
+COMMIT;
+--let $assert_text= Committed gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2
+--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2"
+--source include/assert.inc
+
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
+DROP TABLE t1;
+
diff --git a/mysql-test/r/no_binlog_gtid_next_begin_caused_trx.result b/mysql-test/r/no_binlog_gtid_next_begin_caused_trx.result
new file mode 100644
index 00000000000..44f6216c1b6
--- /dev/null
+++ b/mysql-test/r/no_binlog_gtid_next_begin_caused_trx.result
@@ -0,0 +1,34 @@
+RESET MASTER;
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
+CREATE TABLE t1 (c1 INT);
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2';
+BEGIN;
+#
+# Check that the command 'BEGIN' causes an error
+# 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+# an empty transaction, since it causes an implicit commit. And we
+# do not save the gtid specified by GTID_NEXT into
+# [email protected]_executed in the case.
+#
+BEGIN;
+ERROR HY000: Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
+include/assert.inc [Dose not commit gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2]
+INSERT INTO t1 VALUES (1);
+#
+# Check that the command 'BEGIN' causes an error
+# 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+# a non-empty transaction, since it causes an implicit commit. And
+# we do not save the gtid specified by GTID_NEXT into
+# [email protected]_executed in the case.
+#
+BEGIN;
+ERROR HY000: Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
+include/assert.inc [Dose not commit gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2]
+#
+# Check that we save the gtid specified by GTID_NEXT into
+# [email protected]_executed when committing the transaction.
+#
+COMMIT;
+include/assert.inc [Committed gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2]
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
+DROP TABLE t1;
diff --git a/mysql-test/suite/binlog/r/binlog_gtid_next_begin_caused_trx.result b/mysql-test/suite/binlog/r/binlog_gtid_next_begin_caused_trx.result
new file mode 100644
index 00000000000..44f6216c1b6
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_gtid_next_begin_caused_trx.result
@@ -0,0 +1,34 @@
+RESET MASTER;
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
+CREATE TABLE t1 (c1 INT);
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2';
+BEGIN;
+#
+# Check that the command 'BEGIN' causes an error
+# 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+# an empty transaction, since it causes an implicit commit. And we
+# do not save the gtid specified by GTID_NEXT into
+# [email protected]_executed in the case.
+#
+BEGIN;
+ERROR HY000: Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
+include/assert.inc [Dose not commit gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2]
+INSERT INTO t1 VALUES (1);
+#
+# Check that the command 'BEGIN' causes an error
+# 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+# a non-empty transaction, since it causes an implicit commit. And
+# we do not save the gtid specified by GTID_NEXT into
+# [email protected]_executed in the case.
+#
+BEGIN;
+ERROR HY000: Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
+include/assert.inc [Dose not commit gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2]
+#
+# Check that we save the gtid specified by GTID_NEXT into
+# [email protected]_executed when committing the transaction.
+#
+COMMIT;
+include/assert.inc [Committed gtid aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2]
+SET GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
+DROP TABLE t1;
diff --git a/mysql-test/suite/binlog/t/binlog_gtid_next_begin_caused_trx.test b/mysql-test/suite/binlog/t/binlog_gtid_next_begin_caused_trx.test
new file mode 100644
index 00000000000..dc12cb95fa8
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_gtid_next_begin_caused_trx.test
@@ -0,0 +1,30 @@
+# ==== Purpose ====
+#
+# When binlog is enabled and @@SESSION.GTID_NEXT == 'UUID:NUMBER',
+# verify that the command 'BEGIN' causes an error
+# 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+# an empty/a non-empty transaction, since it causes an implicit
+# commit. We do not save the gtid specified by GTID_NEXT into
+# [email protected]_executed in the case.
+#
+# ==== Implementation ====
+#
+# See extra/binlog_tests/gtid_next_begin_caused_trx.test
+#
+# ==== References ====
+#
+# Bug#22130929  GTID_NEXT AND BEGIN BEHAVIOR IS DIFFERENT B/W BINLOG AND BINLOG-LESS SERVER
+#
+
+# Test in this file is binlog format agnostic, thus no need
+# to rerun them for every format.
+--source include/have_binlog_format_row.inc
+--source include/have_gtid.inc
+# Test is not required to run against embedded server
+--source include/not_embedded.inc
+
+# Make sure the test is repeatable
+RESET MASTER;
+
+--source extra/binlog_tests/gtid_next_begin_caused_trx.test
+
diff --git a/mysql-test/t/no_binlog_gtid_next_begin_caused_trx-master.opt b/mysql-test/t/no_binlog_gtid_next_begin_caused_trx-master.opt
new file mode 100644
index 00000000000..f3fd62da9b7
--- /dev/null
+++ b/mysql-test/t/no_binlog_gtid_next_begin_caused_trx-master.opt
@@ -0,0 +1,2 @@
+--gtid_mode=ON
+--enforce_gtid_consistency
diff --git a/mysql-test/t/no_binlog_gtid_next_begin_caused_trx.test b/mysql-test/t/no_binlog_gtid_next_begin_caused_trx.test
new file mode 100644
index 00000000000..71f9833827d
--- /dev/null
+++ b/mysql-test/t/no_binlog_gtid_next_begin_caused_trx.test
@@ -0,0 +1,28 @@
+# ==== Purpose ====
+#
+# When binlog is disabled and @@SESSION.GTID_NEXT == 'UUID:NUMBER',
+# verify that the command 'BEGIN' causes an error
+# 'ER_CANT_DO_IMPLICIT_COMMIT_IN_TRX_WHEN_GTID_NEXT_IS_SET' inside
+# an empty/a non-empty transaction, since it causes an implicit
+# commit. We do not save the gtid specified by GTID_NEXT into
+# [email protected]_executed in the case.
+#
+# ==== Implementation ====
+#
+# See extra/binlog_tests/gtid_next_begin_caused_trx.test
+#
+# ==== References ====
+#
+# Bug#22130929  GTID_NEXT AND BEGIN BEHAVIOR IS DIFFERENT B/W BINLOG AND BINLOG-LESS SERVER
+#
+
+
+# Test is not required to run against embedded server
+--source include/not_embedded.inc
+# Should be tested against "binlog disabled" server
+--source include/not_log_bin.inc
+
+# Make sure the test is repeatable
+RESET MASTER;
+
+--source extra/binlog_tests/gtid_next_begin_caused_trx.test
diff --git a/sql/rpl_gtid_execution.cc b/sql/rpl_gtid_execution.cc
index ec36772e8c9..65161e2ca97 100644
--- a/sql/rpl_gtid_execution.cc
+++ b/sql/rpl_gtid_execution.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2011, 2015, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2011, 2016, Oracle and/or its affiliates. All rights reserved.
    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU General Public License as
@@ -453,7 +453,8 @@ enum_gtid_statement_status gtid_pre_statement_checks(THD *thd)
   DBUG_ASSERT(gtid_next->type != AUTOMATIC_GROUP ||
               thd->owned_gtid.is_empty());
-  if (stmt_causes_implicit_commit(thd, CF_IMPLICIT_COMMIT_BEGIN) &&
+  if ((stmt_causes_implicit_commit(thd, CF_IMPLICIT_COMMIT_BEGIN) ||
+       thd->lex->sql_command == SQLCOM_BEGIN) &&
       thd->in_active_multi_stmt_transaction() &&
       gtid_next->type == GTID_GROUP)
   {

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-2659191/,如需轉載,請註明出處,否則將追究法律責任。


相關文章

資料庫|MySQL

MySQL 資料庫相關流程圖 / 原理圖

MySQL 資料庫相關流程圖 / 原理圖
MySQL

MySQL 索引和 SQL 調優總結

MySQL索引MySQL支援諸多儲存引擎,而各種儲存引擎對索引的支援也各不相同,因此MySQL資料庫支援多種索引型別,如BTree索引,雜湊索引,全文索引等等。為了避免混亂,本文將只關注於BTree索
面試|MySQL

面試官出的MySQL索引問題,這篇文章全給你解決!

原文連結:blog.ouyangsihai.cn &gt;&gt; MySQL的B+樹索引的概念、使用、優化及使用場景 0 前言這篇文章不會講解索引的基礎知識,主要是關於MySQL資料庫的B+樹索引的
MySQL

​MySQL 一個讓你懷疑人生的hang死現象

1、故障現象某客戶反饋,在利用binlog日誌的解析內容做基於時間點的恢復時,出現hang死(超過12小時那種),恢復過程無法繼續,而且100%重現基於時間點恢復的實現方式為:使用mysqlbinlo
MySQL

MySQL 執行DDL語句 hang住了怎麼辦?

MySQL 執行DDL語句 hang住了怎麼辦? 不要慌,先點支菸,聽我娓娓道來!前兩天,早上7點多的時候,抓起手機忽然看到了圈內的一則DDL語句 hang住的案例,閱讀到文末,發現文中留有一些疑問(
MySQL|加密

mysql5.7 ssl加密連線

該操作在mysql5.7及以後版本,5.6及以前版本不適用該操作確認資料庫版本號和埠號mysql&gt; select version();+-----------+| version() |+---
MySQL

記一次 K3s MySQL 啟動 OOM 排查

之前的用Docker Compose做開發環境,MySQL也在Docker裡執行,一切使用正常。後來開發環境遷移到K3s(輕量級的K8s),但是MySQL一啟動就被OOM Killer幹掉,所以一直沒
MySQL

MySQL:一個innodb_thread_concurrency設定不當引發的故障

原始碼版本:5.7.22一、問題來源歡迎關注我的《深入理解MySQL主從原理 32講 》,如下:這是一個朋友問我的典型案例。整個故障現象表現為,MySQL資料庫頻繁的出現大量的請求不能響應。下面是一些
MySQL|加密

連線 MySQL 8.0 時,加密方式不相容的解決方法

問題laravel專案使用MySQL 8.0資料庫(放在docker裡面),執行php artisan migrate後報錯:Illuminate\Database\QueryException :
MySQL

揭祕MySQL 主從環境中大事務的傳奇事蹟

toc或許某些人會說MySQL Low,而且樂此不疲,不過,我完全也可以說某些人壓根就不會用MySQL,萬事萬物都具有兩面性的,最終結果的好壞還得看是誰在用、怎麼用。關於這個問題的爭論,相信大家都喜聞