[MDEV-23713] Replication stops with "Index for table is corrupt", table with HASH index, assertion: !cursor->index->is_committed() fails in row_ins_sec_index_entry_by_modify - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Cannot Reproduce
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Fix Version/s: N/A
Component/s: Replication
Labels:
None

Description

with myisam:

--source include/master-slave.inc

--connection master

set sql_mode='';                                     # necessary to let the following REPLACE

set binlog_row_image="minimal";   # necessary to hit the slave assert

CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY, b1 varbinary(100), b2 varbinary(100), UNIQUE KEY (b1) USING hash) engine=myisam;

INSERT INTO t1 VALUES (0,'1','a'),(1,'2','b'),(2,'3','c');

REPLACE INTO t1 (b2) SELECT id FROM t1 WHERE id < 3;

--sync_slave_with_master

 show slave status;

--source include/rpl_end.inc

Last_Errno	126

Last_Error	Could not execute Update_rows_v1 event on table test.t1; Index for table './test/t1.MYI' is corrupt; try to repair it, Error_code: 126; Index for table './test/t1.MYI' is corrupt; try to repair it, Error_code: 126; handler error HA_ERR_CRASHED; the event's master log master-bin.000001, end_log_pos 1029

with Innodb:

10.4 9b688471274868ff94115
2020-09-10 17:22:35 0x7f5849d36300 InnoDB: Assertion failure in file /home/alice/git/10.4/storage/innobase/row/row0ins.cc line 218
InnoDB: Failing assertion: !cursor->index->is_committed()
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: https://mariadb.com/kb/en/library/innodb-recovery-modes/
InnoDB: about forcing recovery.
200910 17:22:35 [ERROR] mysqld got signal 6 ;
Server version: 10.4.15-MariaDB-debug-log

linux/raise.c:51(__GI_raise)[0x7f58605ecf47]
stdlib/abort.c:81(__GI_abort)[0x7f58605ee8b1]
/home/alice/git/10.4/sql/mysqld(+0x25d9979)[0x55905d626979]
ut/ut0dbg.cc:60(_GLOBAL__sub_D_00099_0_ut0dbg.cc)[0x55905d4159ef]
row/row0ins.cc:219(row_ins_sec_index_entry_by_modify(unsigned long, unsigned long, btr_cur_t, unsigned short, mem_block_info_t, mem_block_info_t, dtuple_t const, que_thr_t, mtr_t))[0x55905d424aac]
row/row0ins.cc:3100(row_ins_sec_index_entry_low(unsigned long, unsigned long, dict_index_t, mem_block_info_t, mem_block_info_t, dtuple_t, unsigned long, que_thr_t*))[0x55905d425df8]
row/row0ins.cc:3315(row_ins_sec_index_entry(dict_index_t, dtuple_t, que_thr_t*, bool))[0x55905d528dc1]
row/row0upd.cc:2512(row_upd_sec_index_entry(upd_node_t, que_thr_t))[0x55905d529376]
row/row0upd.cc:2539(row_upd_sec_step(upd_node_t, que_thr_t))[0x55905d52eb2c]
row/row0upd.cc:3325(row_upd(upd_node_t, que_thr_t))[0x55905d52f629]
row/row0upd.cc:3440(row_upd_step(que_thr_t*))[0x55905d47380b]
row/row0mysql.cc:1850(row_update_for_mysql(row_prebuilt_t*))[0x55905d1220b7]
handler/ha_innodb.cc:8761(ha_innobase::update_row(unsigned char const, unsigned char const))[0x55905cb90b46]
sql/handler.cc:6787(handler::ha_update_row(unsigned char const, unsigned char const))[0x55905ced25c9]
sql/log_event.cc:14744(Update_rows_log_event::do_exec_row(rpl_group_info*))[0x55905cebe723]
sql/log_event.cc:11635(Rows_log_event::do_apply_event(rpl_group_info*))[0x55905c13287d]
sql/log_event.h:1484(Log_event::apply_event(rpl_group_info*))[0x55905c114cb9]
sql/slave.cc:3960(apply_event_and_update_pos_apply(Log_event, THD, rpl_group_info*, int))[0x55905c1157bc]
sql/slave.cc:4123(apply_event_and_update_pos(Log_event, THD, rpl_group_info*))[0x55905c116de6]
sql/slave.cc:4439(exec_relay_log_event(THD, Relay_log_info, rpl_group_info*))[0x55905c11e7e0]
sql/slave.cc:5612(handle_slave_sql)[0x55905ddb208f]
nptl/pthread_create.c:463(start_thread)[0x7f5861ec56db]
x86_64/clone.S:97(clone)[0x7f58606cfa3f]

Query (0x612000031d43): REPLACE INTO t1 (b2) SELECT id FROM t1 WHERE id < 3

Attachments

Issue Links

relates to

MDEV-371 Unique indexes for blobs

Closed

MDEV-22184 UNIQUE key with USING HASH clause changes logic for partitioned tables

Open

MDEV-22759 Failing assertion: !cursor->index->is_committed() upon update on table with HASH index

Closed

