在一套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"文章來源:http://www.zghlxwxcb.cn/news/detail-493782.html
[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
參考資料
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文章來源地址http://www.zghlxwxcb.cn/news/detail-493782.html
到了這里,關(guān)于MySQL 8的MGR集群中設(shè)置autocommit=0引起ERROR 1064 (42000)錯誤的文章就介紹完了。如果您還想了解更多內(nèi)容,請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章,希望大家以后多多支持TOY模板網(wǎng)!