Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.6, 10.2.8, 10.2
-
None
-
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
|