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
Triage: Triaged: D2 (Serious)

[14 Jan 2009 3:46] Christopher Powers
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.
[14 Jan 2009 11:16] Godofredo Miguel Solorzano
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>
[14 Jan 2009 22:25] 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/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
      -
[14 Jan 2009 23:37] Christopher Powers
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...".
[21 Jan 2009 0:30] 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/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
[13 Feb 2009 7:24] Bugs System
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)
[15 May 2009 12:59] MC Brown
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.