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

参考资料

[1]

Bug #97917: https://bugs.mysql.com/bug.php?id=97917#:~:text=How%20to%20repeat%3A%20In%20the%20same%20connection%2C%20run%3A,active%20transaction%20%5B9%20Dec%202019%208%3A46%5D%20Sergey%20Kuzmichev