[MDEV-27583] InnoDB uses different constants for FK cascade error message in SQL vs error log Created: 2022-01-23  Updated: 2022-02-17  Resolved: 2022-02-17

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3

Type: Bug Priority: Trivial
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None


 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (
 
    a INT, b INT, KEY(b),
 
    CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE
 
) ENGINE=InnoDB;
 
INSERT INTO t1 (a,b) VALUES
 
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
 
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0);
 
--error ER_GET_ERRMSG
 
DELETE FROM t1 WHERE b = 1;
 
SHOW WARNINGS;
 
 
 
DROP TABLE t1;

The message returned to the query uses DICT_FK_MAX_RECURSIVE_LOAD (20):

10.2 5d57e04b

 
DELETE FROM t1 WHERE b = 1;
 
ERROR HY000: Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
 
SHOW WARNINGS;
 
Level	Code	Message
 
Warning	152	InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again
 
Error	1296	Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB

The message written in the error log uses FK_MAX_CASCADE_DEL (15):

2022-01-23  2:38:51 140072887355136 [ERROR] InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop excessive foreign constraints and try again

The latter seems correct.

 Comments   
Comment by Marko Mäkelä [ 2022-02-17 ]

Thank you. The DICT_FK_MAX_RECURSIVE_LOAD (20) only applies to loading table definitions, while FK_MAX_CASCADE_DEL (15) is what matters here. I see that the test is only inserting 16 records, so it can only possibly hit the FK_MAX_CASCADE_DEL but not DICT_FK_MAX_RECURSIVE_LOAD (which to my understanding would require creating 20 foreign key constraints, while the test only creates one).

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