分析MySQL中关于Bug的解决方法

51次阅读
没有评论

共计 11421 个字符，预计需要花费 29 分钟才能阅读完成。

这篇文章主要介绍“分析 MySQL 中关于 Bug 的解决方法”，在日常操作中，相信很多人在分析 MySQL 中关于 Bug 的解决方法问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”分析 MySQL 中关于 Bug 的解决方法”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

故障

下面是 commit

$ git show a43f7d5a6130550619afad3fb513c98e00dc505e
commit a43f7d5a6130550619afad3fb513c98e00dc505e
Author: Daogang Qu  bill.qu@oracle.com 
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 GLOBAL@gtid_executed when committing an empty
 transaction caused by a  BEGIN  command. But we save the gtid
 into GLOBAL@gtid_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 GLOBAL@gtid_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
+# GLOBAL@gtid_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 GLOBAL@gtid_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 GLOBAL@gtid_executed.
+# 6) Check that we save the gtid specified by GTID_NEXT into
+# GLOBAL@gtid_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 # GLOBAL@gtid_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 # GLOBAL@gtid_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 # GLOBAL@gtid_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
+# GLOBAL@gtid_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
+# GLOBAL@gtid_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
+# GLOBAL@gtid_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
+# GLOBAL@gtid_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
+# GLOBAL@gtid_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
+# GLOBAL@gtid_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
+# GLOBAL@gtid_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
+# GLOBAL@gtid_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)
 {

到此，关于“分析 MySQL 中关于 Bug 的解决方法”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！

正文完
binlog bug gtid mysql when
发表至： 数据库
2023-07-26
 
丸趣
版权声明：本站原创文章，由 丸趣 2023-07-26发表，共计11421字。
转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。
CRM运行该怎么解决
navicat连接数据库文件的示例
什么是非关系型数据库MongoDB
怎么处理Docker mysql容器升级到mysql8遇到的问题
评论（没有评论）