[MDEV-29954] Unique hash key on column prefix is computed incorrectly Created: 2022-11-06  Updated: 2024-01-24  Resolved: 2024-01-23

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: corruption, regression

Issue Links:
Blocks
is blocked by MDEV-27653 long uniques don't work with unicode ... Closed
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed
Relates
relates to MDEV-24402 CHECK TABLE may miss some cases of in... Closed

 Description    

--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;
 
 
 
# Cleanup
 
DROP TABLE t;

10.6 79dc3989f

 
CHECK 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
 
DROP TABLE t;
 
bug.15430 'innodb'                       [ fail ]  Found warnings/errors in server log file!
 
        Test ended at 2022-11-06 14:15:27
 
line
 
2022-11-06 14:15:26 4 [ERROR] InnoDB: Flagged corruption of `a` in table `test`.`t` in CHECK TABLE; Wrong count

The error started showing up on 10.6 after

commit ab0190101b0587e0e03b2d75a967050b9a85fd1b (HEAD)
 
Author: Marko Mäkelä
 
Date:   Fri Oct 21 10:02:54 2022 +0300
 
 
 
    MDEV-24402: InnoDB CHECK TABLE ... EXTENDED



 Comments   
Comment by Elena Stepanova [ 2022-11-06 ]

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

Comment by Elena Stepanova [ 2022-11-06 ]

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

Comment by Marko Mäkelä [ 2022-11-07 ]

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,

Comment by Marko Mäkelä [ 2022-11-07 ]

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`

Comment by Elena Stepanova [ 2022-11-07 ]

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.

Comment by Roel Van de Paar [ 2023-02-14 ]

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

Comment by Marko Mäkelä [ 2023-08-24 ]

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.

Generated at Thu Feb 08 10:12:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.