Hi, we have another problem that appears with cluster all the time, for only certain table.
After setting up 3-nodes cluster, we are trying to use pt-online-schema-change tool to no-matter-what (for instance, we can just run alter “engine=InnoDB” to do nothing with table structure in fact), but shortly after, our cluster becomes stuck with pause:
Logs
pxc1
2022-03-05T17:46:00.873432Z 7505364 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=4 solved by abort
2022-03-05T17:46:00.873482Z 7505451 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=4 solved by abort
2022-03-05T17:46:00.873674Z 7505466 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.873717Z 7505470 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.873818Z 7505446 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.873852Z 7505446 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.874614Z 7505454 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.874661Z 7505454 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.875248Z 7505446 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.875365Z 7505467 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.875413Z 7505467 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.875431Z 7505467 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.875463Z 7505364 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2022-03-05T17:46:00.875487Z 7505364 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:
2022-03-05T17:46:00.875498Z 7505364 [Note] [MY-000000] [WSREP] Winning thread:
THD: 7505364, mode: toi, state: exec, conflict: aborted, seqno: 215655032
SQL: CREATE TRIGGER `pt_osc_xxxxxxxx_users_del` AFTER DELETE ON `xxxxxxxx`.`users` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `xxxxxxxx`.`_users_new` WHERE `xxxxxxxx`.`_users_new`.`user_id` <=> OLD.`user_id`; END
2022-03-05T17:46:00.875514Z 7505364 [Note] [MY-000000] [WSREP] Victim thread:
THD: 7505472, mode: local, state: exec, conflict: aborted, seqno: -1
SQL: SELECT field FROM `xxxxxxxx`.`users` WHERE `user_id` = 'x'
2022-03-05T17:46:00.875598Z 7505472 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.875607Z 7505472 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=10 solved by abort
2022-03-05T17:46:00.875616Z 7505472 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=8 solved by abort
2022-03-05T17:46:00.875625Z 7505472 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=10 solved by abort
2022-03-05T17:46:00.882388Z 0 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=10 solved by abort
2022-03-05T17:46:00.892544Z 7505469 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=10 solved by abort
2022-03-05T17:46:00.909535Z 7505364 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2022-03-05T17:46:00.909554Z 7505364 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:
2022-03-05T17:46:00.909562Z 7505364 [Note] [MY-000000] [WSREP] Winning thread:
THD: 7505364, mode: toi, state: exec, conflict: aborted, seqno: 215655044
SQL: CREATE TRIGGER `pt_osc_xxxxxxxx_users_upd`
........
pxc2
2022-03-05T17:46:02.525997Z 0 [Note] [MY-000000] [Galera] declaring 65d2b994-bce4 at ssl://xx.xxx.xxx.199:4567 stable
2022-03-05T17:46:02.526161Z 0 [Note] [MY-000000] [Galera] forgetting 95da07cd-9470 (ssl://xx.xxx.xxx.201:4567)
2022-03-05T17:46:02.526839Z 0 [Note] [MY-000000] [Galera] Node 2df1acc3-b163 state primary
2022-03-05T17:46:02.527012Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view (view_id(PRIM,2df1acc3-b163,10)
memb {
2df1acc3-b163,0
65d2b994-bce4,0
}
joined {
}
left {
}
partitioned {
95da07cd-9470,0
}
)
2022-03-05T17:46:02.527032Z 0 [Note] [MY-000000] [Galera] Save the discovered primary-component to disk
2022-03-05T17:46:02.530148Z 0 [Note] [MY-000000] [Galera] forgetting 95da07cd-9470 (ssl://xx.xxx.xxx.201:4567)
2022-03-05T17:46:02.530368Z 0 [Note] [MY-000000] [Galera] New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2
pxc3
2022-03-05T17:46:00.904407Z 20 [Note] [MY-000000] [WSREP] MDL conflict db=xxxxxxxx table=users ticket=1 solved by abort
2022-03-05T17:46:02.518296Z 11 [Note] [MY-011825] [InnoDB] *** Priority TRANSACTION:
TRANSACTION 83020623739, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT , undo log entries 3
MySQL thread id 11, OS thread handle 140358281434880, query id 54881213 wsrep: writing row for write-set (215655578)
2022-03-05T17:46:02.518444Z 11 [Note] [MY-011825] [InnoDB] *** Priority TRANSACTION:
TRANSACTION 83020623738, ACTIVE (PREPARED) 0 sec
, undo log entries 2
MySQL thread id 13, OS thread handle 140358281139968, query id 54881211 wsrep: preparing to commit write set(215655579)
2022-03-05T17:46:02.518501Z 11 [Note] [MY-011825] [InnoDB] *** WAITING FOR THIS LOCK TO BE GRANTED:
2022-03-05T17:46:02.518518Z 11 [Note] [MY-011825] [InnoDB] SQL1:
2022-03-05T17:46:02.518533Z 11 [Note] [MY-011825] [InnoDB] SQL2:
2022-03-05T17:46:02.518554Z 11 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2022-03-05T17:46:02.518572Z 11 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:
2022-03-05T17:46:02.518590Z 11 [Note] [MY-000000] [WSREP] Winning thread:
THD: 11, mode: high priority, state: exec, conflict: executing, seqno: 215655578
SQL: (null)
2022-03-05T17:46:02.518606Z 11 [Note] [MY-000000] [WSREP] Victim thread:
THD: 13, mode: high priority, state: exec, conflict: committing, seqno: 215655579
SQL: (null)
2022-03-05T17:46:02.521458Z 11 [Note] [MY-011825] [InnoDB] Killed transaction: ID: 0 in hit list - MySQL thread id 13, OS thread handle 140358281139968, query id 54881211 wsrep: preparing to commit write set(215655579)
2022-03-05T17:46:02.521502Z 11 [Note] [MY-011825] [InnoDB] WSREP seqnos, BF: 215655578, victim: 215655579
2022-03-05T17:46:02.521523Z 11 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2022-03-05T17:46:02.521540Z 11 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:
2022-03-05T17:46:02.521557Z 11 [Note] [MY-000000] [WSREP] Winning thread:
THD: 11, mode: high priority, state: exec, conflict: executing, seqno: 215655578
SQL: (null)
2022-03-05T17:46:02.521592Z 11 [Note] [MY-000000] [WSREP] Victim thread:
THD: 13, mode: high priority, state: exec, conflict: must_abort, seqno: 215655579
SQL: (null)
2022-03-05T17:46:02.524290Z 13 [Note] [MY-000000] [Galera] Failed to apply write set: gtid: 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:215655579 server_id: 65d2b994-9bca-11ec-bce4-13f1b176a00a client_id: 7505593 trx_id: 192633761 flags: 3
2022-03-05T17:46:02.524887Z 13 [Note] [MY-000000] [Galera] Closing send monitor...
2022-03-05T17:46:02.524900Z 13 [Note] [MY-000000] [Galera] Closed send monitor.
2022-03-05T17:46:02.524910Z 13 [Note] [MY-000000] [Galera] gcomm: terminating thread
2022-03-05T17:46:02.524923Z 13 [Note] [MY-000000] [Galera] gcomm: joining thread
2022-03-05T17:46:02.525223Z 13 [Note] [MY-000000] [Galera] gcomm: closing backend
2022-03-05T17:46:02.526052Z 13 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view (view_id(NON_PRIM,2df1acc3-b163,9)
memb {
95da07cd-9470,0
}
joined {
}
left {
}
partitioned {
2df1acc3-b163,0
65d2b994-bce4,0
}
)
2022-03-05T17:46:02.526098Z 13 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 0
2022-03-05T17:46:02.526121Z 13 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view ((empty))
2022-03-05T17:46:02.526480Z 13 [Note] [MY-000000] [Galera] gcomm: closed
2022-03-05T17:46:02.526529Z 0 [Note] [MY-000000] [Galera] New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
2022-03-05T17:46:02.526656Z 0 [Note] [MY-000000] [Galera] Flow-control interval: [100, 100]
2022-03-05T17:46:02.526680Z 0 [Note] [MY-000000] [Galera] Received NON-PRIMARY.
2022-03-05T17:46:02.526698Z 0 [Note] [MY-000000] [Galera] Shifting SYNCED -> OPEN (TO: 215655706)
2022-03-05T17:46:02.526719Z 0 [Note] [MY-000000] [Galera] New SELF-LEAVE.
Versions
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
mysql Ver 8.0.23-14.1 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel14, Revision d3b9a1d, WSREP version 26.4.3)
Provider options
wsrep_provider_options="gcache.size=2G;socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem"
Cluster freeze happens every time we are trying to run PTOSC on this table. The case shown above is the latest one, but previously we also saw these errors:
2022-03-03T16:45:47.959951Z 0 [Note] [MY-000000] [Galera] Member 0(pxc-xx-node2) initiates vote on 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706787,95187530f4e561db: Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is depre
2022-03-03T16:45:47.960075Z 0 [Note] [MY-000000] [Galera] Votes over 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706787:
95187530f4e561db: 1/3
Waiting for more votes.
2022-03-03T16:45:47.960235Z 12 [Note] [MY-000000] [Galera] Got vote request for seqno 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706787
2022-03-03T16:45:47.966241Z 0 [Note] [MY-000000] [Galera] Member 2(pxc-xx-node1) initiates vote on 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706787,95187530f4e561db: Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is depre
2022-03-03T16:45:47.966346Z 0 [Note] [MY-000000] [Galera] Votes over 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706787:
95187530f4e561db: 2/3
Winner: 95187530f4e561db
2022-03-03T16:45:47.966496Z 12 [Note] [MY-000000] [Galera] 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706787 already voted on. Continue.
2022-03-03T16:45:47.974820Z 0 [Note] [MY-000000] [Galera] Member 0(pxc-xx-node2) initiates vote on 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706788,965b9a15da8fe87d: Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is depre
2022-03-03T16:45:47.974904Z 0 [Note] [MY-000000] [Galera] Votes over 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706788:
965b9a15da8fe87d: 1/3
Waiting for more votes.
2022-03-03T16:45:47.975052Z 18 [Note] [MY-000000] [Galera] Got vote request for seqno 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706788
2022-03-03T16:45:47.980153Z 0 [Note] [MY-000000] [Galera] Member 2(pxc-xx-node1) initiates vote on 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706788,965b9a15da8fe87d: Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is depre
2022-03-03T16:45:47.980242Z 0 [Note] [MY-000000] [Galera] Votes over 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706788:
965b9a15da8fe87d: 2/3
Winner: 965b9a15da8fe87d
2022-03-03T16:45:47.980356Z 18 [Note] [MY-000000] [Galera] 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706788 already voted on. Continue.
2022-03-03T16:45:47.989968Z 0 [Note] [MY-000000] [Galera] Member 0(pxc-xx-node2) initiates vote on 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706791,a3dedf7695bf2968: Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is depre
2022-03-03T16:45:47.990076Z 0 [Note] [MY-000000] [Galera] Votes over 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706791:
a3dedf7695bf2968: 1/3
Waiting for more votes.
2022-03-03T16:45:47.990252Z 1 [Note] [MY-000000] [Galera] Got vote request for seqno 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706791
2022-03-03T16:45:47.994072Z 0 [Note] [MY-000000] [Galera] Member 2(pxc-xx-node1) initiates vote on 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706791,a3dedf7695bf2968: Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is deprecated and will be removed in a future release., Error_code: 1681; Integer display width is depre
2022-03-03T16:45:47.994148Z 0 [Note] [MY-000000] [Galera] Votes over 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706791:
a3dedf7695bf2968: 2/3
Winner: a3dedf7695bf2968
2022-03-03T16:45:47.994265Z 1 [Note] [MY-000000] [Galera] 2f3c9caa-917d-11ec-8fa9-3675bdbbabe7:177706791 already voted on. Continue.
What could be the reason of such behavior? Thanks.