Skip to content
  • Dmitry Lenev's avatar
    77302129
    Short-term fix bug #11754911 "ALTER TABLE CAUSES INCONSISTENT · 77302129
    Dmitry Lenev authored
    VALUES FOR FOREIGN KEYS".
    
    ALTER TABLE that changes a column definition may lead to changes
    in values stored in this column. If such a column participates in
    a foreign key (which is not added by this ALTER TABLE) changes in
    the column might break referential integrity.
    
    Same could have happened when ALTER IGNORE TABLE removed some
    rows from the table that served as a parent in a foreign key.
    
    This has happened because there was no check that for a new column
    value there is a matching parent key value (or if parent table
    was changed that such a change doesn't leave child key values
    as orphans).
    
    This patch tries to address the problem for the case when ALTER
    TABLE is executed using COPY-algorithm. The corresponding problem
    for the INPLACE algorithm should be handled inside of storage
    engine calls implementing it.
    
    Incompatible changes:
    1) We no longer allow dropping or renaming of columns in a foreign
       key when ALTER is executed using COPY algorithm (Rename is
       disallowed since we miss an API to communicate information about
       new names of columns to the InnoDB engine).
    2) We do not allow changes to parent and child columns which
       theoretically might result in value change. "Insignificant"
       changes that can't result in value change are still allowed.
    3) An error is emitted when ALTER IGNORE TABLE tries to delete a
       row from a table which is a parent in a foreign key.
    
    Checks 2-3) are not carried out when @@foreign_key_checks are off (=0).
    
    Long-term we can allow "significant" changes to child columns.
    To implement this we need a storage engine method that will temporarily
    copy info about affected foreign keys from the old version of table
    to the new version, so foreign key checks can be performed during copy.
    77302129
    Short-term fix bug #11754911 "ALTER TABLE CAUSES INCONSISTENT
    Dmitry Lenev authored
    VALUES FOR FOREIGN KEYS".
    
    ALTER TABLE that changes a column definition may lead to changes
    in values stored in this column. If such a column participates in
    a foreign key (which is not added by this ALTER TABLE) changes in
    the column might break referential integrity.
    
    Same could have happened when ALTER IGNORE TABLE removed some
    rows from the table that served as a parent in a foreign key.
    
    This has happened because there was no check that for a new column
    value there is a matching parent key value (or if parent table
    was changed that such a change doesn't leave child key values
    as orphans).
    
    This patch tries to address the problem for the case when ALTER
    TABLE is executed using COPY-algorithm. The corresponding problem
    for the INPLACE algorithm should be handled inside of storage
    engine calls implementing it.
    
    Incompatible changes:
    1) We no longer allow dropping or renaming of columns in a foreign
       key when ALTER is executed using COPY algorithm (Rename is
       disallowed since we miss an API to communicate information about
       new names of columns to the InnoDB engine).
    2) We do not allow changes to parent and child columns which
       theoretically might result in value change. "Insignificant"
       changes that can't result in value change are still allowed.
    3) An error is emitted when ALTER IGNORE TABLE tries to delete a
       row from a table which is a parent in a foreign key.
    
    Checks 2-3) are not carried out when @@foreign_key_checks are off (=0).
    
    Long-term we can allow "significant" changes to child columns.
    To implement this we need a storage engine method that will temporarily
    copy info about affected foreign keys from the old version of table
    to the new version, so foreign key checks can be performed during copy.
Loading