[MDEV-13708] Crash with indexed virtual columns and FK cascading deletes Created: 2017-09-01  Updated: 2020-05-04  Resolved: 2017-09-18

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, JSON, Storage Engine - InnoDB, Virtual Columns
Affects Version/s: 10.2.6, 10.2.8, 10.2
Fix Version/s: 10.2.9

Type: Bug Priority: Major
Reporter: Brian Grossman Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 6.8


 Description   

I have discovered an issue that causes the server to crash and restart, when it is trying to delete rows that involve a virtual column, an index, and a foreign key. The crash happens for me in both 10.2.6 and 10.2.8.

To reproduce, start with the two CREATE TABLEs below, then the INSERTs given. When you try any of the DELETEs given, MariaDB will crash.

To avoid the crash, try the delete again after any of the given ALTER TABLEs.

  • One drops the other_id index in tbl_lr.
  • One drops the other_id foreign key in tbl_lh.
  • One changes the other_id column definition from virtual to persistent.

--
 
-- for easy repeated attempts, drop the tables
 
--
 
SET FOREIGN_KEY_CHECKS=0;
 
drop table if exists tbl_lr ;
 
drop table if exists tbl_lh ;
 
SET FOREIGN_KEY_CHECKS=1;


--
 
-- Create the tables and fill them.
 
--
 
