Elena Stepanova added a comment - 2022-11-06 14:59 - edited Adding a sleep before CHECK causes appearance of additional warnings in the CHECK and different errors in the error log; --source include/have_innodb.inc   CREATE TABLE t (a TINYBLOB, b CHAR (64), UNIQUE (a,b(8))) ENGINE=InnoDB; INSERT INTO t VALUES ( 'foo' , 'bar' ); --sleep 2 CHECK TABLE t EXTENDED;   # Cleanup DROP TABLE t; 10.6 79dc3989f CHECK TABLE t EXTENDED; Table Op Msg_type Msg_text test.t check Warning InnoDB: Clustered index record not found for index `a` of table `test`.`t`: COMPACT RECORD(info_bits=0, 2 fields): {[8]U Z I:S (0x55A75AA8493A53CE),[6] (0x000000000200)} test.t check Warning InnoDB: The B-tree of index a is corrupted. test.t check Warning InnoDB: Index 'a' contains 0 entries, should be 1. test.t check error Corrupt DROP TABLE t; bug.15436 'innodb' [ fail ] Found warnings/errors in server log file! Test ended at 2022-11-06 17:10:33 line 2022-11-06 17:10:32 4 [ERROR] InnoDB: Clustered index record not found for index `a` of table `test`.`t`: COMPACT RECORD(info_bits=0, 2 fields): {[8]U Z I:S (0x55A75AA8493A53CE),[6] (0x000000000200)} 2022-11-06 17:10:32 4 [ERROR] InnoDB: Flagged corruption of `a` in table `test`.`t` in CHECK TABLE-check index

Elena Stepanova added a comment - 2022-11-06 16:45 - edited The MyISAM variation of the error naturally existed before the InnoDB CHECK extension. It should probably be another bug report, if we still file bugs related to unique blobs; the point of this report was that CHECK which succeeded or pretended to succeed before is now throwing corruption errors. 10.4 0d927a57 CREATE TABLE t (a TINYBLOB, b CHAR (64), UNIQUE (a,b(8))) ENGINE=MyISAM; INSERT INTO t VALUES ( 'foo' , 'bar' ); CHECK TABLE t EXTENDED; Table Op Msg_type Msg_text test.t check error Record at : 0 Can't find key for index : 1 test.t check error Corrupt bug.t2 [ fail ] Found warnings/errors in server log file! Test ended at 2022-11-06 18:44:57 line 2022-11-06 18:44:56 4 [ERROR] Got an error from thread_id=4, /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/storage/myisam/ha_myisam.cc:1119 2022-11-06 18:44:56 4 [ERROR] MySQL thread id 4, OS thread handle 140035913884416, query id 24 localhost root Checking table

