From 73c391afc5f9315658e0486a375dbce2d109b9b5 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 17 Feb 2022 10:48:24 +0200 Subject: MDEV-27583 InnoDB uses different constants for FK cascade error message in SQL vs error log convert_error_code_to_mysql(): Use the correct limit FK_MAX_CASCADE_DEL in the error message. The DICT_FK_MAX_RECURSIVE_LOAD applies to the number of foreign key constraints in table definitions, not to the number of rows that are visited while processing a foreign key constraint. --- mysql-test/suite/innodb/r/foreign_key.result | 23 +++++++++++++++++++++-- mysql-test/suite/innodb/t/foreign_key.test | 23 +++++++++++++++++++++++ storage/innobase/handler/ha_innodb.cc | 2 +- 3 files changed, 45 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index efaa9028eae..d6420118f34 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -659,9 +659,9 @@ START TRANSACTION WITH CONSISTENT SNAPSHOT; connection default; DELETE IGNORE FROM t1 WHERE b = 1; Warnings: -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 +Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB -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 +Warning 152 InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop extra constraints and try again Warning 1296 Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB SELECT a FROM t1 FORCE INDEX(a); a @@ -812,4 +812,23 @@ ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint Parsing foreign keys 3... ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") Parsing foreign keys 4... +# +# MDEV-27583 InnoDB uses different constants for FK cascade +# error message in SQL vs error log +# +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); +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 15. 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 +DROP TABLE t1; +FOUND 1 /InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*/ in mysqld.1.err # End of 10.2 tests diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 08f2c529659..1d4e5fd9d2a 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -822,6 +822,29 @@ eval create table t1($fk_ref int primary key, $fk_field int, $constrs) engine in drop table t1; --enable_query_log +--echo # +--echo # MDEV-27583 InnoDB uses different constants for FK cascade +--echo # error message in SQL vs error log +--echo # + +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; + +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_PATTERN= InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15.*; +-- source include/search_pattern_in_file.inc + --echo # End of 10.2 tests --source include/wait_until_count_sessions.inc diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index a36ed4ddbcd..0c0900bd744 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -1953,7 +1953,7 @@ convert_error_code_to_mysql( "constraints that exceed max " "depth of %d. Please " "drop extra constraints and try " - "again", DICT_FK_MAX_RECURSIVE_LOAD); + "again", FK_MAX_CASCADE_DEL); return(HA_ERR_FK_DEPTH_EXCEEDED); case DB_CANT_CREATE_GEOMETRY_OBJECT: -- cgit v1.2.1