MySQL Bugs: #34944: Foreign Keys: ignoring on update|delete clause after match clause

Bug #34944	Foreign Keys: ignoring on update\|delete clause after match clause
Submitted:	28 Feb 2008 22:36	Modified:	16 Jan 2014 3:06
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0/5.1/6.1.0-fk-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk but I expect this happens in earlier versions.

I create an InnoDB table with a clause that looks like this:
FOREIGN KEY (column_name) MATCH FULL ON DELETE CASCADE.
I use "show create" to show that the foreign key description
is accepted, but the ON DELETE CASCADE clause was silently
ignored.
In general: if there is a MATCH clause, then any
ON DELETE or ON UPDATE clause is ignored.

The MySQL Reference Manual "CREATE TABLE" description
http://dev.mysql.com/doc/refman/6.0/en/create-table.html
does say that InnoDB ignores "inline REFERENCES" but
does not say that InnoDB will ever ignore ON UPDATE|DELETE.
So either the manual is missing this, or the server is wrong.

How to repeat:
mysql> create table t1 (s1 int primary key);
Query OK, 0 rows affected (0.09 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1 (s1 int primary key) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t2 (s1 int, foreign key (s1) references t1 (s1) match full on delete cascade) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `s1` int(11) DEFAULT NULL,
  KEY `s1` (`s1`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thank you for the bug report. Verified as described.

drop table if exists t1;
create table t1 (s1 int primary key);
drop table t1;
create table t1 (s1 int primary key) engine=innodb;
create table t2 (s1 int, foreign key (s1) references t1 (s1) match full on delete
cascade) engine=innodb;
show create table t2;

Changed category to InnoDB.

As far as I understand the InnoDB code, the function dict_create_foreign_constraints_low() does not know of the 'MATCH' syntax elements. If it does not find 'ON' after the referenced table's columns list, it ignores the rest of the query string.

The function dict_print_info_on_foreign_key_in_create_format() doesn't know of 'MATCH' either. It won't print the info even if it would be stored inside of InnoDB.

It is puzzling that [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] was ever documented, as there is no MySQL storage engine to my knowledge that ever supported this feature.  It seems the real bug is documenting that MATCH syntax at all.

The triage codes here are questionable.  There is not a substantial impact in ignoring the syntax, since there is documentation indicating these options are silently ignored.  A (full?) workaround exists in the form of triggers.  If the CHECK clause, which "is parsed but ignored by all storage engines" were implemented, it could also provide a (partial) workaround.

This should be coded as a feature request, not a P2 bug. It should not be targeted at release 5.1 or even 6.0, but should be documented as part of the design requirements for the FK support project in 6.1, as something appropriate should be done for all engines whether they support referential integrity or not, and whether or not they record and replay or enforce any of the MATCH clauses.

Changing status to "to be fixed later" and request change of D/W/I codes, as well as Priority and removal of internal tag SR51SP.

Thank you for removing the SR51SP tag and setting the target to 6.x.

Let me ask that you please also review D3/W1 and I3, as well as the P2 priority.

Rationale:

A D4 is a "minor dysfunction", which is a behavior contrary to specification or documentation.  Because this functionality was *NEVER* implemented, it's only the case that what is happening (w.r.t. MATCH) is "wrong".  It is not correct to ignore the ON DELETE CASCADE clause, but it is only when MATCH is specified, not a problem with ON DELETE CASCADE (or other actions).  When MATCH is not specified ON DELETE CASCADE (etc) works just fine.   Since there is no MySQL user documentation for this clause, the problem is just as much a problem with the documentation as code.

There is an obvious and complete workaround (don't specify MATCH!), so the present W1 rating of no workaround is simply wrong.

It is also hard to justify the I3 ("substantial") rating for "Impact", since no user has ever reported the problem for many years.

Therefore, the P2 rating should be revised to P3 or even P4.

Thank you for removing the SR51SP tag and setting the target to 6.x.

Let me ask that you please also review D3/W1 and I3, as well as the P2 priority.

Rationale:

A D4 is a "minor dysfunction", which is a behavior contrary to specification or documentation.  Because this functionality was *NEVER* implemented, it's only the case that what is happening (w.r.t. MATCH) is "wrong".  It is not correct to ignore the ON DELETE CASCADE clause, but it is only when MATCH is specified, not a problem with ON DELETE CASCADE (or other actions).  When MATCH is not specified ON DELETE CASCADE (etc) works just fine.   Since there is no MySQL user documentation for this clause, the problem is just as much a problem with the documentation as code.

There is an obvious and complete workaround (don't specify MATCH!), so the present W1 rating of no workaround is simply wrong.

It is also hard to justify the I3 ("substantial") rating for "Impact", since no user has ever reported the problem for many years.

Therefore, the P2 rating should be revised to P3 or even P4.

noted this on WL#3333 Foreign keys with match full or match partial.

If this is ever implemented, then it should address issue mentioned here.