[MDEV-28190] sql_mode makes MDEV-371 virtual column expressions nondeterministic Created: 2022-03-29  Updated: 2023-04-06  Resolved: 2023-04-06

Status: Closed
Project: MariaDB Server
Component/s: Character Sets, Data Manipulation - Delete, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
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

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: affects-tests, corruption, not-10.2, not-10.3, regression

Issue Links:
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed
Relates
relates to MDEV-18156 Assertion `0' failed or `btr_validate... Closed
relates to MDEV-26453 Assertion `0' failed in row_upd_sec_i... Closed
relates to MDEV-19011 Assertion `file->s->base.reclength < ... Closed
relates to MDEV-23713 Replication stops with "Index for tab... Stalled
relates to MDEV-27653 long uniques don't work with unicode ... Closed
relates to MDEV-28514 Assertion `file->s->base.reclength < ... Confirmed

 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)

 Comments   
Comment by Marko Mäkelä [ 2022-03-29 ]

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

Comment by Roel Van de Paar [ 2022-09-09 ]

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

Comment by Roel Van de Paar [ 2022-09-09 ]

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.

Comment by Marko Mäkelä [ 2022-09-09 ]

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?

Comment by Nikita Malyavin [ 2022-09-09 ]

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

Comment by Alexander Barkov [ 2022-11-15 ]

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;

Generated at Thu Feb 08 09:58:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.