Jan Lindström (Inactive) added a comment - 2022-06-20 07:42 While testing my fix I noticed that DROP CONSTRAINT ..., ALGORITHM=COPY has no effect. If you remove ALGORITHM it works as expected. CREATE TABLE t1 (a INT primary key, b int) ENGINE=InnoDB; CREATE TABLE t2 (a int primary key, b int, constraint b foreign key (b) references t1(a)) engine=innodb; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`), CONSTRAINT `b` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t2 DROP CONSTRAINT b, ALGORITHM=COPY; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`), CONSTRAINT `b` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Jan Lindström (Inactive) added a comment - 2022-08-05 10:35 http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.4-MDEV-22230 https://github.com/MariaDB/server/commit/ba80fb383c7172eaa77375d2945fad77829df9c6

Elena Stepanova added a comment - 2023-07-04 10:02 Maybe a more convincing test case would be --source include/have_innodb.inc   CREATE TABLE t1 (a INT ) ENGINE=InnoDB; ALTER TABLE t1 MODIFY COLUMN a VARCHAR (2), DROP FOREIGN KEY IF EXISTS x;   # Cleanup DROP TABLE t1; as it doesn't tamper explicitly with the algorithm and it has "IF EXISTS" for the foreign key drop, so it's a perfectly normal valid ALTER which shouldn't fail. But it does 11.1 3883eb63 mysqltest: At line 4: query 'ALTER TABLE t1 MODIFY COLUMN a VARCHAR(2), DROP FOREIGN KEY IF EXISTS x' failed: ER_ERROR_ON_RENAME (1025): Error on rename of './test/t1' to './test/#sql-backup-246583-4' (errno: 152 "Cannot delete a parent row" )

Marko Mäkelä added a comment - 2023-07-04 11:45 The FOREIGN KEY handling was refactored in 10.5.0 by MDEV-20480 . I think that it would make sense to fix this only in 10.5 and later major releases, because 10.4 will reach end of life soon.

Thirunarayanan Balathandayuthapani added a comment - 2023-08-08 10:22 Needs different patch. So taking over this issue

Thirunarayanan Balathandayuthapani added a comment - 2023-11-06 09:49 Patch is in bb-10.4- MDEV-22230

Thirunarayanan Balathandayuthapani added a comment - 2023-11-08 11:48 https://github.com/MariaDB/server/pull/2822

Marko Mäkelä added a comment - 2023-11-08 12:48 This needs some more work, including test coverage to ensure that ALTER TABLE some_table DROP FOREIGN KEY IF EXISTS fk_in_another_table will not drop a constraint by that name from another table.