Marko Mäkelä added a comment - 2022-11-07 08:02 If I define UNIQUE(a,b) , the check will pass. As soon as I define a prefix index on the column b , the check will fail. Internally, the unique index is defined as INDEX(DB_ROW_HASH_1) , with the value 0x55a75aa8493a53ce for the only row. It is notable that a non- EXTENDED check reports no failure. I am debugging the following variant of the test: --source include/have_innodb.inc CREATE TABLE t (a TINYBLOB, b CHAR (64), UNIQUE (a,b(8))) ENGINE=InnoDB STATS_PERSISTENT=0; INSERT INTO t VALUES ( 'foo' , 'bar' ); SET @save_freq=@@ GLOBAL .innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_max_purge_lag_wait= 0; SET GLOBAL innodb_max_purge_lag_wait= @save_freq; CHECK TABLE t QUICK EXTENDED; SELECT b FROM t FORCE INDEX (a); DROP TABLE t; The wait for the purge of committed transaction history is there to give the MDEV-24402 EXTENDED more chances to catch errors. When the value of the column is being computed from the clustered index record in row_check_index_match() , the function innobase_get_computed_value() will invoke row_sel_field_store_in_mysql_format_func() with the full length of column b , instead of using the column prefix length b(8) . I think that this is correct: InnoDB is supposed to supply the full values of the base columns. What seems incorrect is the way how the value of DB_ROW_HASH_1 is computed. I see that the vf.vcol_info.expr in TABLE::update_virtual_field() is defined as Item_func_hash on two expressions, the second one being Item_func_left ). This subexpression would be created in parse_vcol_defs() when opening the table definition: if (keypart->key_part_flag & HA_PART_KEY_SEG) { int length= keypart->length/keypart->field->charset()->mbmaxlen; list_item= new (mem_root) Item_func_left(thd, new (mem_root) Item_field(thd, keypart->field), new (mem_root) Item_int(thd, length)); list_item->fix_fields(thd, NULL); keypart->field->vcol_info= table->field[keypart->field->field_index]->vcol_info; } else list_item= new (mem_root) Item_field(thd, keypart->field); We must ensure that the full column b from the clustered index will be attached to the Item_field when the expression is being evaluated. During the invocation of innobase_get_computed_value() , Item_field::val_str() will be invoked twice: First for on the full column a , with str->Ptr="foo" , then on the column prefix b , with the incorrect value str->Ptr=nullptr . For the full column, the str->Ptr was initialized already during the execution of the INSERT statement. Curiously, even during the INSERT , the column prefix index is not being taken account in the calculation, but apparently the evaluation error will be ignored, unlike in innobase_get_computed_value() : 10.6 18a0f0c17839e3662ac86a22975ae45886ae4cf6 (rr) display *str 1: *str = {<Charset> = {m_charset = 0x563948b170e0 <my_charset_bin>}, <Binary_string> = {<Sql_alloc> = {<No data fields>}, Ptr = 0x0, str_length = 0, Alloced_length = 0, extra_alloc = 0, alloced = false, thread_specific = false}, <No data fields>} (rr) backtrace #0 Item_field::val_str (this=0x7fe0a80266d8, str=0x7fe0a80265c0) at /mariadb/10.6m/sql/item.cc:3331 #1 0x00005639481d513f in Item_func_left::val_str (this=0x7fe0a8026598, str=0x7fe0a80265c0) at /mariadb/10.6m/sql/item_strfunc.cc:1620 #2 0x000056394811a2c4 in Item::val_str (this=<optimized out>) at /mariadb/10.6m/sql/item.h:1014 #3 Item_func_hash::val_int (this=0x7fe0a80ad0b0) at /mariadb/10.6m/sql/item_func.cc:1772 #4 0x000056394814719e in Item::save_int_in_field (this=0x7fe0a80ad0b0, field=0x7fe0a8026080, no_conversions=<optimized out>) at /mariadb/10.6m/sql/item.cc:6809 #5 0x0000563948509db0 in Type_handler_int_result::Item_save_in_field (this=<optimized out>, item=<optimized out>, field=<optimized out>, no_conversions=<optimized out>) at /mariadb/10.6m/sql/sql_type.cc:4353 #6 0x00005639481376c7 in Item::save_in_field (this=0x7fe0a80ad0b0, field=0x7fe0a8026080, no_conversions=<optimized out>) at /mariadb/10.6m/sql/item.cc:6819 #7 0x000056394845769a in TABLE::update_virtual_fields (this=this@entry=0x7fe0a80e2f38, h=<optimized out>, update_mode=update_mode@entry=VCOL_UPDATE_FOR_WRITE) at /mariadb/10.6m/sql/table.cc:8855 #8 0x00005639482c1019 in fill_record (thd=thd@entry=0x7fe0a80020d8, table=table@entry=0x7fe0a80e2f38, ptr=0x7fe0a818beb8, ptr@entry=0x7fe0a818be98, values=@0x7fe0a802ed58: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fe0a802edf8, last = 0x7fe0a802ee90, elements = 2}, <No data fields>}, ignore_errors=ignore_errors@entry=false, use_value=use_value@entry=false) at /mariadb/10.6m/sql/sql_base.cc:8946 #9 0x00005639482c108b in fill_record_n_invoke_before_triggers (thd=thd@entry=0x7fe0a80020d8, table=table@entry=0x7fe0a80e2f38, ptr=0x7fe0a818be98, values=@0x7fe0a802ed58: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fe0a802edf8, last = 0x7fe0a802ee90, elements = 2}, <No data fields>}, ignore_errors=ignore_errors@entry=false, event=event@entry=TRG_EVENT_INSERT) at /mariadb/10.6m/sql/sql_base.cc:8988 #10 0x000056394834aa38 in mysql_insert (thd=thd@entry=0x7fe0a80020d8, table_list=0x7fe0a802e1d0,

