Details
Description
On a Galera node, a foreign key column of type CHAR(n) in a multi-byte character set gets two different certification keys for the same parent row.
The parent row key comes from wsrep_store_key_val_for_row() in storage/innobase/handler/ha_innodb.cc. It starts from the whole padded field and cuts it to n characters:
size_t true_len= key_part->length; |
...
|
if (true_len > 0 && cs->mbmaxlen > 1) { |
int error; |
|
|
true_len= my_well_formed_length(cs,
|
(const char *)src_start, |
(const char *)src_start |
+ true_len,
|
(true_len / cs->mbmaxlen),
|
&error);
|
}
|
The child's reference key comes from wsrep_rec_get_foreign_key() in storage/innobase/rem/rem0rec.cc, which collates the bytes InnoDB stored. InnoDB strips the trailing spaces of a CHAR column only down to n bytes, and only for a character set whose shortest character is one byte and whose longest is more, in row_mysql_store_col_in_innobase_format() in storage/innobase/row/row0mysql.cc:
} else if (comp && type == DATA_MYSQL |
&& dtype_get_mbminlen(dtype) == 1
|
&& dtype_get_mbmaxlen(dtype) > 1) {
|
...
|
n_chars = dtype_get_len(dtype) / dtype_get_mbmaxlen(dtype);
|
|
|
/* Strip space padding. */ |
while (col_len > n_chars && ptr[col_len - 1] == 0x20) { |
col_len--;
|
}
|
Both sides collate, but one has cut at n characters and the other at n bytes, so the row key carries more trailing space weights than the reference key. Where InnoDB does not strip at all, in a ROW_FORMAT=REDUNDANT table and in a character set whose shortest character is more than one byte, the reference key covers the whole padded field while the row key still cuts at n characters.
The two keys for the same parent row, from a temporary print at both append sites. The column is CHAR(36) CHARACTER SET utf8mb4 and the value is five U+00E9 characters. The first byte is the index ordinal:
parent row key (73 bytes)
|
00 00 45 00 45 00 45 00 45 00 45 00 20 00 20 00 20 00 20 00 20 00 20 00
|
20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00
|
20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00
|
20
|
|
|
child FK ref key (63 bytes)
|
00 00 45 00 45 00 45 00 45 00 45 00 20 00 20 00 20 00 20 00 20 00 20 00
|
20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00
|
20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
|
The row key keeps 36 characters, so five letters and 31 spaces. The stored value is 36 bytes, so five letters and 26 spaces. The lock report of the same run shows that stored value, and so does frame 12 of the stack below.
The same print over every shape the testcase covers, on the same build and with a five character value. A shape where the two lengths differ is a shape where the node stops applying:
| Column | Parent row key | Child FK ref key |
|---|---|---|
| CHAR(36) utf8mb4 | 73 | 63 |
| CHAR(36) utf8mb3 | 73 | 63 |
| CHAR(36) utf8mb4, four byte characters | 73 | 43 |
| CHAR(36) utf8mb4_bin | 109 | 94 |
| CHAR(36) utf8mb4, ROW_FORMAT=REDUNDANT | 73 | 279 |
| CHAR(36) utf16 | 73 | 145 |
| CHAR(36) ucs2 | 73 | 73 |
| CHAR(36) latin1 | 37 | 37 |
| CHAR(36) utf8mb4, ASCII value | 73 | 73 |
| VARCHAR(36) utf8mb4 | 11 | 11 |
ucs2 matches because every character there is two bytes, so the two cuts land in the same place. latin1 matches because a single byte character set takes neither cut. VARCHAR matches because neither side pads it.
Certification then sees no dependency between the child row insert and the parent row update, both write sets apply in parallel, and the two appliers deadlock on the parent row. Neither can be aborted, so the node stops applying. A debug build asserts in wsrep_assert_valid_bf_bf_wait(). An optimized build keeps waiting, and the testcase reaches its --die.
Some load is needed; one transaction inserts 2000 filler rows before the child row, so the foreign key check lands late while the parent row update applies alongside it.
CHAR(36) holding a UUID as text is a common table design.
MDEV-41012 and MENT-2879 end the same way through a different mechanism. Pull request 5634 for MDEV-41012 stops a UUID, INET6 or INET4 value from being collated at all. A CHAR column is still collated on both sides, so that change does not reach this one. On a CS 10.11.19 debug build carrying it the testcase below fails on every run.
MTR Testcase:
--source include/galera_cluster.inc
|
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
|
--connection node_2
|
SET GLOBAL wsrep_slave_threads = 2; |
|
|
--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2
|
SET SESSION wsrep_sync_wait = 0; |
|
|
--let $shape = 0
|
while ($shape < 10)
|
{
|
--inc $shape |
--let $opt = ENGINE=InnoDB |
|
|
if ($shape == 1) |
{
|
--let $name = CHAR(36) utf8mb4 |
--let $col = CHAR(36) CHARACTER SET utf8mb4 |
--let $val = _utf8mb4 0xC3A9C3A9C3A9C3A9C3A9 |
}
|
if ($shape == 2) |
{
|
--let $name = CHAR(36) utf8mb3 |
--let $col = CHAR(36) CHARACTER SET utf8mb3 |
--let $val = _utf8mb3 0xC3A9C3A9C3A9C3A9C3A9 |
}
|
if ($shape == 3) |
{
|
--let $name = CHAR(36) utf8mb4, four byte characters |
--let $col = CHAR(36) CHARACTER SET utf8mb4 |
--let $val = _utf8mb4 0xF09F9880F09F9880F09F9880F09F9880F09F9880 |
}
|
if ($shape == 4) |
{
|
--let $name = CHAR(36) utf8mb4_bin |
--let $col = CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin |
--let $val = _utf8mb4 0xC3A9C3A9C3A9C3A9C3A9 |
}
|
if ($shape == 5) |
{
|
--let $name = CHAR(36) utf8mb4, ROW_FORMAT=REDUNDANT |
--let $col = CHAR(36) CHARACTER SET utf8mb4 |
--let $val = _utf8mb4 0xC3A9C3A9C3A9C3A9C3A9 |
--let $opt = ENGINE=InnoDB ROW_FORMAT=REDUNDANT |
}
|
if ($shape == 6) |
{
|
--let $name = CHAR(36) utf16 |
--let $col = CHAR(36) CHARACTER SET utf16 |
--let $val = _utf16 0x00E900E900E900E900E9 |
}
|
if ($shape == 7) |
{
|
--let $name = CHAR(36) ucs2 |
--let $col = CHAR(36) CHARACTER SET ucs2 |
--let $val = _ucs2 0x00E900E900E900E900E9 |
}
|
if ($shape == 8) |
{
|
--let $name = CHAR(36) latin1 |
--let $col = CHAR(36) CHARACTER SET latin1 |
--let $val = _latin1 0xE9E9E9E9E9 |
}
|
if ($shape == 9) |
{
|
--let $name = CHAR(36) utf8mb4, ASCII value |
--let $col = CHAR(36) CHARACTER SET utf8mb4 |
--let $val = _utf8mb4 0x6162636465 |
}
|
if ($shape == 10) |
{
|
--let $name = VARCHAR(36) utf8mb4 |
--let $col = VARCHAR(36) CHARACTER SET utf8mb4 |
--let $val = _utf8mb4 0xC3A9C3A9C3A9C3A9C3A9 |
}
|
|
|
--echo # Shape $shape: $name |
--connection node_1 |
--eval CREATE TABLE p (id $col PRIMARY KEY, v INT) $opt |
--eval CREATE TABLE c (id INT AUTO_INCREMENT PRIMARY KEY, p_id $col NOT NULL, KEY k (p_id), FOREIGN KEY (p_id) REFERENCES p (id)) $opt |
--eval CREATE TABLE f (id INT AUTO_INCREMENT PRIMARY KEY, pad CHAR(255)) $opt |
--eval INSERT INTO p VALUES ($val, 0) |
|
|
--let $i = 0 |
--let $round = 10 |
while ($round)
|
{
|
--inc $i |
--connection node_1 |
BEGIN; |
INSERT INTO f (pad) SELECT REPEAT('x',255) FROM seq_1_to_2000; |
--eval INSERT INTO c (p_id) VALUES ($val) |
COMMIT; |
--eval UPDATE p SET v = v + 1 WHERE id = $val |
|
|
--connection node_2a |
--let $wait_timeout = 30 |
--let $wait_condition = SELECT COUNT(*) = $i FROM c |
--source include/wait_condition.inc |
if (!$success) |
{
|
SELECT ID, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user'; |
SELECT trx_id, trx_state, trx_mysql_thread_id, trx_query FROM INFORMATION_SCHEMA.INNODB_TRX; |
--die Node 2 stopped applying. The two appliers are deadlocked on the foreign key parent row. |
}
|
--dec $round |
}
|
|
|
--connection node_1 |
DROP TABLE c, p, f; |
}
|
|
|
--connection node_2
|
SET GLOBAL wsrep_slave_threads = 1; |
--disconnect node_2a |
Leads to:
|
CS 10.11.19 7e981a969a9982445a0c68b4aea853f128b83b0a (Debug, Clang 22.1.8-20260622) Build 05/09/2026 |
2026-09-07 5:19:34 2 [ERROR] InnoDB: Requested lock on record RECORD LOCK_X
|
2026-09-07 5:19:34 2 [ERROR] InnoDB: Conflicting lock on table: `test`.`p` index: PRIMARY that has lock
|
RECORD LOCKS space id 9 page no 3 n bits 320 index PRIMARY of table `test`.`p` trx id 56 lock_mode X locks rec but not gap
|
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
|
0: len 30; hex c3a9c3a9c3a9c3a9c3a92020202020202020202020202020202020202020; asc ; (total 36 bytes);
|
1: len 6; hex 000000000038; asc 8;;
|
2: len 7; hex 0f0000013b0110; asc ; ;;
|
3: len 4; hex 80000001; asc ;;
|
|
|
2026-09-07 5:19:34 2 [ERROR] InnoDB: WSREP state:
|
2026-09-07 5:19:34 2 [ERROR] WSREP: Thread BF trx_id: 53 thread: 2 seqno: 7 client_state: exec client_mode: high priority transaction_mode: executing applier: 1 toi: 0 local: 0 query: INSERT INTO c (p_id) VALUES (_utf8mb4 0xC3A9C3A9C3A9C3A9C3A9)Ƽ�j
|
2026-09-07 5:19:34 2 [ERROR] WSREP: Thread BF trx_id: 56 thread: 11 seqno: 8 client_state: exec client_mode: high priority transaction_mode: committing applier: 1 toi: 0 local: 0 query: NULL
|
2026-09-07 05:19:34 0x7ac1a81156c0 InnoDB: Assertion failure in file /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc line 583
|
InnoDB: We intentionally generate a memory trap.
|
|
CS 10.11.19 7e981a969a9982445a0c68b4aea853f128b83b0a (Debug, Clang 22.1.8-20260622) Build 05/09/2026 |
#5 0x000062f79979de4d in ut_dbg_assertion_failed (expr=0x0, file=0x62f799d52d9e "/tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc", line=583) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/ut/ut0dbg.cc:60
|
#6 0x000062f7995b5110 in wsrep_assert_valid_bf_bf_wait (lock=0x7ac17113b780, trx=0x7ac17113ab80, type_mode=1026) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc:583
|
#7 0x000062f79959ba05 in wsrep_BF_has_to_wait (lock=0x7ac17113b780, trx=0x7ac17113ab80, report_bf_bf_wait=false, type_mode=1026) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc:635
|
#8 0x000062f79959b869 in lock_rec_has_to_wait_wsrep (trx=0x7ac17113ab80, type_mode=1026, lock2=0x7ac17113b780) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc:741
|
#9 0x000062f79959bf3e in lock_rec_has_to_wait (trx=0x7ac17113ab80, type_mode=1026, lock2=0x7ac17113b780, lock_is_on_supremum=false) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc:856
|
#10 0x000062f7995adde4 in lock_rec_other_has_conflicting (mode=1026, cell=..., id=..., heap_no=2, trx=0x7ac17113ab80) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc:1223
|
#11 0x000062f7995b01d8 in lock_rec_lock (impl=false, mode=1026, block=0x72c0cb80f700, heap_no=2, index=0x7ac148032280, thr=0x7ac140029e48) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc:1831
|
#12 0x000062f7995b19df in lock_clust_rec_read_check_and_lock (flags=0, block=0x72c0cb80f700, rec=0x72c0cbcdc07f "ééééé", ' ' <repeats 26 times>, index=0x7ac148032280, offsets=0x7ac1a8111180, mode=LOCK_S, gap_mode=1024, thr=0x7ac140029e48) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/lock/lock0lock.cc:6385
|
#13 0x000062f7996aad41 in row_ins_set_shared_rec_lock (type=1024, block=0x72c0cb80f700, rec=0x72c0cbcdc07f "ééééé", ' ' <repeats 26 times>, index=0x7ac148032280, offsets=0x7ac1a8111180, thr=0x7ac140029e48) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/row/row0ins.cc:1413
|
#14 0x000062f7996aa10a in row_ins_check_foreign_constraint (check_ref=1, foreign=0x7ac0e402d5e0, table=0x7ac0e402cc10, entry=0x7ac14802f808, thr=0x7ac140029e48) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/row/row0ins.cc:1715
|
#15 0x000062f7996b2c58 in row_ins_check_foreign_constraints (table=0x7ac0e402cc10, index=0x7ac0e402fe30, pk=false, entry=0x7ac14802f808, thr=0x7ac140029e48) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/row/row0ins.cc:1956
|
#16 0x000062f7996b2d36 in row_ins_sec_index_entry (index=0x7ac0e402fe30, entry=0x7ac14802f808, thr=0x7ac140029e48, check_foreign=true) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/row/row0ins.cc:3408
|
#17 0x000062f7996ba5a7 in row_ins_index_entry (index=0x7ac0e402fe30, entry=0x7ac14802f808, thr=0x7ac140029e48) at /tmp/10.11-MDEV-41012_dbg/storage/innobase/row/row0ins.cc:3504
|
Attachments
Issue Links
- relates to
-
MDEV-38310 SIGABRT in wsrep_assert_valid_bf_bf_wait | wsrep_BF_has_to_wait | lock_rec_has_to_wait_wsrep
-
- Open
-
-
-
- In Progress
-
-
-
- Open
-
