[MDEV-4439] ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS does not work if constraint name is not used Created: 2013-04-25  Updated: 2014-02-06  Resolved: 2014-02-06

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.2
Fix Version/s: 10.0.8

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-318 IF (NOT) EXIST clauses for ALTER TABL... Closed

 Description   

ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS creates index on the given column using the key id provided but that name is not the same as constraint name (at least on InnoDB). That makes this feature unusable (and some cases to crash).

Problem is that MySQL does not really know foreign key constraint names, it knows key names. Thus 

alter table t2 add constraint f foreign key if not exists (i) references t1(pk)

Would create foreign key constraint f on InnoDB data dictionary and MySQL key f on MySQL data dictionary. But

alter table t2 add foreign key if not exists f(i) references t1(pk);

Would create foreign key constraint t2_ibfk_1 on InnoDB data dictionary and MySQL key f on MySQL data dictionary. In this case you can't do:

alter table t2 drop foreign key if exists t2_ibfk_1;

Because, there is no key t2_ibfk_1 on MySQL data dictionary, and 

alter table t2 drop foreign key if exists f;

would not also work because there is no foreign key constraint f on InnoDB data dictionary while there is a key f on MySQL. There is no real test cases on mysql-test suite for adding/dropping foreign keys with if [not] exists feature.

Test case:

create table t1 (pk int primary key) engine=InnoDB;
 
create table t2 (i int) engine=InnoDB;
 
alter table t2 add foreign key if not exists f(i) references t1(pk);
 
show create table t2;
 
alter table t2 drop foreign key if exists f;
 
show warnings;
 
alter table t2 drop foreign key if exists t2_ibfk_1;
 
show warnings;
 
show create table t2;
 
drop table t2,t1;
 
create table t1 (pk int primary key) engine=InnoDB;
 
create table t2 (i int) engine=InnoDB;
 
alter table t2 add constraint f foreign key if not exists (i) references t1(pk);
 
show create table t2;
 
alter table t2 drop foreign key if exists f;
 
show warnings;
 
show create table t2;
 
alter table t2 add constraint f foreign key if not exists (i) references t1(pk);
 
show create table t2;
 
alter table t2 add constraint f foreign key if not exists (i) references t1(pk);
 
show warnings;
 
show create table t2;
 
drop table t2,t1;



 Comments   
Comment by Jan Lindström (Inactive) [ 2013-09-11 ]

While MySQL/MariaDB parser does parse foreign key SQL-clauses result is not passed to handler ::create() function. This fact has led to situation where storage engine developer has to create his/her own method to parse and handle foreign key SQL-clauses. InnoDB does this at dict_create_foreign_constraints_low function on dict/dict0dict.cc file. But this file was not updated when IF (NOT) EXISTS clause was introduced.

Comment by Jan Lindström (Inactive) [ 2013-11-05 ]

Additional problem: While the alter table add foreign key if not exists does produce sometimes warning the actual foreign key is created:

CREATE TABLE fk_t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
CREATE TABLE fk_t2(a INT) ENGINE=InnoDB;
ALTER TABLE fk_t2 ADD FOREIGN KEY tt(a) REFERENCES fk_t1(a);
ALTER TABLE fk_t2 ADD FOREIGN KEY IF NOT EXISTS tt(a) REFERENCES fk_t1(a);
Warnings:
Note 1061 Duplicate key name 'tt'
ALTER TABLE fk_t2 ADD CONSTRAINT tt FOREIGN KEY IF NOT EXISTS (a) REFERENCES fk_t1(a);
Warnings:
Note 1061 Duplicate key name 'tt'
show create table fk_t2;
Table Create Table
fk_t2 CREATE TABLE `fk_t2` (
`a` int(11) DEFAULT NULL,
KEY `tt` (`a`),
CONSTRAINT `tt` FOREIGN KEY (`a`) REFERENCES `fk_t1` (`a`),
CONSTRAINT `fk_t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `fk_t1` (`a`),
CONSTRAINT `fk_t2_ibfk_2` FOREIGN KEY (`a`) REFERENCES `fk_t1` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Comment by Alexey Botchkov [ 2013-11-08 ]

Here is my patch proposal for the server part:
http://lists.askmonty.org/pipermail/commits/2013-November/005655.html

Comment by Sergei Golubchik [ 2014-01-14 ]

as discussed, the server part should be different, in particular: if it's a DROP IF EXISTS FOREIGN KEY and the server thinks that the key does not exist, it should not remove it from the drop list or remove the DROP_FOREIGN_KEY flag. It's because the server doesn't know true foreign key names, as they were generated by the engine.

Comment by Jan Lindström (Inactive) [ 2014-01-29 ]

Problem is more complicated because MySQL server does not know the foreign key names. This leads to situation where both 

ALTER TABLE fk_t2 ADD FOREIGN KEY IF NOT EXISTS tt(a) REFERENCES fk_t1(a);

Here key name tt is not used as a foreign key name inside InnoDB. Thus server does not know if there is a foreign key for columns (a). Correct behaviour is not to create foreign key to that column if it already exists. Similarly,

alter table t2 drop foreign key if exists f;

Here the name f may or may not be the correct foreign key name. Thus as above correctly suggest, server needs to forward the query to storage engine. The real problem here is that InnoDB currently does not use the alter table flags, instead InnoDB parses the query again, e.g.

alter table t1 add foreign key if not exits k, add foreign key if not exits j, drop froreign key if exists m;

Server might know some of those names and some not, but InnoDB parser will parse all parts of that simple query and tries to do all the changes anyway.

Comment by Alexey Botchkov [ 2014-01-31 ]

Another fix proposal:
http://lists.askmonty.org/pipermail/commits/2014-January/005851.html

Comment by Alexey Botchkov [ 2014-02-01 ]

Yet another fix:
http://lists.askmonty.org/pipermail/commits/2014-February/005855.html

Generated at Thu Feb 08 06:56:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.