Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
-
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | InnoDB uses different constants for error message for SQL vs error log | InnoDB uses different constants for FK cascade error message in SQL vs error log |
| issue.field.resolutiondate | 2022-02-17 09:12:55.0 | 2022-02-17 09:12:55.275 |
| Fix Version/s | 10.2.44 [ 27514 ] | |
| Fix Version/s | 10.3.35 [ 27512 ] | |
| Fix Version/s | 10.4.25 [ 27510 ] | |
| Fix Version/s | 10.5.16 [ 27508 ] | |
| Fix Version/s | 10.6.8 [ 27506 ] | |
| Fix Version/s | 10.7.4 [ 27504 ] | |
| Fix Version/s | 10.8.3 [ 27502 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
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).