MDEV-23722 InnoDB: Assertion: result != FTS_INVALID in fts_trx_row_get_new_state

Marking of deletion of row in fts index happens twice in
self-referential foreign key relation. So while performing
referential checks of foreign key, InnoDB can avoid updating
of fts index if the foreign key has self-referential relationship.

Reviewed-by: Marko Mäkelä

4 files changed, 154 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index e9fbc742379..74d7cbb7d9a 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -244,3 +244,38 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 XA END 'xid';
 XA ROLLBACK 'xid';
 DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+f1 VARCHAR(10), f2 VARCHAR(10),
+f3 VARCHAR(10), f4 VARCHAR(10),
+f5 VARCHAR(10), f6 VARCHAR(10),
+f7 VARCHAR(10), f8 VARCHAR(10),
+INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+(3, 'innodb', 'innodb', 'innodb', 'innodb',
+'innodb', 'innodb', 'innodb', 'innodb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
+START TRANSACTION;
+DELETE FROM t1 where f1='mariadb';
+SELECT * FROM t1;
+pk	f1	f2	f3	f4	f5	f6	f7	f8
+2	NULL	mariadb	mariadb	mariadb	mariadb	mariadb	mariadb	mariadb
+3	innodb	innodb	innodb	innodb	innodb	innodb	innodb	innodb
+ROLLBACK;
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
+START TRANSACTION;
+DELETE FROM t1 where f3='mariadb';
+SELECT * FROM t1;
+pk	f1	f2	f3	f4	f5	f6	f7	f8
+3	innodb	innodb	innodb	innodb	innodb	innodb	innodb	innodb
+ROLLBACK;
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+UPDATE t1 SET f6='update';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
+UPDATE t1 SET f6='cascade';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 3269aeed62b..29bf91f2f24 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -299,6 +299,41 @@ ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE;# Cleanu
 XA END 'xid';
 XA ROLLBACK 'xid';
 DROP TABLE t1;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+                 f1 VARCHAR(10), f2 VARCHAR(10),
+		 f3 VARCHAR(10), f4 VARCHAR(10),
+		 f5 VARCHAR(10), f6 VARCHAR(10),
+		 f7 VARCHAR(10), f8 VARCHAR(10),
+		 INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+		 INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+                       'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+                      (2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+                       'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+                      (3, 'innodb', 'innodb', 'innodb', 'innodb',
+                       'innodb', 'innodb', 'innodb', 'innodb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
+START TRANSACTION;
+DELETE FROM t1 where f1='mariadb';
+SELECT * FROM t1;
+ROLLBACK;
+
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
+
+START TRANSACTION;
+DELETE FROM t1 where f3='mariadb';
+SELECT * FROM t1;
+ROLLBACK;
+
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f6='update';
+
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f6='cascade';
+DROP TABLE t1;
 #
 # End of 10.1 tests
 #
diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
index 9f5c1f271fc..43f79241626 100644
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
@@ -913,4 +913,46 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
 DROP TABLE t4;
+#
+# InnoDB: Failing assertion: result != FTS_INVALID in
+#        fts_trx_row_get_new_state
+#
+SET FOREIGN_KEY_CHECKS=1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+f1 VARCHAR(10), f2 VARCHAR(10),
+f3 VARCHAR(10), f4 VARCHAR(10),
+f5 VARCHAR(10), f6 VARCHAR(10),
+f7 VARCHAR(10), f8 VARCHAR(10),
+FULLTEXT(f1), FULLTEXT(f2), FULLTEXT(f3), FULLTEXT(f4),
+FULLTEXT(f5), FULLTEXT(f6), FULLTEXT(f7), FULLTEXT(f8),
+INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+(3, 'innodb', 'innodb', 'innodb', 'innodb',
+'innodb', 'innodb', 'innodb', 'innodb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
+START TRANSACTION;
+DELETE FROM t1 where f1='mariadb';
+SELECT * FROM t1;
+pk	f1	f2	f3	f4	f5	f6	f7	f8
+2	NULL	mariadb	mariadb	mariadb	mariadb	mariadb	mariadb	mariadb
+3	innodb	innodb	innodb	innodb	innodb	innodb	innodb	innodb
+ROLLBACK;
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
+START TRANSACTION;
+DELETE FROM t1 where f3='mariadb';
+SELECT * FROM t1;
+pk	f1	f2	f3	f4	f5	f6	f7	f8
+3	innodb	innodb	innodb	innodb	innodb	innodb	innodb	innodb
+ROLLBACK;
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+UPDATE t1 SET f6='update';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
+UPDATE t1 SET f6='cascade';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DROP TABLE t1;
 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test
index 6ef0452f4c6..e8a3035ba25 100644
--- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test
+++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test
@@ -907,4 +907,46 @@ DROP TABLE t2;
 DROP TABLE t3;
 DROP TABLE t4;
 
+--echo #
+--echo # InnoDB: Failing assertion: result != FTS_INVALID in
+--echo #        fts_trx_row_get_new_state
+--echo #
+SET FOREIGN_KEY_CHECKS=1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+                 f1 VARCHAR(10), f2 VARCHAR(10),
+                 f3 VARCHAR(10), f4 VARCHAR(10),
+                 f5 VARCHAR(10), f6 VARCHAR(10),
+		 f7 VARCHAR(10), f8 VARCHAR(10),
+		 FULLTEXT(f1), FULLTEXT(f2), FULLTEXT(f3), FULLTEXT(f4),
+		 FULLTEXT(f5), FULLTEXT(f6), FULLTEXT(f7), FULLTEXT(f8),
+                 INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+		 INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+                       'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+                      (2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+                       'mariadb', 'mariadb', 'mariadb', 'mariadb'),
+		      (3, 'innodb', 'innodb', 'innodb', 'innodb',
+		       'innodb', 'innodb', 'innodb', 'innodb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
+START TRANSACTION;
+DELETE FROM t1 where f1='mariadb';
+SELECT * FROM t1;
+ROLLBACK;
+
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
+
+START TRANSACTION;
+DELETE FROM t1 where f3='mariadb';
+SELECT * FROM t1;
+ROLLBACK;
+
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f6='update';
+
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f6='cascade';
+DROP TABLE t1;
+
 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;