MDEV-23530 ERROR 1071: Specified key was too long; max key length is 1000/2000 bytes on CREATE TABLE ... UNIQUE KEY ... USING HASH

Open

MDEV-27744 LPAD in vcol created in ORACLE mode makes table corrupted in non-ORACLE

Closed

MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic

Closed

MDEV-32093 long uniques break old->new replication

Closed

MDEV-27145 Failing assertion: !cursor->index->is_committed() in row0ins.cc

Closed

MDEV-31133 MariaDB Slave crash and cannot restart

Open

MDEV-33055 Replica crash loop after Assertion

Closed

MDEV-33351 Corruption of indexes on generated virtual column(s) on an optimistic parallel replica

Closed

(6 relates to)

Activity

Ascending order - Click to sort in descending order

Miguel P added a comment - 2023-01-19 10:17 - edited

This seems to be the same as ~~MDEV-27145~~. I'm also affected by this on MariaDB 10.3.36-MariaDB-0+deb10u2-log. It started happening somewhat randomly on a system that had been replicating for a year, after a reboot.

Normal async replication. Master is the same and has binlog-format=ROW, InnoDB tables and a couple of MyISAM tables that I've added to ignore-table. Dumps obtained with single-transaction. I cannot set replication to work correctly - tried GTID and no GTID, innodb_change_buffering=all or none, mysqldump or mydumper, won't matter: on start it'll immediately encounter an inconsistency error as if I didn't get a consistent backup, and if you try to skip the error in any way (sql_slave_skip_counter=1, slave_skip_errors, etc.) it'll start crash looping with InnoDB: Assertion failure in file /build/mariadb-10.3-lMTCRk/mariadb-10.3-10.3.36/storage/innobase/row/row0ins.cc line 221
InnoDB: Failing assertion: !cursor->index->is_committed()

No idea what to do.

Miguel P added a comment - 2023-01-19 10:17 - edited This seems to be the same as MDEV-27145 . I'm also affected by this on MariaDB 10.3.36-MariaDB-0+deb10u2-log. It started happening somewhat randomly on a system that had been replicating for a year, after a reboot. Normal async replication. Master is the same and has binlog-format=ROW, InnoDB tables and a couple of MyISAM tables that I've added to ignore-table. Dumps obtained with single-transaction. I cannot set replication to work correctly - tried GTID and no GTID, innodb_change_buffering=all or none, mysqldump or mydumper, won't matter: on start it'll immediately encounter an inconsistency error as if I didn't get a consistent backup, and if you try to skip the error in any way (sql_slave_skip_counter=1, slave_skip_errors, etc.) it'll start crash looping with InnoDB: Assertion failure in file /build/mariadb-10.3-lMTCRk/mariadb-10.3-10.3.36/storage/innobase/row/row0ins.cc line 221 InnoDB: Failing assertion: !cursor->index->is_committed() No idea what to do.

Miguel P added a comment - 2023-01-20 23:32

UPDATE: Finally found what was breaking our replication. Indices on GENERATED columns (which are pretty much the point of a GENERATED column — without indexing they're just pre-made aliases for convenience). These break replication and point-in-time recovery, but the funny thing is that they corrupt who knows what, then replication reports inconsistency errors in a different table (in our case, it tended to be on tables which had no GENERATED columns), then if these errors are attempted to be skipped or worked around, MySQL would start crash looping with said assertion. The workaround is to stay clear of GENERATED anything; just use a physical column with a trigger, at least on 10.3.

Miguel P added a comment - 2023-01-20 23:32 UPDATE: Finally found what was breaking our replication. Indices on GENERATED columns (which are pretty much the point of a GENERATED column — without indexing they're just pre-made aliases for convenience). These break replication and point-in-time recovery, but the funny thing is that they corrupt who knows what, then replication reports inconsistency errors in a different table (in our case, it tended to be on tables which had no GENERATED columns), then if these errors are attempted to be skipped or worked around, MySQL would start crash looping with said assertion. The workaround is to stay clear of GENERATED anything; just use a physical column with a trigger, at least on 10.3.

Roel Van de Paar added a comment - 2024-02-18 20:42

The original description also lists a InnoDB stack for the same testcase using InnoDB.
However, the issue does not seem reproducible anymore using the provided testcase:
It passes for MBR and RBR, and reports an unsafe statement for REPLACE...SELECT in SBR:

11.0.5 35cc4b6c058aeb905e8d802569444bdfce02193a (Debug)

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. Statement: REPLACE INTO t1 (b2) SELECT id FROM t1 WHERE id < 3

Roel Van de Paar added a comment - 2024-02-18 20:42 The original description also lists a InnoDB stack for the same testcase using InnoDB. However, the issue does not seem reproducible anymore using the provided testcase: It passes for MBR and RBR, and reports an unsafe statement for REPLACE...SELECT in SBR: 11.0.5 35cc4b6c058aeb905e8d802569444bdfce02193a (Debug) [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. Statement: REPLACE INTO t1 (b2) SELECT id FROM t1 WHERE id < 3

Roel Van de Paar added a comment - 2024-02-22 03:20 - edited

In regards the original testcase:

10.6.16 961b96a5e0dd40512b8fff77dcec273187ccc9fd (Debug) Build 11/09/2023

10.6.16-dbg>CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY, b1 varbinary(100), b2 varbinary(100), UNIQUE KEY (b1) USING hash) engine=myisam;

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Versus

10.6.16 6f091434f313996436b4f01d7e7d62a9f3786958 (Debug) Build 31/10/2023
10.6.16-dbg>CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY, b1 varbinary(100), b2 varbinary(100), UNIQUE KEY (b1) USING hash) engine=myisam;
Query OK, 0 rows affected (0.006 sec)

So there must have been a related change in between 11 Sep and 31 Oct 23. Same on InnoDB.
Also see MDEV-23530.

Roel Van de Paar added a comment - 2024-02-22 03:20 - edited In regards the original testcase: 10.6.16 961b96a5e0dd40512b8fff77dcec273187ccc9fd (Debug) Build 11/09/2023 10.6.16-dbg>CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY, b1 varbinary(100), b2 varbinary(100), UNIQUE KEY (b1) USING hash) engine=myisam; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes Versus 10.6.16 6f091434f313996436b4f01d7e7d62a9f3786958 (Debug) Build 31/10/2023 10.6.16-dbg>CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY, b1 varbinary(100), b2 varbinary(100), UNIQUE KEY (b1) USING hash) engine=myisam; Query OK, 0 rows affected (0.006 sec) So there must have been a related change in between 11 Sep and 31 Oct 23. Same on InnoDB. Also see MDEV-23530 .

