Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13708

Crash with indexed virtual columns and FK cascading deletes

    Details

      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
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                bgrossman Brian Grossman
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: