MySQL Bugs: #22707: Partitions: error message with MyISAM foreign key clause

Bug #22707	Partitions: error message with MyISAM foreign key clause
Submitted:	26 Sep 2006 18:55	Modified:	9 Apr 2008 15:03
Reporter:	Peter Gulutzan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
I expect MySQL to ignore foreign-key clauses when engine=MyISAM.
But if there are partitions, I get an error message.

How to repeat:
mysql> create table tp (s1 int, foreign key (s1) references tp0 (s1)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table tp;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tp (s1 int, foreign key (s1) references tp0 (s1)) engine=myisam partition by hash (s1);
ERROR 1215 (HY000): Cannot add foreign key constraint

Thank you for the bug report. Verified as described.

So if we are to ignore foreign key constraints on partitioned tables then we'll ignore it for
all engines. It would be very strange to ignore FK constraints on some partitioned tables
and report an error on some. Especially if the one where we report an error is the one that
normally supports FK constraints.

"So if we are to ignore foreign key constraints on partitioned tables then we'll ignore it for all engines."

Ignoring foreign key constraints for partitioned InnoDB tables is absolutely the *wrong* thing to do. Please do not do this under any circumstances.

There should be an error if foreign keys are supported by the storage engine but made impossible by some other feature being utilized, in this case partitioning.

I'm closing this bug as 'Not a bug' after consultation with Mikael and Peter G.

Here is Peter's answer to my question:
> What to do if a FK is to be used on a partitioned table:
> 1) always issue an error (As is, + does not accept any mistake from  
> the
> user, - not consistent with non partitioned behavior)

Although I reported this bug, I now feel "1)" is best.

a. It made sense to say that there should be no error so that
we would be consistent with non-partitioned MyISAM behaviour.
But we know that behaviour is on the "gotchas" list that we
intend to fix some day or some year.

b. Things have been like this for a long time.

c. Now that you have listed all the alternatives, I realize
that they are no better than what we have now.

d. A fix now will probably be superseded in 6.1 due to WL#148.

I think you agree. And I think Mikael would agree, since he
coded this way in the first place. Please say Bug#22707 is "not a bug".
The only request I'd make is that the error message mention
both "foreign key" and "partition".

> 2) never issue an error or warning (+ consistent with non partitioned
behavior - very confusing with storage engines that support FK)
> 3) only issue an error when using InnoDB ( + consistent with non
> partitioned behavior, shows the restriction of partitioning on  
> InnoDB, -
> not entirely consistent with non partitioned behavior)
> 4) always issue a warning ( + allows non partitioned behavior but
> informs the user it is ignored, - false security, users can miss the
> warning.)
> 5) only issue a warning when using InnoDB ( + consistent with non
> partitioned behavior - false security, users can miss the warning.)
>
> I would probably go for 1) even though it to some extent breaks
> expected/old behavior, it is in my eyes correct. But I could do 3),  
> 4) or 5) too if
> that is a better solution in your eyes. I rather do not do 2) since  
> it could lead to confusion with partitioned InnoDB tables.