Roel Van de Paar added a comment - 2024-02-22 03:32 - edited

A fixed and InnoDB version of the testcase, run with --repeat 100. Did not fail on InnoDB nor MyISAM.

--source include/have_innodb.inc

--source include/master-slave.inc

--source include/have_binlog_format_row.inc

--connection master

SET sql_mode='';  # necessary to let the following REPLACE

SET binlog_row_image="minimal";  # necessary to hit the slave assert

CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY, b1 VARBINARY(100), b2 VARBINARY(100), UNIQUE KEY (b1) USING HASH) ENGINE=InnoDB;

INSERT INTO t1 VALUES (0,'1','a'),(1,'2','b'),(2,'3','c');

REPLACE INTO t1 (b2) SELECT id FROM t1 WHERE id < 3;

--sync_slave_with_master

SHOW SLAVE STATUS;

--source include/rpl_end.inc

Closed as non-reproducible.

Roel Van de Paar added a comment - 2024-02-22 03:32 - edited A fixed and InnoDB version of the testcase, run with --repeat 100 . Did not fail on InnoDB nor MyISAM. --source include/have_innodb.inc --source include/master-slave.inc --source include/have_binlog_format_row.inc --connection master SET sql_mode= '' ; # necessary to let the following REPLACE SET binlog_row_image= "minimal" ; # necessary to hit the slave assert CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY , b1 VARBINARY(100), b2 VARBINARY(100), UNIQUE KEY (b1) USING HASH) ENGINE=InnoDB; INSERT INTO t1 VALUES (0, '1' , 'a' ),(1, '2' , 'b' ),(2, '3' , 'c' ); REPLACE INTO t1 (b2) SELECT id FROM t1 WHERE id < 3; --sync_slave_with_master SHOW SLAVE STATUS; --source include/rpl_end.inc Closed as non-reproducible.

Alice Sherepa added a comment - 2024-02-22 11:39

no assertion after:

commit d762e9d943aa444695ebe845a7376fd9cbb0e3dc

Author: Sergei Golubchik <serg@mariadb.org>

Date:   Tue Sep 5 16:58:55 2023 +0200

    MDEV-32093 long uniques break old->new replication

    recalculate long unique hash in Write_rows_log_event

    and Update_rows_log_event.

    normally generated columns (stored and indexed virtual)

    are deterministic and their values don't need to be recalculated

    on the slave as they're already present in the row image.

    but the long unique hash function was changed in MDEV-27653,

    so a row event from the old master will have the old hash,

    but a table created on the new slave will need a new hash.

Alice Sherepa added a comment - 2024-02-22 11:39 no assertion after: commit d762e9d943aa444695ebe845a7376fd9cbb0e3dc Author: Sergei Golubchik <serg@mariadb.org> Date: Tue Sep 5 16:58:55 2023 +0200 MDEV-32093 long uniques break old->new replication recalculate long unique hash in Write_rows_log_event and Update_rows_log_event. normally generated columns (stored and indexed virtual) are deterministic and their values don't need to be recalculated on the slave as they're already present in the row image. but the long unique hash function was changed in MDEV-27653, so a row event from the old master will have the old hash, but a table created on the new slave will need a new hash.

Andrei Elkin added a comment - 2024-02-22 16:02

alice!

Andrei Elkin added a comment - 2024-02-22 16:02 alice !

People

Assignee:: Unassigned

Reporter:: Alice Sherepa

Votes:: 1 Vote for this issue

Watchers:: 11 Start watching this issue

Dates

Created:: 2020-09-10 15:21

Updated:: 2024-07-07 21:57

Resolved:: 2024-02-22 03:30

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server