国产 无码 综合区,色欲AV无码国产永久播放,无码天堂亚洲国产AV,国产日韩欧美女同一区二区

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

這篇具有很好參考價(jià)值的文章主要介紹了MySQL 8的MGR集群中設(shè)置autocommit=0引起ERROR 1064 (42000)錯誤。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方,請大家不吝賜教,您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問。

在一套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

參考資料

[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文章來源地址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)!

本文來自互聯(lián)網(wǎng)用戶投稿,該文觀點(diǎn)僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務(wù),不擁有所有權(quán),不承擔(dān)相關(guān)法律責(zé)任。如若轉(zhuǎn)載,請注明出處: 如若內(nèi)容造成侵權(quán)/違法違規(guī)/事實(shí)不符,請點(diǎn)擊違法舉報(bào)進(jìn)行投訴反饋,一經(jīng)查實(shí),立即刪除!

領(lǐng)支付寶紅包贊助服務(wù)器費(fèi)用

相關(guān)文章

  • SQL 錯誤 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to yo

    在為用戶指定數(shù)據(jù)的時候,報(bào)錯了,SQL 錯誤 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to yo 原因 出現(xiàn)這個錯誤是因?yàn)閿?shù)據(jù)庫名稱 jeecg-boot 包含了不允許的字符(如連字符-)。根據(jù) MySQL 文檔,數(shù)據(jù)庫名、表名和列名等標(biāo)識符應(yīng)該只包含字母、數(shù)字、下

    2024年04月17日
    瀏覽(21)
  • 記錄DBeaver報(bào)SQL 錯誤 [1064] [42000]: #42000的一個坑

    記錄DBeaver報(bào)SQL 錯誤 [1064] [42000]: #42000的一個坑

    在這里插入圖片描述 一般情況下報(bào)SQL 錯誤 [1064] [42000]: #42000的錯誤是因?yàn)镾QL語句中 有沖突 。 但是?。?! 但是?。?! 但是!??! DBeaver在執(zhí)行SQL語句時,如果 超過兩條同時執(zhí)行 的話,必須要使用“ 執(zhí)行SQL腳本 ”。 使用“執(zhí)行SQL語句”選項(xiàng)的話就會報(bào)42000錯誤!

    2024年02月12日
    瀏覽(56)
  • StarRocks案例5: hive外表報(bào)錯starrocks [42000][1064] hdfsOpenFile failed

    StarRocks版本 2.5.5 現(xiàn)在需要使用hive外表,然后根據(jù)官網(wǎng)的的命令創(chuàng)建了hive resource CREATE EXTERNAL RESOURCE “hive0” PROPERTIES ( “type” = “hive”, “hive.metastore.uris” = “thrift://myhadoop:9083” ); 然后查詢報(bào)錯: StarRocks的committer在論壇回復(fù)我了,原來是打包的問題。 沒想到打包名字居然是

    2024年02月09日
    瀏覽(16)
  • MySQL Shell如何接管手動搭建(含仲裁節(jié)點(diǎn))MGR集群

    MySQL Shell如何接管手動搭建(含仲裁節(jié)點(diǎn))MGR集群

    本文源自GreatSQL社區(qū)用戶的一次提問: Q:一個包含仲裁節(jié)點(diǎn)( ARBITRATOR )的GreatSQL MGR集群,一開始是用手動方式構(gòu)建,后來想用MySQL Shell接管,可以嗎? A:是可以的,不過也有一定局限性 具體的操作如下 可以看到三個節(jié)點(diǎn)都是 ONLINE 狀態(tài) 連接 Primary 節(jié)點(diǎn),查看下原來的賬戶

    2024年02月05日
    瀏覽(18)
  • 已解決mysql報(bào)錯ERROR 1049 (42000): Unknown database ‘?dāng)?shù)據(jù)庫‘

    已解決mysql報(bào)錯ERROR 1049 (42000): Unknown database ‘?dāng)?shù)據(jù)庫‘ 對于錯誤代碼1049(42000):Unknown database ‘?dāng)?shù)據(jù)庫‘,這個錯誤通常表示您正在嘗試訪問一個不存在的數(shù)據(jù)庫。 下滑查看解決方法 要解決這個問題,您可以按照以下步驟進(jìn)行操作: 確認(rèn)數(shù)據(jù)庫名稱:首先,請確保您正在

    2024年02月16日
    瀏覽(19)
  • 登陸mysql提示 :ERROR 1044 (42000): Access denied for user ‘‘@‘localhost‘ to database ‘mysql‘

    再次登陸mysql如提示 : ERROR 1044 (42000): Access denied for user \\\'\\\'@\\\'localhost\\\' to database \\\'mysql\\\' 這個錯誤是因?yàn)閙ysql數(shù)據(jù)庫的user表里,存在用戶名為空的賬戶即匿名賬戶,實(shí)際上是匿名登錄的, 通過錯誤提示里的\\\'\\\'@\\\'localhost\\\'可以看出來 ,雖然登錄時使用命令mysql -u root -p ,指定了用戶是r

    2024年02月16日
    瀏覽(45)
  • MySQL報(bào)錯:ERROR 1118 (42000): Row size too large. 或者 Row size too large (> 8126).

    MySQL報(bào)錯:ERROR 1118 (42000): Row size too large. 或者 Row size too large (> 8126).

    今天拿到一個建語句時,大概二百多個字段,然后大部分類型是 string 的,要求建 MySQL 的表。首先將 string 替換為 varchar(xx),然后執(zhí)行了一下語句,報(bào)錯如下所示: ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhe

    2023年04月09日
    瀏覽(20)
  • mysql:Error 3948 (42000): Loading local data is disabled; this must be enabled on both the client an

    mysql:Error 3948 (42000): Loading local data is disabled; this must be enabled on both the client an

    上面圖片是AI創(chuàng)作,未經(jīng)允許,不可商用哦!如有更多需要,可私戳! 執(zhí)行項(xiàng)目過程中意外出現(xiàn)的報(bào)錯,之前也沒有遇到過 報(bào)錯信息如下: 翻譯如下: 看報(bào)錯信息感覺是和數(shù)據(jù)庫有一定關(guān)系,網(wǎng)上搜索該錯誤,也都直指mysql中的一個參數(shù): local_infile 需要指定該參數(shù)為開啟狀

    2024年02月02日
    瀏覽(17)
  • pycharm中的python與mysql(1064):“You have an error in your SQL syntax; check the manual that corresponds

    1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \\\'‘director’,‘star’,‘score’) values (‘奧利 維?!ぜ{卡什’,‘弗朗索瓦·克é’ at line 1” 這個錯誤原因是在pycharm里敲代碼時,將python語法與mysql語法混淆了。 mysql語法

    2024年02月16日
    瀏覽(28)
  • 錯誤代碼: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL ser

    錯誤代碼: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL ser

    ????? ???翻譯一下就是: SQL語法有錯誤;查看與您的MySQL服務(wù)器版本相對應(yīng)的手冊,了解“……”附近要使用的正確語法。 ??????? 會出現(xiàn)這種問題,大部分都是因?yàn)樽约旱拇中拇笠饫瞺 第一步: ??????? 先檢查一下自己寫的SQL語句中的符號是否有中文的,有的話改

    2024年02月12日
    瀏覽(22)

覺得文章有用就打賞一下文章作者

支付寶掃一掃打賞

博客贊助

微信掃一掃打賞

請作者喝杯咖啡吧~博客贊助

支付寶掃一掃領(lǐng)取紅包,優(yōu)惠每天領(lǐng)

二維碼1

領(lǐng)取紅包

二維碼2

領(lǐng)紅包