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，具体操作如下所示：

1. mysql> show variables like 'group_replication_bootstrap_group';
   
2. +-----------------------------------+-------+
   
3. | Variable_name                     | Value |
   
4. +-----------------------------------+-------+
   
5. | group_replication_bootstrap_group | OFF   |
   
6. +-----------------------------------+-------+
   
7. 1 row in set (0.02 sec)
   
8. 
   
9. mysql> set global group_replication_bootstrap_group=on;
   
10. Query OK, 0 rows affected (0.00 sec)
    
11. 
    
12. mysql>  show variables like 'group_replication_bootstrap_group';
    
13. +-----------------------------------+-------+
    
14. | Variable_name                     | Value |
    
15. +-----------------------------------+-------+
    
16. | group_replication_bootstrap_group | ON    |
    
17. +-----------------------------------+-------+
    
18. 1 row in set (0.00 sec)
    
19. 
    
20. mysql> select * from performance_schema.replication_group_members;
    
21. +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
    
22. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    
23. +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
    
24. | group_replication_applier | f8eea283-c942-11ed-a4e9-0050569783ac | mydbmysqlu03 |        7306 | OFFLINE      |             |                | MySQL                      |
    
25. +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
    
26. 1 row in set (0.00 sec)
    
27. 
    
28. mysql> START GROUP_REPLICATION;
    
29. ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
    
30. mysql> START GROUP_REPLICATION;
    
31. ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
    
32. mysql> SET autocommit=1;
    
33. Query OK, 0 rows affected (0.00 sec)
    
34. 
    
35. mysql> START GROUP_REPLICATION;
    
36. Query OK, 0 rows affected (1.60 sec)
    
37. 
    
38. mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
    
39. Query OK, 0 rows affected (0.00 sec)
    
40. 
    
41. mysql>  select * from performance_schema.replication_group_members;
    
42. +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
    
43. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    
44. +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
    
45. | group_replication_applier | f8eea283-c942-11ed-a4e9-0050569783ac | mydbmysqlu03 |        7306 | ONLINE       | PRIMARY     | 8.0.33         | MySQL                      |
    
46. +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+----------------------------+
    
47. 1 row in set (0.00 sec)
    
48. 
    
49. 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"

1. [6 Dec 2019 15:23] Sergey Kuzmichev
   
2. Description:
   
3. 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.
   
4. Error reported is:
   
5. ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
   
6. 
   
7. This has impact on mysql shell, as it will fail to remove an instance from the cluster under some circumstances.
   
8. 
   
9. How to repeat:
   
10. In the same connection, run:
    
11. 
    
12. SET autocommit=0;
    
13. SELECT * FROM mysql.slave_master_info;
    
14. START GROUP_REPLICATION;
    
15. 
    
16. Error reported:
    
17. 
    
18. ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
    
19. [9 Dec 2019 8:46] Sergey Kuzmichev
    
20. 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.
    
21. 
    
22. MySQL Shell, however, might end up not being able to remove an instance due to this, but that's a different issue.
    
23. [10 Dec 2019 10:56] Sergey Kuzmichev
    
24. 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?
    
25. 
    
26. #autocommit=0
    
27. mysql> select * from test;
    
28. Empty set (0.00 sec)
    
29. 
    
30. mysql> start group_replication;
    
31. ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
    
32. mysql> start slave;
    
33. 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.
    
34. mysql> start group_replication;
    
35. ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
    
36. [10 Dec 2019 13:05] MySQL Verification Team
    
37. Hi,
    
38. 
    
39. 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.
    
40. 
    
41. 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
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源:https://www.cnblogs.com/kerrycode/p/17496608.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！