[MDEV-28190] sql_mode makes MDEV-371 virtual column expressions nondeterministic - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
Fix Version/s: 11.1.1, 10.11.3, 11.0.2, 10.4.29, 10.5.20, 10.6.13, 10.7.8, 10.8.8, 10.9.6, 10.10.4
Component/s: Character Sets, Data Manipulation - Delete, Storage Engine - InnoDB
Labels:

Description

Looks like a new occurrence of ~~MDEV-26453~~

CREATE TABLE t2 (a INT,b CHAR(20)) ENGINE=InnoDB;

CREATE UNIQUE INDEX bi USING HASH ON t2 (b);

INSERT INTO t2 VALUES (0,0);

SET sql_mode='pad_char_to_full_length';

DELETE FROM t2;

Leads to:

10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Debug)
mysqld: /test/10.9_dbg/storage/innobase/row/row0upd.cc:2050: dberr_t row_upd_sec_index_entry(upd_node_t, que_thr_t): Assertion `0' failed.

10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Debug)
Core was generated by `/test/MD160322-mariadb-10.9.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
[Current thread is 1 (Thread 0x14e7141a1700 (LWP 1362604))]
(gdb) bt
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x000014e732c56859 in __GI_abort () at abort.c:79
#2 0x000014e732c56729 in __assert_fail_base (fmt=0x14e732dec588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55d261ff41e7 "0", file=0x55d2620e2380 "/test/10.9_dbg/storage/innobase/row/row0upd.cc", line=2050, function=<optimized out>) at assert.c:92
#3 0x000014e732c68006 in __GI___assert_fail (assertion=assertion@entry=0x55d261ff41e7 "0", file=file@entry=0x55d2620e2380 "/test/10.9_dbg/storage/innobase/row/row0upd.cc", line=line@entry=2050, function=function@entry=0x55d2620e3768 "dberr_t row_upd_sec_index_entry(upd_node_t, que_thr_t)") at assert.c:101
#4 0x000055d261a03866 in row_upd_sec_index_entry (node=node@entry=0x14e6c003b0a0, thr=thr@entry=0x14e6c0082de8) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2050
#5 0x000055d261a0463e in row_upd_sec_step (thr=0x14e6c0082de8, node=0x14e6c003b0a0) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2199
#6 row_upd (thr=0x14e6c0082de8, node=0x14e6c003b0a0) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:2936
#7 row_upd_step (thr=thr@entry=0x14e6c0082de8) at /test/10.9_dbg/storage/innobase/row/row0upd.cc:3051
#8 0x000055d2619ab359 in row_update_for_mysql (prebuilt=0x14e6c003a5f8) at /test/10.9_dbg/storage/innobase/row/row0mysql.cc:1700
#9 0x000055d261813944 in ha_innobase::delete_row (this=0x14e6c0039900, record=0x14e6c0039478 <incomplete sequence \361>) at /test/10.9_dbg/storage/innobase/handler/ha_innodb.cc:8673
#10 0x000055d26147e176 in handler::ha_delete_row (this=0x14e6c0039900, buf=0x14e6c0039478 <incomplete sequence \361>) at /test/10.9_dbg/sql/handler.cc:7659
#11 0x000055d261105afd in TABLE::delete_row (this=0x14e6c00293f8) at /test/10.9_dbg/sql/sql_delete.cc:281
#12 0x000055d261103a6d in mysql_delete (thd=thd@entry=0x14e6c0000db8, table_list=0x14e6c0013ea0, conds=<optimized out>, order_list=order_list@entry=0x14e6c0005ca8, limit=18446744073709551615, options=<optimized out>, result=<optimized out>) at /test/10.9_dbg/sql/sql_delete.cc:834
#13 0x000055d261166589 in mysql_execute_command (thd=thd@entry=0x14e6c0000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.9_dbg/sql/sql_limit.h:85
#14 0x000055d261150437 in mysql_parse (thd=thd@entry=0x14e6c0000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14e7141a0400) at /test/10.9_dbg/sql/sql_parse.cc:8027
#15 0x000055d26115f0d3 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14e6c0000db8, packet=packet@entry=0x14e6c000b889 "DELETE FROM t2", packet_length=packet_length@entry=14, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_class.h:1362
#16 0x000055d26116251a in do_command (thd=0x14e6c0000db8, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_parse.cc:1402
#17 0x000055d2612dd228 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55d263c49738, put_in_cache=put_in_cache@entry=true) at /test/10.9_dbg/sql/sql_connect.cc:1418
#18 0x000055d2612dd82d in handle_one_connection (arg=arg@entry=0x55d263c49738) at /test/10.9_dbg/sql/sql_connect.cc:1312
#19 0x000055d261763f23 in pfs_spawn_thread (arg=0x55d263b5d568) at /test/10.9_dbg/storage/perfschema/pfs.cc:2201
#20 0x000014e733167609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#21 0x000014e732d53163 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Debug builds crash. Both optimized and debug have corruptions:

