Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.25
-
None
-
None
-
MariaDB Galera Cluster
Previous MariaDB version: 10.5.21
Current MariaDB version: 10.6.25
Package version: 1:10.6.25+maria~ubu2204
OS/distribution: Ubuntu 22.04
Architecture: amd64
MariaDB packages:
mariadb-server-10.6 1:10.6.25+mariaubu2204
mariadb-server-core-10.6 1:10.6.25+mariaubu2204
Galera provider package:
galera-4 26.4.25-ubu2204
wsrep_patch_version: wsrep_26.22
Storage engine: InnoDBMariaDB Galera Cluster Previous MariaDB version: 10.5.21 Current MariaDB version: 10.6.25 Package version: 1:10.6.25+maria~ubu2204 OS/distribution: Ubuntu 22.04 Architecture: amd64 MariaDB packages: mariadb-server-10.6 1:10.6.25+mariaubu2204 mariadb-server-core-10.6 1:10.6.25+mariaubu2204 Galera provider package: galera-4 26.4.25-ubu2204 wsrep_patch_version: wsrep_26.22 Storage engine: InnoDB
Description
After upgrading our MariaDB Galera cluster from 10.5.21 to 10.6.25, we have twice had the Galera cluster go down/stall while running schema changes. Both incidents involved the same table/foreign-key relationship.
The schema change was adding an index to parent_table:
ALTER TABLE parent_table |
ADD INDEX flag_col (flag_col); |
The child_table table has a foreign key to parent_table. The child_table table is approximately 3.4 GB.
The index appeared to be added successfully within a few seconds on the node where the DDL was issued. The cluster then went down/stalled during Galera replication of the DDL. The second node reported an InnoDB/WSREP BF lock wait while trying to acquire an X table lock on child_table, even though the query being applied was the ALTER TABLE on parent_table.
Important detail: node 2 was not accepting any client/application traffic at the time of the incident. It was only handling Galera replication/apply. Therefore, the lock wait appears to have occurred in the Galera applier / InnoDB DDL path rather than being caused by a local application query on node 2.
The same failure has occurred twice since upgrading to 10.6.25. We had previously been running schema changes under 10.5.21 without this failure mode.
Relevant table relationship:
child_table has a foreign key to parent_table:
CONSTRAINT child_table_ibfk_7 |
FOREIGN KEY (parent_id) |
REFERENCES parent_table (id) |
So in this incident:
- The DDL was run on the parent table: parent_table.
- The lock wait occurred on the child table: child_table.
- The child table is approximately 3.4 GB.
- The affected node was replication-only and not serving client/application traffic.
Relevant error log excerpt from node 2:
TABLE LOCK table example_db.child_table trx id 46291153362 lock mode X waiting
|
|
|
—-- TRX HAS BEEN WAITING 50000168 us FOR THIS LOCK TO BE GRANTED:
|
|
|
ADD INDEX flag_col (flag_col)
|
|
|
2026-06-08 15:00:23 51 [Note] InnoDB: WSREP: BF lock wait long for trx:0xac72af1d2 error: Success query: ALTER TABLE parent_table
|
Expected result:
The replicated TOI DDL should either:
1. Apply successfully on all nodes, or
2. Fail/abort cleanly without taking down or indefinitely stalling the Galera cluster.
Since node 2 had no client traffic, we would not expect a Galera applier-side lock wait to make the cluster unavailable.
Actual result:
The ALTER TABLE ... ADD INDEX completed quickly on the initiating node, but the cluster went down/stalled when the DDL was applied on another node.
Node 2 logged InnoDB: WSREP: BF lock wait long while the applier was waiting for an X table lock on the FK-related table child_table.
The node reporting the lock wait was not receiving application traffic and was only handling replication/apply.
Impact:
Production Galera cluster outage/stall.
This has reproduced twice since upgrading from 10.5.21 to 10.6.25.
Both incidents involved the same table/foreign-key relationship.
Relevant runtime variables:
SHOW GLOBAL VARIABLES LIKE 'wsrep_OSU_method'; |
+——————+—--+ |
| Variable_name | Value |
|
+——————+—--+ |
| wsrep_osu_method | TOI |
|
+——————+—--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'wsrep_applier_threads'; |
Empty set |
|
|
SHOW GLOBAL VARIABLES LIKE 'wsrep_slave_threads'; |
+———————+—--+ |
| Variable_name | Value |
|
+———————+—--+ |
| wsrep_slave_threads | 48 |
|
+———————+—--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'wsrep_provider_version'; |
Empty set |
|
|
SHOW GLOBAL VARIABLES LIKE 'wsrep_patch_version'; |
+———————+———--+ |
| Variable_name | Value |
|
+———————+———--+ |
| wsrep_patch_version | wsrep_26.22 |
|
+———————+———--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'wsrep_desync'; |
+—————+—--+ |
| Variable_name | Value |
|
+—————+—--+ |
| wsrep_desync | OFF | |
+—————+—--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'read_only'; |
+—————+—--+ |
| Variable_name | Value |
|
+—————+—--+ |
| read_only | OFF | |
+—————+—--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'super_read_only'; |
Empty set |
|
|
SHOW GLOBAL VARIABLES LIKE 'lock_wait_timeout'; |
+—————--+—--+ |
| Variable_name | Value |
|
+—————--+—--+ |
| lock_wait_timeout | 86400 |
|
+—————--+—--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; |
+-------------+—--+ |
| Variable_name | Value |
|
+-------------+—--+ |
| innodb_lock_wait_timeout | 50 |
|
+-------------+—--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_on_timeout'; |
+--------------+—--+ |
| Variable_name | Value |
|
+--------------+—--+ |
| innodb_rollback_on_timeout | OFF | |
+--------------+—--+ |
|
|
SHOW GLOBAL VARIABLES LIKE 'innodb_wsrep_applier_lock_wait_timeout'; |
Empty set |
wsrep_provider_options from node 2:
base_dir = /var/lib/mysql/;
|
base_host = 10.0.0.2;
|
base_port = 4567;
|
cert.log_conflicts = no;
|
cert.optimistic_pa = yes;
|
debug = no;
|
evs.auto_evict = 0;
|
evs.causal_keepalive_period = PT1S;
|
evs.debug_log_mask = 0x1;
|
evs.delay_margin = PT1S;
|
evs.delayed_keep_period = PT30S;
|
evs.inactive_check_period = PT0.5S;
|
evs.inactive_timeout = PT15S;
|
evs.info_log_mask = 0;
|
evs.install_timeout = PT7.5S;
|
evs.join_retrans_period = PT1S;
|
evs.keepalive_period = PT1S;
|
evs.max_install_timeouts = 3;
|
evs.send_window = 4;
|
evs.stats_report_period = PT1M;
|
evs.suspect_timeout = PT5S;
|
evs.use_aggregate = true;
|
evs.user_send_window = 2;
|
evs.version = 1;
|
evs.view_forget_timeout = P1D;
|
gcache.dir = /var/lib/mysql/;
|
gcache.keep_pages_size = 0;
|
gcache.keep_plaintext_size = 128M;
|
gcache.mem_size = 0;
|
gcache.name = galera.cache;
|
gcache.page_size = 128M;
|
gcache.recover = yes;
|
gcache.size = 512M;
|
gcomm.thread_prio = ;
|
gcs.check_appl_proto = 1;
|
gcs.fc_debug = 0;
|
gcs.fc_factor = 1.0;
|
gcs.fc_limit = 16;
|
gcs.fc_master_slave = no;
|
gcs.fc_single_primary = no;
|
gcs.max_packet_size = 64500;
|
gcs.max_throttle = 0.25;
|
gcs.recv_q_hard_limit = 9223372036854775807;
|
gcs.recv_q_soft_limit = 0.25;
|
gcs.sync_donor = no;
|
gmcast.listen_addr = tcp://0.0.0.0:4567;
|
gmcast.mcast_addr = ;
|
gmcast.mcast_ttl = 1;
|
gmcast.peer_timeout = PT3S;
|
gmcast.segment = 0;
|
gmcast.time_wait = PT5S;
|
gmcast.version = 0;
|
ist.recv_addr = 172.20.110.6;
|
pc.announce_timeout = PT3S;
|
pc.checksum = false;
|
pc.ignore_quorum = false;
|
pc.ignore_sb = false;
|
pc.linger = PT20S;
|
pc.npvo = false;
|
pc.recovery = true;
|
pc.version = 0;
|
pc.wait_prim = true;
|
pc.wait_prim_timeout = PT30S;
|
pc.weight = 1;
|
protonet.version = 0;
|
repl.causal_read_timeout = PT30S;
|
repl.commit_order = 3;
|
repl.key_format = FLAT8;
|
repl.max_ws_size = 2147483647;
|
repl.proto_max = 11;
|
socket.checksum = 2;
|
socket.recv_buf_size = auto;
|
socket.send_buf_size = auto;
|
Relevant Galera configuration from affected node, sanitized:
[galera]
|
wsrep_on = ON
|
wsrep_cluster_name = "example_cluster"
|
wsrep_cluster_address = gcomm://10.0.0.1,10.0.0.2,10.0.0.3
|
wsrep_provider = /usr/lib/galera/libgalera_smm.so
|
binlog_format = row
|
default_storage_engine = InnoDB
|
innodb_autoinc_lock_mode = 2
|
|
|
bind-address = 0.0.0.0
|
|
|
wsrep_sst_method = rsync
|
wsrep_sst_donor = "node01"
|
wsrep_node_address = "10.0.0.2"
|
wsrep_node_name = "node02"
|
wsrep_slave_threads = 48
|
wsrep_provider_options = "gcache.size=512M"
|
|
|
[mariadb]
|
skip-name-resolve
|
open-files-limit = 20000
|
ft_min_word_len = 2
|
innodb_ft_min_token_size = 2
|
sql_mode = NO_ENGINE_SUBSTITUTION
|
|
|
thread-cache-size = 16
|
table-open-cache = 2048
|
table-definition-cache = 512
|
query-cache-size = 32M
|
query-cache-limit = 2M
|
|
|
sort-buffer-size = 2M
|
read-buffer-size = 2M
|
read-rnd-buffer-size = 8M
|
join-buffer-size = 2M
|
|
|
tmp-table-size = 64M
|
max-heap-table-size = 64M
|
tmp-disk-table-size = 1536M
|
|
|
back-log = 100
|
max-connections = 4096
|
max-connect-errors = 10000
|
max-allowed-packet = 256M
|
interactive-timeout = 600
|
wait-timeout = 600
|
|
|
innodb = FORCE
|
|
|
key-buffer-size = 64M
|
myisam-sort-buffer-size = 128M
|
|
|
innodb-buffer-pool-size = 100G
|
innodb-buffer-pool-instances = 16
|
innodb-log-file-size = 256M
|
innodb_log_files_in_group = 2
|
innodb-log-buffer-size = 4M
|
innodb-file-per-table = 1
|
innodb_strict_mode = off
|
|
|
slow-query-log = 1
|
slow-query-log-file = /var/log/mysql/slow-log.log
|
|
|
log-error = /var/log/mysql/mysqld.log
|
log_slave_updates = ON
|
Notes on configuration:
wsrep_osu_method is not explicitly configured in the cnf file. Runtime value is TOI.
lock_wait_timeout is not explicitly configured in the cnf file. Runtime value is 86400.
innodb_lock_wait_timeout is not explicitly configured in the cnf file. Runtime value is 50.
innodb_wsrep_applier_lock_wait_timeout does not exist on this build.
The affected node was not accepting client traffic, so the lock wait is believed to have occurred in Galera replication/apply or InnoDB internal DDL/foreign-key locking rather than being caused by an application transaction.
SHOW CREATE TABLE output for affected parent table:
Note: this SHOW CREATE TABLE output was captured after the incident, so it includes KEY flag_col (flag_col). Before the DDL was run, this index did not exist.
CREATE TABLE parent_table ( |
id int(11) unsigned NOT NULL AUTO_INCREMENT, |
order_ref varchar(12) NOT NULL DEFAULT '', |
method_code varchar(3) NOT NULL COMMENT 'Courier code', |
service_type varchar(3) NOT NULL DEFAULT 'DEL', |
method_id smallint(5) unsigned NOT NULL DEFAULT 0, |
service_level varchar(3) NOT NULL, |
api_code varchar(5) NOT NULL, |
api_id int(11) unsigned DEFAULT NULL, |
tracking_ref varchar(32) NOT NULL, |
label_data mediumtext NOT NULL, |
package_qty smallint(5) unsigned NOT NULL DEFAULT 1, |
total_weight decimal(6,2) unsigned NOT NULL DEFAULT 0.00, |
export_required char(1) NOT NULL DEFAULT 'N', |
special_instructions varchar(255) NOT NULL, |
notes varchar(255) NOT NULL, |
flag_col char(1) NOT NULL DEFAULT 'N', |
processed char(1) NOT NULL DEFAULT 'N', |
date_int int(10) unsigned NOT NULL DEFAULT 0, |
staff_id smallint(5) unsigned NOT NULL DEFAULT 0, |
site_id smallint(5) unsigned NOT NULL DEFAULT 0, |
express tinyint(1) unsigned NOT NULL DEFAULT 0, |
voided tinyint(1) unsigned NOT NULL DEFAULT 0, |
PRIMARY KEY (id), |
KEY tracking_ref (tracking_ref), |
KEY order_ref (order_ref), |
KEY processed (processed), |
KEY site_id (site_id), |
KEY express (express), |
KEY voided (voided), |
KEY processed_2 (processed,voided), |
KEY processed_voided_site_id (processed,voided,site_id), |
KEY flag_col (flag_col) |
) ENGINE=InnoDB AUTO_INCREMENT=7686744 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PACK_KEYS=0 |
SHOW CREATE TABLE output for affected child table:
CREATE TABLE child_table ( |
child_id int(11) unsigned NOT NULL AUTO_INCREMENT, |
list_id int(10) unsigned DEFAULT NULL, |
item_id mediumint(8) unsigned DEFAULT NULL, |
parent_id int(11) unsigned DEFAULT NULL, |
related_line_id int(11) unsigned DEFAULT NULL, |
related_pick_id mediumint(8) unsigned DEFAULT NULL, |
related_child_id mediumint(8) unsigned DEFAULT NULL, |
fulfilment_line_id mediumint(8) unsigned DEFAULT NULL, |
reserve_line_id int(11) DEFAULT NULL, |
product_id mediumint(8) unsigned DEFAULT NULL, |
location_id smallint(6) unsigned DEFAULT NULL, |
staff_id smallint(6) unsigned DEFAULT NULL, |
status char(1) NOT NULL DEFAULT 'P', |
packing_location_id smallint(6) unsigned DEFAULT NULL, |
packing_ready char(1) NOT NULL DEFAULT 'N', |
date_int int(10) unsigned NOT NULL DEFAULT 0, |
voided tinyint(1) unsigned NOT NULL DEFAULT 0, |
site_id smallint(6) unsigned DEFAULT NULL, |
destination_type smallint(6) unsigned NOT NULL, |
created_at datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)', |
filter_id int(11) unsigned DEFAULT NULL, |
PRIMARY KEY (child_id), |
KEY list_id (list_id), |
KEY product_id (product_id), |
KEY location_id (location_id), |
KEY staff_id (staff_id), |
KEY parent_id (parent_id), |
KEY related_line_id (related_line_id), |
KEY item_id (item_id), |
KEY related_child_id (related_child_id), |
KEY related_pick_id (related_pick_id), |
KEY fulfilment_line_id (fulfilment_line_id), |
KEY reserve_line_id (reserve_line_id), |
KEY voided (voided), |
KEY status_2 (status,voided), |
KEY non_voided_packing_ready (packing_ready,voided), |
KEY site_id (site_id), |
KEY packing_location_id (packing_location_id), |
KEY status_date (status,date_int), |
KEY filter_id (filter_id), |
CONSTRAINT child_table_ibfk_1 FOREIGN KEY (location_id) REFERENCES ref_table_1 (id), |
CONSTRAINT child_table_ibfk_10 FOREIGN KEY (related_child_id) REFERENCES ref_table_2 (id), |
CONSTRAINT child_table_ibfk_12 FOREIGN KEY (reserve_line_id) REFERENCES ref_table_3 (id), |
CONSTRAINT child_table_ibfk_13 FOREIGN KEY (product_id) REFERENCES ref_table_4 (id), |
CONSTRAINT child_table_ibfk_14 FOREIGN KEY (fulfilment_line_id) REFERENCES ref_table_5 (id), |
CONSTRAINT child_table_ibfk_15 FOREIGN KEY (filter_id) REFERENCES ref_table_6 (id), |
CONSTRAINT child_table_ibfk_2 FOREIGN KEY (staff_id) REFERENCES ref_table_7 (id), |
CONSTRAINT child_table_ibfk_3 FOREIGN KEY (packing_location_id) REFERENCES ref_table_1 (id), |
CONSTRAINT child_table_ibfk_4 FOREIGN KEY (site_id) REFERENCES ref_table_8 (id), |
CONSTRAINT child_table_ibfk_5 FOREIGN KEY (list_id) REFERENCES ref_table_9 (id), |
CONSTRAINT child_table_ibfk_6 FOREIGN KEY (item_id) REFERENCES ref_table_10 (id), |
CONSTRAINT child_table_ibfk_7 FOREIGN KEY (parent_id) REFERENCES parent_table (id), |
CONSTRAINT child_table_ibfk_8 FOREIGN KEY (related_line_id) REFERENCES ref_table_11 (id), |
CONSTRAINT child_table_ibfk_9 FOREIGN KEY (related_pick_id) REFERENCES ref_table_12 (id) |
) ENGINE=InnoDB AUTO_INCREMENT=22123468 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
Possible related existing issues:
I found several existing issues that may be related. This report may be a duplicate or related regression, but our case includes the additional detail that the affected node was replication-only and not serving client traffic.
MDEV-32853: InnoDB: WSREP: BF lock wait long for trx indefinitely
MDEV-30161: InnoDB lock wait timeout watchdog
MDEV-35002: ALTER TABLE replicating (TOI) to other hosts in cluster caused long BF lock on downstream servers
MDEV-38544: Galera applier threads not respecting innodb_lock_wait_timeout / waiting 50 seconds
Steps to reproduce:
We do not yet have a minimal public test case. The production reproduction is:
1. Run a MariaDB Galera cluster on 10.6.25 with wsrep_osu_method=TOI.
2. Have at least one node that is not accepting client/application traffic and is only applying Galera replication.
3. Use InnoDB tables with a foreign-key relationship where a large child table references the parent table.
4. In our case, the child table is approximately 3.4 GB.
5. Run:
ALTER TABLE parent_table |
ADD INDEX flag_col (flag_col); |
6. The ALTER completes quickly on the initiating node.
7. On another node, the Galera applier logs InnoDB: WSREP: BF lock wait long while waiting for an X table lock on child_table.
8. The cluster stalls/goes down.
Requested investigation:
Please investigate whether MariaDB 10.6.25 has a regression or known unsafe interaction involving:
1. Galera TOI DDL replication.
2. InnoDB ALTER TABLE ... ADD INDEX.
3. Foreign-key-related table locking, specifically when altering a parent table that has a large child table referencing it.
4. Galera applier / BF lock waits.
5. Applier-side lock waits on nodes with no client traffic.
6. Behaviour change from MariaDB 10.5.21 to 10.6.25.
The key question is why a replication-only node could stall during TOI application of the DDL while waiting for an X table lock on the FK-related child table, and why this wait caused the Galera cluster to become unavailable rather than failing/aborting cleanly.
Additional information available:
We can provide the following if useful:
- Full unabridged MariaDB error log from node 2 around the incident.
- Error logs from all Galera nodes around the incident.
- Sanitized SHOW CREATE TABLE parent_table.
- Sanitized SHOW CREATE TABLE child_table.
- Foreign-key definitions between the affected tables.
- Full output of SHOW VARIABLES.
- Output of SHOW GLOBAL STATUS LIKE 'wsrep%' if captured during/after the incident.
- Details of the upgrade path from 10.5.21 to 10.6.25.
- Sanitized my.cnf/Galera configuration.
- Slow query log around the incident time, if useful.
Privacy note:
The logs and schema names may contain internal identifiers. We have sanitized public excerpts and can provide full logs privately if required.
Attachments
Issue Links
- relates to
-
-
- Closed
-
