Bug #34455 Ambiguous foreign keys syntax is accepted

• Submitted: 11 Feb 2008 11:51
• Modified: 6 Mar 2010 20:22
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 4.1, 5.0, 5.1
• OS: Any
• Assigned to: Marc ALFF
• CPU Architecture: Any

[11 Feb 2008 11:51] Dmitry Lenev

Description:
For non-InnoDB tables MySQL accepts CREATE TABLE statements with FOREIGN KEY clauses which has non-standard and ambiguous syntax. These clauses, for example, can contain duplicate ON UPDATE or ON DELETE subclauses with different referential actions. Also non-standard order between MATCH and referential actions clauses is accepted. This syntax is not documented as supported in manual.

Although currently this does not cause much problems since we ignore FK definitions for non-InnoDB tables anyway, this might cause problems in future, especially when we will implement FK support for many engines.

How to repeat:
create table parent (pk int primary key );
create table child (fk int, foreign key ttt (fk) references parent (pk) on delete cascade on delete set null match simple);

Suggested fix:
Fix parser to accept only standard syntax.

[13 Feb 2008 18:06] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42234

ChangeSet@1.2551, 2008-02-13 11:05:36-07:00, [e-mail]. +4 -0
  Bug#34455 (Ambiguous foreign keys syntax is accepted)
  
  Before this fix, the parser would accept illegal syntaxes in a FOREIGN KEY
  clause.
  In particular, the following was accepted:
  - multiple MATCH clauses
  - multiple ON DELETE clauses
  - multiple ON UPDATE clauses
  - MATCH clauses specified after ON UPDATE or ON DELETE
  In case of multiple redundant clauses, this leads to confusion,
  and implementation dependent results (last one wins).
  
  With this fix, these illegal syntaxes are now properly rejected.

[3 Mar 2008 18:19] Bugs System

Pushed into 6.0.5-alpha

[20 Mar 2008 18:48] Paul DuBois

Noted in 6.0.5 changelog.

[25 Jan 2010 11:58] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98040

2973 Marc Alff	2010-01-25
      Bug#34455 Ambiguous foreign keys syntax is accepted
      
      Backport from 6.0 to 5.5

[25 Jan 2010 12:12] Marc ALFF

Backport to 5.5 queued in:
- mysql-next-mr-marc

[13 Feb 2010 8:37] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100213083436-9pesg4h55w1mekxc) (version source revid:[e-mail]-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)

[13 Feb 2010 8:39] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100213083327-cee4ao3jpg33eggv) (version source revid:[e-mail]-20100211135018-1f9dbghg0itszigo) (pib:16)

[13 Feb 2010 13:13] Marc ALFF

Doc team: to document in mysql-next-mr / Celosia

[13 Feb 2010 17:59] Paul DuBois

Already fixed in earlier 6.0.x.

Setting report to Need Merge pending push of Celosia to release tree.

[6 Mar 2010 10:56] Bugs System

Pushed into 5.5.3-m3 (revid:[e-mail]-20100306103849-hha31z2enhh7jwt3) (version source revid:[e-mail]-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)

[6 Mar 2010 20:22] Paul DuBois

Noted in 5.5.3 changelog.