10.5.16 73fee39ea62037780c59161507e89dd76c10b7a3 (Debug)
Version: '10.5.16-MariaDB-debug' socket: '/test/MD160322-mariadb-10.5.16-linux-x86_64-dbg/socket.sock' port: 10507 MariaDB Server
2022-03-29 16:55:25 4 [ERROR] InnoDB: Record in index `bi` of table `test`.`t2` was not found on update: TUPLE (info_bits=0, 2 fields): {[8] e (0x0000016500000A95),[6] (0x000000000200)} at: COMPACT RECORD(info_bits=0, 2 fields): {[8] (0x00000104000002C4),[6] (0x000000000200)}
mysqld: /test/10.5_dbg/storage/innobase/row/row0upd.cc:2114: dberr_t row_upd_sec_index_entry(upd_node_t, que_thr_t): Assertion `0' failed.

10.4.25 9c6135e81f29b3e3286d6b864c0fdafc2fea16ce (Optimized)
Version: '10.4.25-MariaDB' socket: '/test/MD160322-mariadb-10.4.25-linux-x86_64-opt/socket.sock' port: 10365 MariaDB Server
2022-03-29 17:12:35 9 [ERROR] InnoDB: Record in index `bi` of table `test`.`t2` was not found on update: TUPLE (info_bits=0, 2 fields): {[8] e (0x0000016500000A95),[6] (0x000000000200)} at: COMPACT RECORD(info_bits=0, 2 fields): {[8] (0x00000104000002C4),[6] (0x000000000200)}
2022-03-29 17:12:38 0 [Note] /test/MD160322-mariadb-10.4.25-linux-x86_64-opt/bin/mysqld (initiated by: root[root] @ localhost []): Normal shutdown

10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Optimized)
2022-03-29 17:15:46 0 [Note] /test/MD160322-mariadb-10.9.0-linux-x86_64-opt/bin/mysqld: ready for connections.
Version: '10.9.0-MariaDB' socket: '/test/MD160322-mariadb-10.9.0-linux-x86_64-opt/socket.sock' port: 10640 MariaDB Server
2022-03-29 17:16:02 4 [ERROR] InnoDB: Record in index `bi` of table `test`.`t2` was not found on update: TUPLE (info_bits=0, 2 fields): {[8] e (0x0000016500000A95),[6] (0x000000000200)} at: COMPACT RECORD(info_bits=0, 2 fields): {[8] (0x00000104000002C4),[6] (0x000000000200)}

Bug confirmed present in:
MariaDB: 10.4.25 (dbg), 10.4.25 (opt), 10.5.16 (dbg), 10.5.16 (opt), 10.6.8 (dbg), 10.6.8 (opt), 10.7.4 (dbg), 10.7.4 (opt), 10.8.3 (dbg), 10.8.3 (opt), 10.9.0 (dbg), 10.9.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.2.44 (dbg), 10.2.44 (opt), 10.3.35 (dbg), 10.3.35 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.37 (dbg), 5.7.37 (opt), 8.0.28 (dbg), 8.0.28 (opt)

Attachments

Issue Links

is caused by

MDEV-371 Unique indexes for blobs

Closed

relates to

MDEV-18156 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH

Closed

MDEV-26453 Assertion `0' failed in row_upd_sec_index_entry & corruption

Closed

MDEV-19011 Assertion `file->s->base.reclength < file->s->vreclength' failed in ha_myisam::setup_vcols_for_repair

Closed

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

Closed

MDEV-27653 long uniques don't work with unicode collations

Closed

MDEV-28514 Assertion `file->s->base.reclength < file->s->vreclength || !table->s->stored_fields' failed in ha_myisam::setup_vcols_for_repair

