[MDEV-22230] Unexpected ER_ERROR_ON_RENAME upon DROP non-existing FOREIGN KEY with ALGORITHM=COPY Created: 2020-04-13  Updated: 2023-11-30  Resolved: 2023-11-27

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-28904 DROP CONSTRAINT ..., ALGORITHM=COPY ... Stalled
relates to MDEV-20480 Obsolete internal parser for FK in In... Closed
relates to MDEV-22232 Server crashes in rpl_sql_thread_info... Closed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 
--error ER_CANT_DROP_FIELD_OR_KEY
 
ALTER TABLE t1 DROP FOREIGN KEY x, ALGORITHM=COPY;
 
 
 
# Cleanup
 
DROP TABLE t1;

10.5 8c0b9880

 
query 'ALTER TABLE t1 DROP FOREIGN KEY x, ALGORITHM=COPY' failed with wrong errno 1025: 'Error on rename of './test/t1' to './test/#sql2-10fa-4' (errno: 152 "Cannot delete a parent row")', instead of 1091...

Without ALGORITHM=COPY it fails with ER_CANT_DROP_FIELD_OR_KEY as expected.

There is an ancient upstream bug https://bugs.mysql.com/bug.php?id=14347 which seems related, but I'm not sure it's quite the same.

 Comments   
Comment by Jan Lindström (Inactive) [ 2022-06-20 ]

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

Comment by Jan Lindström (Inactive) [ 2022-08-05 ]
Comment by Elena Stepanova [ 2023-07-04 ]

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")

Comment by Marko Mäkelä [ 2023-07-04 ]

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.

Comment by Thirunarayanan Balathandayuthapani [ 2023-08-08 ]

Needs different patch. So taking over this issue

Comment by Thirunarayanan Balathandayuthapani [ 2023-11-06 ]

Patch is in bb-10.4-MDEV-22230

Comment by Thirunarayanan Balathandayuthapani [ 2023-11-08 ]

https://github.com/MariaDB/server/pull/2822

Comment by Marko Mäkelä [ 2023-11-08 ]

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.

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