在一套MySQL MGR集群測試環(huán)境中，同事測試時，在my.cnf參數(shù)文件中修改了autocommit參數(shù)（修改為autocommit=0），結(jié)果上周五，由于系統(tǒng)管理員要升級RHEL 8.8的系統(tǒng)補(bǔ)丁，所以將這這三臺MySQL的數(shù)據(jù)庫服務(wù)關(guān)閉了，升級完RHEL 8.8的系統(tǒng)補(bǔ)丁后，啟動MySQL的集群時遇到了“ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction”錯誤

當(dāng)前測試環(huán)境為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

當(dāng)時看到錯誤ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction時，心里隱隱猜測可能是這個參數(shù)的變更導(dǎo)致了這個錯誤出現(xiàn)，因?yàn)橹岸啻螠y試過MGR集群的重啟、切換主節(jié)點(diǎn)、刪除節(jié)點(diǎn)等都沒遇到問題，而最近就是因?yàn)橄嚓P(guān)測試修改了這個參數(shù)，于是將事務(wù)自動提交開啟（autocommit=1）后，問題解決了。后面搜索了一下相關(guān)資料，發(fā)現(xiàn)參數(shù)autocommit還真的會引起這個錯誤，下面是官方文檔^[1]中用戶反饋/報(bào)告的一個"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

參考資料