Confirmed

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä added a comment - 2022-03-29 06:07

~~MDEV-371~~ causes hidden indexed virtual columns to be created.

Marko Mäkelä added a comment - 2022-03-29 06:07 MDEV-371 causes hidden indexed virtual columns to be created.

Roel Van de Paar added a comment - 2022-09-09 07:13 - edited

Tested on UBSAN/ASAN and no additional output observed for testcase (though corruption/crash still happens).

Roel Van de Paar added a comment - 2022-09-09 07:13 - edited Tested on UBSAN/ASAN and no additional output observed for testcase (though corruption/crash still happens).

Roel Van de Paar added a comment - 2022-09-09 07:15

When the sql_mode is set before the INSERT, no crash takes place. The corruption output is apparently InnoDB complaining that the virtual column expression is not deterministic. w/ Thanks to marko.

Roel Van de Paar added a comment - 2022-09-09 07:15 When the sql_mode is set before the INSERT, no crash takes place. The corruption output is apparently InnoDB complaining that the virtual column expression is not deterministic. w/ Thanks to marko .

Marko Mäkelä added a comment - 2022-09-09 07:28

According to Roel, the following will work without any problem:

CREATE TABLE t2 (a INT,b CHAR(20)) ENGINE=InnoDB;

CREATE UNIQUE INDEX bi USING HASH ON t2 (b);

SET sql_mode='pad_char_to_full_length';

INSERT INTO t2 VALUES (0,0);

DELETE FROM t2;

The difference to the Description is that we are not changing sql_mode between the INSERT and DELETE. The sql_mode appears to be part of the hidden virtual column expression that was defined by ~~MDEV-371~~.

Maybe the ~~MDEV-371~~ computation should simply ignore all sql_mode flags of this kind?

Marko Mäkelä added a comment - 2022-09-09 07:28 According to Roel , the following will work without any problem: CREATE TABLE t2 (a INT ,b CHAR (20)) ENGINE=InnoDB; CREATE UNIQUE INDEX bi USING HASH ON t2 (b); SET sql_mode= 'pad_char_to_full_length' ; INSERT INTO t2 VALUES (0,0); DELETE FROM t2; The difference to the Description is that we are not changing sql_mode between the INSERT and DELETE . The sql_mode appears to be part of the hidden virtual column expression that was defined by MDEV-371 . Maybe the MDEV-371 computation should simply ignore all sql_mode flags of this kind?

Nikita Malyavin added a comment - 2022-09-09 12:49

I think ignoring some modes for particular (namely, indexed) virtual columns is not an obvious solution for a user. I guess this problem is not only with long UNIQUE, where the virtual column is implicit, but also can be reproduced with explicit vcol, so I'd rather forbid it, but have to prove it first

Nikita Malyavin added a comment - 2022-09-09 12:49 I think ignoring some modes for particular (namely, indexed) virtual columns is not an obvious solution for a user. I guess this problem is not only with long UNIQUE, where the virtual column is implicit, but also can be reproduced with explicit vcol, so I'd rather forbid it, but have to prove it first

Alexander Barkov added a comment - 2022-11-15 11:47

The problem is repeatable with this script:

SET sql_mode='';

CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)) ENGINE=InnoDB;

CREATE UNIQUE INDEX bi USING HASH ON t1 (b);

INSERT INTO t1 VALUES (0,0);

SET sql_mode='pad_char_to_full_length';

DELETE FROM t1;

Alexander Barkov added a comment - 2022-11-15 11:47 The problem is repeatable with this script: SET sql_mode= '' ; CREATE OR REPLACE TABLE t1 (a CHAR (20),b CHAR (20)) ENGINE=InnoDB; CREATE UNIQUE INDEX bi USING HASH ON t1 (b); INSERT INTO t1 VALUES (0,0); SET sql_mode= 'pad_char_to_full_length' ; DELETE FROM t1;

People

Assignee:: Alexander Barkov

Reporter:: Roel Van de Paar

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2022-03-29 06:04

Updated:: 2023-04-06 12:45

Resolved:: 2023-04-06 12:45

Git Integration

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

MariaDB Server