• Jon Olav Hauglid's avatar
    Bug #42230 during add index, cannot do queries on storage engines · 5e035790
    Jon Olav Hauglid authored
               that implement add_index
    
    The problem was that ALTER TABLE blocked reads on an InnoDB table
    while adding a secondary index, even if this was not needed. It is
    only needed for the final step where the .frm file is updated.
    
    The reason queries were blocked, was that ALTER TABLE upgraded the
    metadata lock from MDL_SHARED_NO_WRITE (which blocks writes) to
    MDL_EXCLUSIVE (which blocks all accesses) before index creation.
    
    The way the server handles index creation, is that storage engines
    publish their capabilities to the server and the server determines
    which of the following three ways this can be handled: 1) build a
    new version of the table; 2) change the existing table but with
    exclusive metadata lock; 3) change the existing table but without
    metadata lock upgrade.
    
    For InnoDB and secondary index creation, option 3) should have been
    selected. However this failed for two reasons. First, InnoDB did
    not publish this capability properly.
    
    Second, the ALTER TABLE code failed to made proper use of the
    information supplied by the storage engine. A variable
    need_lock_for_indexes was set accordingly, but was not later used.
    This patch fixes this problem by only doing metadata lock upgrade
    before index creation/deletion if this variable has been set.
    
    This patch also changes some of the related terminology used 
    in the code. Specifically the use of "fast" and "online" with
    respect to ALTER TABLE. "Fast" was used to indicate that an
    ALTER TABLE operation could be done without involving a
    temporary table. "Fast" has been renamed "in-place" to more
    accurately describe the behavior.
    
    "Online" meant that the operation could be done without taking
    a table lock. However, in the current implementation writes
    are always prohibited during ALTER TABLE and an exclusive
    metadata lock is held while updating the .frm, so ALTER TABLE
    is not completely online. This patch replaces "online" with 
    "in-place", with additional comments indicating if concurrent
    reads are allowed during index creation/deletion or not.
    
    An important part of this update of terminology is renaming
    of the handler flags used by handlers to indicate if index
    creation/deletion can be done in-place and if concurrent reads
    are allowed. For example, the HA_ONLINE_ADD_INDEX_NO_WRITES
    flag has been renamed to HA_INPLACE_ADD_INDEX_NO_READ_WRITE,
    while HA_ONLINE_ADD_INDEX is now HA_INPLACE_ADD_INDEX_NO_WRITE.
    Note that this is a rename to clarify current behavior, the
    flag values have not changed and no flags have been removed or
    added.
    
    Test case added to innodb_mysql_sync.test.
    5e035790
handler.h 78.7 KB