MySQL 8的MGR集群中設定autocommit=0引起ERROR 1064 (42000)錯誤

在一套MySQL MGR集群測驗環境中，同事測驗時，在my.cnf引數檔案中修改了autocommit引數（修改為autocommit=0），結果上周五，由于系統管理員要升級RHEL 8.8的系統補丁，所以將這這三臺MySQL的資料庫服務關閉了，升級完RHEL 8.8的系統補丁后，啟動MySQL的集群時遇到了“ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction”錯誤

當前測驗環境為MySQL 8.0.33，具體操作如下所示：

mysql> show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.02 sec)

mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | ON    |
+-----------------------------------+-------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | f8eea283-c942-11ed-a4e9-0050569783ac | mydbmysqlu03 |        7306 | OFFLINE      |             |                | MySQL                      |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)

mysql> START GROUP_REPLICATION;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
mysql> START GROUP_REPLICATION;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
mysql> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.60 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | f8eea283-c942-11ed-a4e9-0050569783ac | mydbmysqlu03 |        7306 | ONLINE       | PRIMARY     | 8.0.33         | MySQL                      |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)

mysql> exit

當時看到錯誤ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction時，心里隱隱猜測可能是這個引數的變更導致了這個錯誤出現，因為之前多次測驗過MGR集群的重啟、切換主節點、洗掉節點等都沒遇到問題，而最近就是因為相關測驗修改了這個引數，于是將事務自動提交開啟（autocommit=1）后，問題解決了，后面搜索了一下相關資料，發現引數autocommit還真的會引起這個錯誤，下面是官方檔案^[1]中用戶反饋/報告的一個"Bug"

[6 Dec 2019 15:23] Sergey Kuzmichev
Description:
With autocommit=0, after running a SELECT on mysql.slave_master_info, neither START GROUP_REPLICATION nor STOP GROUP_REPLICATION will work in the same connection.
Error reported is:
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

This has impact on mysql shell, as it will fail to remove an instance from the cluster under some circumstances.

How to repeat:
In the same connection, run:

SET autocommit=0;
SELECT * FROM mysql.slave_master_info;
START GROUP_REPLICATION;

Error reported:

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
[9 Dec 2019 8:46] Sergey Kuzmichev
Since with autocommit=0 transaction is always open for a session, the error will be reported for start/stop after any table is queried and transaction is actually started. This is not a bug.

MySQL Shell, however, might end up not being able to remove an instance due to this, but that's a different issue.
[10 Dec 2019 10:56] Sergey Kuzmichev
After some further consideration, it's still at least curious that start/stop group_replication behaves differently than regular start/stop slave does. The latter command will implicitly commit the transaction. Is there a reason for the difference in behavior?

#autocommit=0
mysql> select * from test;
Empty set (0.00 sec)

mysql> start group_replication;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
mysql> start slave;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
[10 Dec 2019 13:05] MySQL Verification Team
Hi,

Thanks for the report, verified as described. Can't say really if the code or only documentation will change as the workaround is rather simple.

thanks

參考資料