MySQL Bugs: #42099: Falcon: Online ALTER add/drop primary key

Bug #42099	Falcon: Online ALTER add/drop primary key
Submitted:	14 Jan 2009 3:46	Modified:	15 May 2009 12:59
Reporter:	Christopher Powers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0	OS:	Any
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	F_ONLINE ALTER

Description:
Falcon ONLINE ALTER does not support add/drop primary key.

How to repeat:
CREATE TABLE T1 (s1 int) ENGINE=FALCON;

ALTER ONLINE TABLE T1 ADD PRIMARY KEY is1 (s1);

Fails with :

'ERROR 42000: This version of MySQL doesn't yet support 'ALTER ONLINE TABLE t1 ADD PRIMARY KEY (s1)'

Suggested fix:
Primary keys must be handled somewhat differently. The existing online alter implementation will have to be modified to use the internal 'upgrade' mechanism to add a primary key.

Thank you for the bug report. Verified as described:

Server version: 6.0.10-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE T1 (s1 int) ENGINE=FALCON;
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> ALTER ONLINE TABLE T1 ADD PRIMARY KEY is1 (s1);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'ALTER ONLINE TABLE T1 ADD PRIMARY KEY is1 (s1)'
mysql>

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

2957 Christopher Powers	2009-01-14
      Bug #42099 Falcon: Online ALTER add/drop primary key
      
      - Implemented online alter add/drop primary key
      - Improved index mapping between server and Falon
      -

A few notes...

1) Online add/drop of primary keys now occur via the internal 'upgrade' process rather than a direct call to createIndex(). Primary keys are a different animal than ordinary keys, so rather than enhance the existing online ADD INDEX, I simply used the mechanism already in place in 'upgrade'.

2) Improved mapping of Falcon and server indexes in StorageTable and StorageTableShare. Sometimes the mapping could get out-of-sync, and the index ids for a table would be different between the server and Falcon.

3) During stress testing, I found that a concurrent SELECT or other operation can sneak in between online ALTER phases 2 and 3. This is a problem for DROP INDEX because at phase 2, Falcon has deleted the index but the server has not.

This manifests as a fatal assertion in the server following a call to StorageInterface::init_index(), which fails if the index has been deleted. The problem is that the server does not provide DDL protection across the three phases of online ALTER (see StorageInterface::alter_table_phase1, 2 and 3).

As a workaround, Falcon does a quick check (StorageTable::checkCurrentIndex) prior to each index operation (StorageInterface::index_scan, index_next, etc) rather than only in index_init(), and returns a more benign error (HA_ERR_KEY_NOT_FOUND) if the server tries to access a deleted index. This allows for a more graceful failure, however, we should never have to contend with it in the first place--the server should know better than to access an index it is in the process of deleting.

I've discussed this with Kostja and Martin Skold. Both agree it is a problem, bug TBD.

4) The CREATE INDEX operation, like all DDL, gets an exclusive lock on syncSysDDL. The Scavenger always gets a shared lock on syncSysDDL. This is a problem because CREATE INDEX populates the index before releasing the lock. Without scavenging, this will blow out the record cache for large tables, so I am now working on a method to decouple syncSysDDL and the Scavenger. See Bug#35755, "Cannot create index on Falcon table...".

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

2967 Christopher Powers	2009-01-20
      Bug #42099 Falcon: Online ALTER add/drop primary key
            
      Updated falcon_online_index test case for online add/drop primary key

Pushed into 6.0.10-alpha (revid:alik@sun.com-20090211182317-uagkyj01fk30p1f8) (version source revid:lars-erik.bjork@sun.com-20090121081224-6vr43golf3j01o9m) (merge vers: 6.0.10-alpha) (pib:6)

An entry has been added to the 6.0.10 changelog: 

The ALTER ONLINE TABLE statement for Falcon tables would not include support for add a primary key.