Marko Mäkelä added a comment - 2022-11-07 08:30 I see that there is no SQL syntax for the Item_func_hash . An attempt to use a different type of hash function failed: CREATE TABLE t (a TINYBLOB, b CHAR (64), v INT UNSIGNED AS (CRC32(CONCAT(a, LEFT (b,8)))) UNIQUE ) ENGINE=InnoDB STATS_PERSISTENT=0; 10.6 18a0f0c17839e3662ac86a22975ae45886ae4cf6 ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED (1901): Function or expression 'crc32(concat(`a`,left(`b`,8)))' cannot be used in the GENERATED ALWAYS AS clause of `v`

Elena Stepanova added a comment - 2022-11-07 09:24 As further discussion revealed and the extension of the test confirmed, the new "flagging" prevents further DML on the table: --source include/have_innodb.inc   CREATE TABLE t (a TINYBLOB, b CHAR (64), UNIQUE (a,b(8))) ENGINE=InnoDB; INSERT INTO t VALUES ( 'foo' , 'bar' ); CHECK TABLE t EXTENDED; INSERT IGNORE INTO t VALUES ( 'baz' , 'qux' ); bb-10.10-release f8997c68fe HECK TABLE t EXTENDED; Table Op Msg_type Msg_text test.t check Warning InnoDB: Index 'a' contains 0 entries, should be 1. test.t check error Corrupt INSERT IGNORE INTO t VALUES ( 'baz' , 'qux' ); bug.t2 'innodb' [ fail ] Test ended at 2022-11-07 11:20:33   CURRENT_TEST: bug.t2 mysqltest: At line 6: query 'INSERT IGNORE INTO t VALUES (' baz ',' qux ')' failed: ER_INDEX_CORRUPT (1712): Index t is corrupted while it worked seemingly all right before: CREATE TABLE t (a TINYBLOB, b CHAR (64), UNIQUE (a,b(8))) ENGINE=InnoDB; INSERT INTO t VALUES ( 'foo' , 'bar' ); CHECK TABLE t EXTENDED; Table Op Msg_type Msg_text test.t check status OK INSERT IGNORE INTO t VALUES ( 'baz' , 'qux' ); SELECT * FROM t; a b foo bar baz qux So I would still say that from the user perspective, it's a regression, and a fairly big one.

Roel Van de Paar added a comment - 2023-02-14 07:37 Ran into this also CREATE TABLE t (c CHAR (10), UNIQUE KEY a USING HASH (c(1))) ENGINE=MyISAM; INSERT INTO t VALUES (0); CHECK TABLE t EXTENDED; 11.0.1 4d09050ca77a7efac4565d46e4bcd85a5f210c53 (Debug) 2023-02-14 18:32:36 4 [ERROR] Got an error from thread_id=4, /test/11.0_dbg/storage/myisam/ha_myisam.cc:1120 2023-02-14 18:32:36 4 [ERROR] MariaDB thread id 4, OS thread handle 22520814904896, query id 5 localhost root Checking table CHECK TABLE t EXTENDED And for InnoDB: 11.0.1 4d09050ca77a7efac4565d46e4bcd85a5f210c53 (Debug) 2023-02-14 18:37:14 4 [ERROR] InnoDB: Clustered index record not found for index `a` of table `test`.`t`: COMPACT RECORD(info_bits=0, 2 fields): {[8] (0x00000104000002C4),[6] (0x000000000001)} 2023-02-14 18:37:14 4 [ERROR] InnoDB: Flagged corruption of `a` in table `test`.`t` in CHECK TABLE-check index

Marko Mäkelä added a comment - 2023-08-24 12:41 If I understood it correctly, MDEV-27653 made it easier to encounter this problem, by inadvertently changing the hash function to return at most 32 bits of entropy instead of 64 bits, via the Hasher::finalize() that truncates m_nr1 to uint32 .