MySQL Bugs: #45701: Falcon doesn't allow ALTER TABLE to lock more than one table.

Bug #45701	Falcon doesn't allow ALTER TABLE to lock more than one table.
Submitted:	24 Jun 2009 12:57	Modified:	26 May 2010 17:48
Reporter:	Dmitry Lenev	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.1-fk	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any
Tags:	F_HANDLER

Description:
Unlike other storage engines Falcon assumes that ALTER TABLE can't lock more than one table. StorageInterface::external_lock() method emits error when ALTER TABLE is changed in such way that it starts doing this.

Unfortunately new foreign keys implementation requires such functionality to be supported by engine, e.g. in new implementation ALTER TABLE ... ADD CONSTRAINT will try to open and lock both child (altered) and parent tables.

How to repeat:
Change ALTER TABLE code to lock several tables and observe error emitted.
Alternatively inspect code in StorageInterface::external_lock().

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/77257

2746 Kevin Lewis	2009-06-25
      Bug#45701 - If a table has two foreign key relationships with one other table, an alter table that drops one of the FKs will call external_lock twice for each table. And if a table has a foreign key relationship with another table, an alter that creates another foreign key relationship between the two tables will also call external_lock twice for each table.
      
      So it is necessary for Falcon to support multiple calls to external_lock by the same connection to the same table.  This was not needed before the Foriegn Key implementation.  
      
      So the current Connection pointer is sent into Table::SetAlter() and stored in Table::alterConnection.   And alterIsActive is now an int instead of a boolean.  If the same connection locks the table a second time, alterIsActive is incremented and alterConnection remains set.  It is not set to NULL until alterIsActive is decremented to zero.

Dmitry Lenev wrote; 
I have applied your patch to mysql-6.1-fk-stage tree, built
server (I am on Linux so I am not affected by the above problem
with InnoDB), and re-run my tests for Falcon engine.

I can confirm that now it successfully passes most of the
tests for milestone 13 of WL#148 "Foreign keys".

Patch pushed to mysql-6.0-falcon-team

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/77699

2723 Dmitry Lenev	2009-07-02
      Bug#45701 - If a table has two foreign key relationships with one other table, an
      alter table that drops one of the FKs will call external_lock twice for each table.
      And if a table has a foreign key relationship with another table, an alter that
      creates another foreign key relationship between the two tables will also call
      external_lock twice for each table.
      
      So it is necessary for Falcon to support multiple calls to external_lock by the
      same connection to the same table.  This was not needed before the Foreign Key
      implementation.
      
      So the current Connection pointer is sent into Table::SetAlter() and stored in
      Table::alterConnection.   And alterIsActive is now an int instead of a boolean.
      If the same connection locks the table a second time, alterIsActive is incremented
      and alterConnection remains set.  It is not set to NULL until alterIsActive is
      decremented to zero.
      
      Manually applied patch from Falcon team tree to mysql-6.1-fk tree.