Hello Experts,
mysql / percona version - Version: 5.7.33-36. We are frequently seeing that our application in HA is failing over from one instance to another and we are not sure of the reason. There are no obvious errors in our application logs. But we are seeing below errors in mysqld.log during the time of failover.
Can you please help us understand what transactions are the below which are taking priority:-
MySQL thread id 12, OS thread handle 139822819411712, query id 4890787279 System lock
MySQL thread id 10, OS thread handle 140562132891392, query id 2743354 System lock
MySQL thread id 7453530, OS thread handle 139822704707328, query id 4890787248
Can these errors cause our application not to connect or mysql service to go into exited state?
*** Priority TRANSACTION:
TRANSACTION 3986822007, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 12, OS thread handle 139822819411712, query id 4890787279 System lock
*** Victim TRANSACTION:
TRANSACTION 3986822004, ACTIVE (PREPARED) 0 sec
, undo log entries 1
MySQL thread id 7453530, OS thread handle 139822704707328, query id 4890787248 ermintrude wsrep: initiating pre-commit for write set (1993771798)
COMMIT
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2200 page no 3 n bits 176 index PRIMARY of table moogdb.sessions trx id 3986822004 lock_mode X
2022-11-19T20:53:34.106777+08:00 12 [Note] WSREP: --------- CONFLICT DETECTED --------
2022-11-19T20:53:34.106788+08:00 12 [Note] WSREP: cluster conflict due to high priority abort for threads:
2022-11-19T20:53:34.106793+08:00 12 [Note] WSREP: Winning thread:
THD: 12, mode: applier, state: executing, conflict: no conflict, seqno: 1993771797
SQL: (null)
2022-11-19T20:53:34.106797+08:00 12 [Note] WSREP: Victim thread:
THD: 7453530, mode: local, state: committing, conflict: no conflict, seqno: 1993771798
SQL: COMMIT
*** Priority TRANSACTION:
TRANSACTION 3986832283, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
, undo log entries 2
MySQL thread id 6, OS thread handle 139822929532672, query id 4890801452 wsrep: writing row for write-set (1993776451)
*** Victim TRANSACTION:
TRANSACTION 3986832281, ACTIVE 0 sec
mysql tables in use 2, locked 2
, undo log entries 16
MySQL thread id 6185427, OS thread handle 139822699300608, query id 4890801447 ermintrude wsrep: initiating replication for write set (-1)
INSERT IGNORE INTO historic_snapshots SELECT s.* FROM snapshots s WHERE s.event_id BETWEEN i_min_event_id AND i_min_event_id + i_event_batch
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1388 page no 2221647 n bits 72 index PRIMARY of table moogdb.snapshots trx id 3986832281 lock mode S
2022-11-19T20:53:43.913727+08:00 6 [Note] WSREP: --------- CONFLICT DETECTED --------
2022-11-19T20:53:43.913749+08:00 6 [Note] WSREP: cluster conflict due to high priority abort for threads:
2022-11-19T20:53:43.913755+08:00 6 [Note] WSREP: Winning thread:
THD: 6, mode: applier, state: executing, conflict: no conflict, seqno: 1993776451
SQL: (null)
2022-11-19T20:53:43.913765+08:00 6 [Note] WSREP: Victim thread:
THD: 6185427, mode: local, state: committing, conflict: no conflict, seqno: -1
SQL: INSERT IGNORE INTO historic_snapshots SELECT s.* FROM snapshots s WHERE s.event_id BETWEEN i_min_event_id AND i_min_event_id + i_event_batch