[MDEV-29887] Drop of non-existing FK succeeds for temporary table Created: 2022-10-26  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Temporary, Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: upstream-fixed


 Description    

--source include/have_innodb.inc
 
 
 
CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB;
 
INSERT INTO t VALUES (1);
 
ALTER TABLE t DROP FOREIGN KEY fk;
 
 
 
# Cleanup
 
DROP TABLE t;

10.3 77951dd7

 
CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB;
 
INSERT INTO t VALUES (1);
 
ALTER TABLE t DROP FOREIGN KEY fk;
 
DROP TABLE t;

So, although there is no foreign key fk (or any other), ALTER succeeds.
Reproducible on all current version lines of MariaDB and on MySQL 5.7. Not reproducible on MySQL 8.0.

 Comments   
Comment by Elena Stepanova [ 2022-10-26 ]

According to marko,

The culprit should be outside InnoDB, because ALTER TABLE on temporary tables always goes through the ALGORITHM=COPY route.

Comment by Marko Mäkelä [ 2022-10-26 ]

I do not think that temporary tables ever supported FOREIGN KEY constraints in InnoDB, not even when they were actually persistent tables (before InnoDB in MySQL 5.7 and MariaDB 10.2 stopped writing any log for them).

Comment by Elena Stepanova [ 2022-10-26 ]

And yet, adding an invalid FK to a temporary InnoDB table causes typical "incorrectly formed" errors (unlike adding the same to a MyISAM table, which truly doesn't care). So if it's unsupported, it's inconsistently unsupported.

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