Bug #39445 Falcon: Update fails following online add column
Submitted: 15 Sep 2008 6:22 Modified: 15 May 2009 15:57
Reporter: Christopher Powers Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S2 (Serious)
Version:6.0-falcon OS:Any
Assigned to: Christopher Powers CPU Architecture:Any
Tags: F_ONLINE ALTER
Triage: Triaged: D2 (Serious)

[15 Sep 2008 6:22] Christopher Powers
Description:
Table updates following an online add column fail with an error message.

How to repeat:
Online add column is currently disabled because of this problem, so a recompile is necessary.

Uncomment HA_ADD_COLUMN in StorageInterface::check_if_supported_alter() in ha_falcon.cpp, recompile and run the following script:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (s1 int, s2 int, key(s2)) ENGINE=FALCON;
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
ALTER ONLINE TABLE t1 ADD COLUMN (s3 int), ADD COLUMN (s4 int);
INSERT INTO t1 VALUES (4,4,4,4), (5,5,5,5), (6,6,6,6);
SELECT * FROM t1;
UPDATE t1 SET s2 = s1 + 1;

The values in column s2 will not be updated and the following error will result:

ERROR 1032 (HY000): Can't find record in 't1'

Suggested fix:
Falcon encodes and decodes records as they move through the Storage Interface. To do this, Falcon maintains a table Format object to map the physical and logical fields of records as they are encoded and decoded.

When a column is added to a table, Falcon generates a new Format object to map new records. Every record in a table references a Format object, therefore, there may be several Formats used by the records within a table, depending upon how the table has changed.

This problem is most likely due to an incorrect use of record Format objects during the update operation. In this case, older records must be decoded using the older Format (pre-add column), updated, then re-encoded using the new Format (post-add column).

Following are the details of how Formats are used within Falcon, provided by Jim Starkey. (This will eventually make its way into the design docs.)

======================================================

Between the server and Falcon, there are really three different ideas of field ids:

   * The server has a column number, subject to change as columns are
     added and dropped.
   * Each Falcon field has an immutable logical field id. New fields
     get new ids; dropped fields leave holes.
   * Each Falcon record format has a dense set of physical record ids.
     A physical record id is really the relative field number in the
     physical record. 

The Falcon Format object has a bi-directional mappings between logical and physical field ids. Most Falcon fields are identified by logical ids. The physical ids are used only within the class Record to fetch or set values within a Falcon record.

Things get a little more exciting in the Falcon storage handler interface.  There are two primary methods that do the work. StorageInterface::encodeRecord and StorageInterface::decodeRecord exist to map between canonical MySQL server records and a physical Falcon records. Both of these guys handle Falcon record fields in physical order, and both are driven by a Falcon format object corresponding to the current record (::decodeData) or the most recent format (::encodeData). To do this efficiently, the StorageInterface class maintains a vector to map from a Falcon logical field id to a MySQL "Field" object (not to be confused with the Falcon "Field" object -- sorry).

The mapping is supposed to work like this:

   * StorageInterface::encodeRecord and ::decodeRecord loop through all
     possible physical field ids based on the Format::maxId.
   * Each physical field id is mapped via Format::fieldId to a logical
     field id
   * The logical field id, in turn, is as an index to
     StorageInterface::fieldMap to get a server Field object the
     represents the field in the MySQL canonical record.

The mechanism is understandable delicate.  For it to work properly:

   * The correct Falcon Format object must be set in StorageRecord::format.
   * Non-existent physical fields have to be skipped over
   * Physical fields for which there is no corresponding server field
     must be skipped
   * StorageInterface::fieldMap has to be computed correctly.

The most difficult case is StorageInterface::update row where there is a format change between the original and new record.  For this to work correctly, StorageTable::format must be set to the appropriate format while the record is being decoded and then set to the most record format (Table::currentFormat) before encoding the new record.  There are lots of opportunities for incorrect assumptions about storage handler call order.  There are even more about keeping StorageInterface::fieldMap up to date across handler objects that straddle an on-line add or drop column operation.

I put in the entire mechanism during a dull day at the UC. In the process, I discovered that the "add column after <field>" didn't work at all (I posted a bug), so a major code path really couldn't be tested at all.

The code looks OK assuming all the mirrors are in place and correct.  If it isn't working, I'd check to see the various field id maps are plausible, and if not, fix them. If a fix takes over three or four (very carefully chosen) lines, it's probably not quite right.
[15 Sep 2008 18:11] Godofredo Miguel Solorzano
Thank you for the bug report. Verified as described.

Server version: 6.0.8-alpha-debug Source distribution

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

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 (s1 int, s2 int, key(s2)) ENGINE=FALCON;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER ONLINE TABLE t1 ADD COLUMN (s3 int), ADD COLUMN (s4 int);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 VALUES (4,4,4,4), (5,5,5,5), (6,6,6,6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+------+------+
| s1   | s2   | s3   | s4   |
+------+------+------+------+
|    1 |    1 | NULL | NULL | 
|    2 |    2 | NULL | NULL | 
|    3 |    3 | NULL | NULL | 
|    4 |    4 |    4 |    4 | 
|    5 |    5 |    5 |    5 | 
|    6 |    6 |    6 |    6 | 
+------+------+------+------+
6 rows in set (0.00 sec)

mysql> UPDATE t1 SET s2 = s1 + 1;
ERROR 1032 (HY000): Can't find record in 't1'
mysql>
[21 Feb 2009 23:34] 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/67101

3030 Christopher Powers	2009-02-21
      Bug #39445 "Falcon: Update fails following online add column"
      
      Enabled online ALTER ADD COLUMN.
      
      The problem is that online ALTER ADD COLUMN creates a new default
      record format for the table that has more fields than the older
      record formats associated with existing rows.
      
      This causes inconsistent results during row updates because the
      the older, shorter rows lacked the encoding for new columns.
      
      StorageInterface::decodeRecord() now sets the NULL flag for fields
      that are not physically represented. The assumption of NULL can be
      safely made because online ALTER ADD COLUMN only supports nullable
      columns and a default value of NULL. 
      
      Columns added outside of this restriction, e.g. with a non-null
      default value, will be created via the slow ALTER in which all
      rows are copied into a new table and thus assigned the newer
      record format.
[22 Feb 2009 7:14] Kevin Lewis
Code looks good.  OK to push.
[23 Feb 2009 11:03] John Embretsen
A regression test for this bug is part of the falcon_online_add_column test which is attached to the e-mail at http://lists.mysql.com/falcon/13 (2008-10-03)

The entire test needs to be adapted to the current feature restrictions, e.g. that ONLINE ADD COLUMN is supporting only nullable columns with DEFAULT NULL values (?). 

In other words, move test segments that include adding/dropping NOT NULL columns into the "negative testing" section. Should also add a test segment for adding DEFAULT NULL columns.
[2 Mar 2009 14:12] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090302140208-lfdejjbcyezlhhjt) (version source revid:vvaintroub@mysql.com-20090223172157-jllfd81jwvak4n5j) (merge vers: 6.0.11-alpha) (pib:6)
[15 May 2009 15:57] MC Brown
An entry has been added to the 6.0.11 changelog: 

Updating Falcon tables after an online ALTER ADD COLUMN operation could fail