CREATE TABLE `tbl_lr` (
 
	`tbl_lr_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
	`context` text COLLATE utf8_unicode_ci DEFAULT NULL,
 
	`other_id` bigint(20) unsigned GENERATED ALWAYS AS (JSON_VALUE(`context`,'$.level1.otherId')) VIRTUAL,
 
	PRIMARY KEY (`tbl_lr_id`) ,
 
	KEY `other_id` (`other_id`)
 
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
	;
 
 
 
CREATE TABLE `tbl_lh` (
 
	`tbl_lr_id` bigint(20) unsigned DEFAULT NULL,
 
	KEY `tbl_lr_id` (`tbl_lr_id`) ,
 
	CONSTRAINT `fk_tbl_lr_id` FOREIGN KEY (`tbl_lr_id`) REFERENCES `tbl_lr` (`tbl_lr_id`) ON DELETE CASCADE
 
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
	;
 
 
 
insert into tbl_lr (tbl_lr_id,context) values
 
	( 1400235131, '{"level1":{"otherId":1232137706}}' ) ,
 
	( 1400235231, '{"level1":{"otherId":1232137806}}' ) ;
 
insert into tbl_lh (tbl_lr_id) values (1400235131), (1400235231) ;
 
 
 
select * from tbl_lh ;
 
select * from tbl_lr ;


--
 
-- Any of these deletes will crash the db when used with the above tables and inserts.
 
--
 
 
 
delete from tbl_lr where tbl_lr_id in ( 1400235131 , 1400235231 ) ; -- kablooey
 
 
 
delete from tbl_lr ; -- kablooey
 
 
 
delete from tbl_lr where tbl_lr_id = 1400235131 ;   delete from tbl_lr ; -- kablooey


--
 
-- The DELETE won't crash the db after CREATE+INSERT when combined with this ALTER.
 
--
 
 
 
alter table tbl_lr drop key other_id ;
 
delete from tbl_lr ; -- not kablooey


--
 
-- The DELETE won't crash the db after CREATE+INSERT when combined with this ALTER.
 
--
 
 
 
alter table tbl_lh drop foreign key fk_tbl_lr_id ;
 
delete from tbl_lr ; -- not kablooey


--
 
-- The DELETE won't crash the db after CREATE+INSERT when combined with this ALTER.
 
--
 
 
 
alter table tbl_lr
 
	drop key `other_id` ,
 
	drop column `other_id` ,
 
	add column `other_id` bigint(20) unsigned as (JSON_VALUE(context, '$.level1.otherId')) PERSISTENT ,
 
	add key `other_id` (`other_id`)
 
	;
 
delete from tbl_lr ; -- not kablooey



 Comments   
Comment by Alice Sherepa [ 2017-09-04 ]

Brian Grossman , thanks for the report!
Reproducible on 10.2, 10.3, (>=10.2.3) with innodb, not reproducible with myisam.
test case:

CREATE TABLE `t1` (
 
	`id` int not null primary key,
 
	`context` varchar(30),
 
	`id2` int AS (JSON_VALUE(`context`,'$.level1.id2')) VIRTUAL,
 
	KEY `id2` (`id2`)) ENGINE=innodb;
 
 
CREATE TABLE `t2` (`id` int,KEY (`id`) ,
 
	CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE) ENGINE=innodb;
 
 
 
insert into t1 (id,context) values ( 1, '{"level1":{"id2":1}}' ),( 2, '{"level1":{"id2":2}}' ) ;
 
insert into t2 (id) values (1), (2) ;
 
 
delete from t1;

error log

Server version: 10.2.9-MariaDB-debug
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=7
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467370 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f47d0000a98
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f48382bdec0 thread_stack 0x49000
 
/data/bld/10.2/bin/mysqld(my_print_stacktrace+0x38)[0x210b182a15]
 
/data/bld/10.2/bin/mysqld(handle_fatal_signal+0x3a3)[0x210aa0f25e]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f483ba98390]
 
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7f483ae51428]
 
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f483ae5302a]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x2dbd7)[0x7f483ae49bd7]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7f483ae49c82]
 
/data/bld/10.2/bin/mysqld(+0xa86602)[0x210ac7d602]
 
/data/bld/10.2/bin/mysqld(+0xc08d93)[0x210adffd93]
 
/data/bld/10.2/bin/mysqld(+0xc08fed)[0x210adfffed]
 
/data/bld/10.2/bin/mysqld(+0xc0b3d3)[0x210ae023d3]
 
/data/bld/10.2/bin/mysqld(+0xc0bd54)[0x210ae02d54]
 
/data/bld/10.2/bin/mysqld(+0xc0c245)[0x210ae03245]
 
/data/bld/10.2/bin/mysqld(+0xc0c724)[0x210ae03724]
 
/data/bld/10.2/bin/mysqld(+0xbaee0d)[0x210ada5e0d]
 
/data/bld/10.2/bin/mysqld(+0xa6ecf8)[0x210ac65cf8]
 
/data/bld/10.2/bin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x1b0)[0x210aa1eac8]
 
/data/bld/10.2/bin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP10SQL_I_ListI8st_orderEyyP13select_result+0x15be)[0x210abab4b2]
 
/data/bld/10.2/bin/mysqld(_Z21mysql_execute_commandP3THD+0x4cc7)[0x210a7a531c]
 
/data/bld/10.2/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x2a2)[0x210a7afb29]
 
/data/bld/10.2/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0xfee)[0x210a79d84b]
 
/data/bld/10.2/bin/mysqld(_Z10do_commandP3THD+0x700)[0x210a79c1be]
 
/data/bld/10.2/bin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1ca)[0x210a8e8b66]
 
/data/bld/10.2/bin/mysqld(handle_one_connection+0x30)[0x210a8e88e6]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f483ba8e6ba]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f483af233dd]

stack trace 10.2.9-MariaDB-debug

Thread 1 (Thread 0x7f48382be700 (LWP 10838)):
 
#0  0x00007f483ae51428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#1  0x00007f483ae5302a in __GI_abort () at abort.c:89
 
#2  0x00007f483ae49bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x210b345f59 "mysql_table", file=file@entry=0x210b338268 "/home/alice/git/10.2/storage/innobase/handler/ha_innodb.cc", line=line@entry=22374, function=function@entry=0x210b34aaa0 <innobase_get_computed_value(dtuple_t const*, dict_v_col_t const*, dict_index_t const*, mem_block_info_t**, mem_block_info_t*, dict_field_t const*, THD*, TABLE*, dict_table_t const*, upd_t*, dict_foreign_t*)::__PRETTY_FUNCTION__> "dfield_t* innobase_get_computed_value(const dtuple_t*, const dict_v_col_t*, const dict_index_t*, mem_heap_t**, mem_heap_t*, const dict_field_t*, THD*, TABLE*, const dict_table_t*, upd_t*, dict_foreign"...) at assert.c:92
 
#3  0x00007f483ae49c82 in __GI___assert_fail (assertion=0x210b345f59 "mysql_table", file=0x210b338268 "/home/alice/git/10.2/storage/innobase/handler/ha_innodb.cc", line=22374, function=0x210b34aaa0 <innobase_get_computed_value(dtuple_t const*, dict_v_col_t const*, dict_index_t const*, mem_block_info_t**, mem_block_info_t*, dict_field_t const*, THD*, TABLE*, dict_table_t const*, upd_t*, dict_foreign_t*)::__PRETTY_FUNCTION__> "dfield_t* innobase_get_computed_value(const dtuple_t*, const dict_v_col_t*, const dict_index_t*, mem_heap_t**, mem_heap_t*, const dict_field_t*, THD*, TABLE*, const dict_table_t*, upd_t*, dict_foreign"...) at assert.c:101
 
#4  0x000000210ac7d602 in innobase_get_computed_value (row=0x7f47d0051598, col=0x7f47d003f0d8, index=0x7f47d00420c8, local_heap=0x7f48382bab88, heap=0x7f47d003ead0, ifield=0x0, thd=0x7f47d0000a98, mysql_table=0x0, old_table=0x0, parent_update=0x0, foreign=0x0) at /home/alice/git/10.2/storage/innobase/handler/ha_innodb.cc:22374
 
#5  0x000000210adffd93 in row_upd_store_v_row (node=0x7f47d0045f68, update=0x0, thd=0x7f47d0000a98, mysql_table=0x0) at /home/alice/git/10.2/storage/innobase/row/row0upd.cc:2160
 
#6  0x000000210adfffed in row_upd_store_row (node=0x7f47d0045f68, thd=0x7f47d0000a98, mysql_table=0x0) at /home/alice/git/10.2/storage/innobase/row/row0upd.cc:2221
 
#7  0x000000210ae023d3 in row_upd_del_mark_clust_rec (node=0x7f47d0045f68, index=0x7f47d00420c8, offsets=0x7f48382bb1f0, thr=0x7f47d004dc98, referenced=1, foreign=0, mtr=0x7f48382bb510) at /home/alice/git/10.2/storage/innobase/row/row0upd.cc:2969
 
#8  0x000000210ae02d54 in row_upd_clust_step (node=0x7f47d0045f68, thr=0x7f47d004dc98) at /home/alice/git/10.2/storage/innobase/row/row0upd.cc:3152
 
#9  0x000000210ae03245 in row_upd (node=0x7f47d0045f68, thr=0x7f47d004dc98) at /home/alice/git/10.2/storage/innobase/row/row0upd.cc:3269
 
#10 0x000000210ae03724 in row_upd_step (thr=0x7f47d004dc98) at /home/alice/git/10.2/storage/innobase/row/row0upd.cc:3415
 
#11 0x000000210ada5e0d in row_update_for_mysql (prebuilt=0x7f47d00453d8) at /home/alice/git/10.2/storage/innobase/row/row0mysql.cc:1947
 
#12 0x000000210ac65cf8 in ha_innobase::delete_row (this=0x7f47d003e260, record=0x7f47d0038e60 "\374\002") at /home/alice/git/10.2/storage/innobase/handler/ha_innodb.cc:9543
 
#13 0x000000210aa1eac8 in handler::ha_delete_row (this=0x7f47d003e260, buf=0x7f47d0038e60 "\374\002") at /home/alice/git/10.2/sql/handler.cc:6025
 
#14 0x000000210abab4b2 in mysql_delete (thd=0x7f47d0000a98, table_list=0x7f47d0042a40, conds=0x0, order_list=0x7f47d0005028, limit=18446744073709551614, options=0, result=0x0) at /home/alice/git/10.2/sql/sql_delete.cc:583
 
#15 0x000000210a7a531c in mysql_execute_command (thd=0x7f47d0000a98) at /home/alice/git/10.2/sql/sql_parse.cc:4612
 
#16 0x000000210a7afb29 in mysql_parse (thd=0x7f47d0000a98, rawbuf=0x7f47d0041e00 "delete from t1", length=14, parser_state=0x7f48382bd240, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7886
 
#17 0x000000210a79d84b in dispatch_command (command=COM_QUERY, thd=0x7f47d0000a98, packet=0x7f47d0007e89 "delete from t1", packet_length=14, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1812
 
#18 0x000000210a79c1be in do_command (thd=0x7f47d0000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360
 
#19 0x000000210a8e8b66 in do_handle_one_connection (connect=0x210e0f1e28) at /home/alice/git/10.2/sql/sql_connect.cc:1354
 
#20 0x000000210a8e88e6 in handle_one_connection (arg=0x210e0f1e28) at /home/alice/git/10.2/sql/sql_connect.cc:1260
 
#21 0x00007f483ba8e6ba in start_thread (arg=0x7f48382be700) at pthread_create.c:333
 
#22 0x00007f483af233dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Sergei Golubchik [ 2017-09-15 ]

It's not related to JSON, here's another test case:

source include/have_innodb.inc;
 
CREATE TABLE t1 (
 
        id int not null primary key,
 
        id2 int AS (id) VIRTUAL,
 
        KEY id2 (id2)) ENGINE=innodb;
 
 
CREATE TABLE t2 (id int,KEY (id),
 
        CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES t1 (id) ON DELETE CASCADE) ENGINE=innodb;
 
 
insert into t1 (id) values (1), (2) ;
 
insert into t2 (id) values (1), (2) ;
 
 
delete from t1;

Generated at Thu Feb 08 08:07:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.