Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.22
-
None
-
None
Description
When adding indexes to some virtual columns on a table, replication breaks due to these indexes becoming corrupt almost immediately.
Rebuilding the table fixes it but the corruption reappears few seconds later.
I am trying to reproduce it but have been unsuccessful so far.
We are using conservative parallel replication there.
Table definition:
CREATE TABLE `verify_request` (
|
`userid` int(10) unsigned NOT NULL,
|
`checkedoutby` int(10) unsigned NOT NULL DEFAULT 0,
|
`country` char(2) NOT NULL,
|
`flags` int(10) unsigned NOT NULL DEFAULT 0,
|
`level` tinyint(3) unsigned NOT NULL DEFAULT 1,
|
`tier` tinyint(3) unsigned NOT NULL DEFAULT 0,
|
`vtier` tinyint(3) unsigned NOT NULL DEFAULT 0,
|
`nextattempt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`checkedout` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`expires` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`data` tinyblob NOT NULL,
|
`stalled` tinyint(3) unsigned NOT NULL DEFAULT 0,
|
`reason_code` smallint(5) unsigned NOT NULL DEFAULT 0,
|
`flag_0` tinyint(1) GENERATED ALWAYS AS (`flags` & 1 <> 0) VIRTUAL INVISIBLE,
|
`flag_1` tinyint(1) GENERATED ALWAYS AS (`flags` & 2 <> 0) VIRTUAL INVISIBLE,
|
`flag_6` tinyint(1) GENERATED ALWAYS AS (`flags` & 32 <> 0) VIRTUAL INVISIBLE,
|
UNIQUE KEY `userid` (`userid`),
|
KEY `stalled` (`stalled`,`userid`),
|
KEY `idx_stalled_flags` (`stalled`,`flag_0`,`flag_1`,`flag_6`),
|
KEY `flag_0` (`flag_0`,`flag_1`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
Relevant errors from error log (after stopping slave and rebuilding table):
2025-08-14 7:28:41 78414 [Note] Slave I/O thread: Start asynchronous replication to master 'backup@mariadb-main-0-3-dba-main-db.service:3306' in log 'mysql-bin.1150576' at position 86518963
|
2025-08-14 7:28:41 78414 [Note] Slave I/O thread: connected to master 'backup@mariadb-main-0-3-dba-main-db.service:3306',replication starts at GTID position '2101-2101-2326716984,2100-2100-2865028916,2103-2103-1934203824'
|
2025-08-14 7:28:41 78434 [ERROR] InnoDB: Record in index `idx_stalled_flags` of table `redacted`.`verify_request` was not found on update: TUPLE (info_bits=0, 5 fields): {[1] (0x00),[1] (0x80),[1] (0x80),[1] (0x80),[4] ? (0x01A63FFA)} at: COMPACT RECORD(info_bits=0, 5 fields): {[1] (0x00),[1] (0x80),[1] (0x80),[1] (0x80),[4] * (0x01A62A13)}
|
2025-08-14 7:28:44 78439 [ERROR] InnoDB: Record in index `idx_stalled_flags` of table `redacted`.`verify_request` was not found on update: TUPLE (info_bits=0, 5 fields): {[1] (0x01),[1] (0x81),[1] (0x80),[1] (0x80),[4] ? (0x01A63FFA)} at: COMPACT RECORD(info_bits=0, 5 fields): {[1] (0x01),[1] (0x81),[1] (0x80),[1] (0x80),[4] > (0x01A63E83)}
|
2025-08-14 7:28:44 78439 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mariadbd server crashes after the startup or when you dump the tables. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
|
2025-08-14 7:28:44 78439 [Warning] InnoDB: Record in index `idx_stalled_flags` of table `redacted`.`verify_request` was not found on rollback, trying to insert: TUPLE (info_bits=0, 5 fields): {[1] (0x01),[1] (0x81),[1] (0x80),[1] (0x80),[4] ? (0x01A63FFA)} at: COMPACT RECORD(info_bits=0, 5 fields): {[1] (0x01),[1] (0x81),[1] (0x80),[1] (0x80),[4] > (0x01A63E83)}
|
2025-08-14 7:28:44 78439 [Warning] InnoDB: Record in index `idx_stalled_flags` of table `redacted`.`verify_request` was not found on rollback, trying to insert: TUPLE (info_bits=0, 5 fields): {[1] (0x01),[1] (0x81),[1] (0x80),[1] (0x80),[4] ? (0x01A63FFA)} at: COMPACT RECORD(info_bits=0, 5 fields): {[1] (0x01),[1] (0x81),[1] (0x80),[1] (0x80),[4] > (0x01A63E83)}
|
2025-08-14 7:28:44 78439 [ERROR] Slave SQL: Could not execute Update_rows_v1 event on table redacted.verify_request; Index for table 'verify_request' is corrupt; try to repair it, Error_code: 1034; handler error HA_ERR_CRASHED; the event's master log mysql-bin.1150578, end_log_pos 85783869, Gtid 2103-2103-1934221350, Internal MariaDB error code: 1034
|
2025-08-14 7:28:44 78439 [Warning] Slave: Index for table 'verify_request' is corrupt; try to repair it Error_code: 1034
|
2025-08-14 7:28:44 78439 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.1150578' position 85755142; GTID position '2100-2100-2865028916,2101-2101-2326716984,2103-2103-1934221336'
|
2025-08-14 7:28:44 78415 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.1150578' at position 85781698; GTID position '2100-2100-2865028916,2101-2101-2326716984,2103-2103-1934221349', master: mariadb-main-0-3-dba-main-db.service:3306
|
It appears that the offending query that triggers the problem is the one below:
INSERT INTO verify_request (userid, country, flags, level, tier, vtier, nextattempt, expires, data, stalled, reason_code)
|
VALUES (
|
72,
|
'US',
|
4,
|
CASE WHEN NULL IS NULL THEN 1 ELSE NULL END,
|
3,
|
3,
|
NOW(),
|
NOW() + INTERVAL 432000 SECOND,
|
'somedata',
|
0,
|
0
|
)
|
ON DUPLICATE KEY UPDATE
|
expires = values(expires),
|
flags = (flags & ~37 | values(flags)) | (CASE WHEN (flags & 1024) != 0 THEN 65536 ELSE 0 END),
|
level = CASE WHEN NULL IS NULL THEN level ELSE values(level) END,
|
country = values(country),
|
tier = values(tier),
|
vtier = GREATEST(vtier, values(vtier)),
|
data = values(data),
|
stalled = IF (flags & values(stalled), values(stalled), 0),
|
nextattempt = LEAST(nextattempt, values(nextattempt))
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Stalled
-