Skip to content
  • Nisha Gopalakrishnan's avatar
    cad840e6
    BUG#18985760: "FAST" ALTER TABLE CHANGE ON ENUM COLUMN TRIGGERS · cad840e6
    Nisha Gopalakrishnan authored
                   FULL TABLE REBUILD
    
    Analysis:
    --------
    Certain ALTER TABLE operations on tables with temporal columns
    in pre-5.6.4 binary format were forced to use 'slow' COPY
    algorithm, even though in theory such operations could have
    been executed using 'fast' INPLACE algorithm. Thus for large
    tables such ALTER TABLEs took a lot of time while they were
    expected to be 'fast'.
    
    Starting from version 5.6.16, ALTER TABLE statements with
    ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE clauses upgrades
    the temporal columns in the old binary format to the new format
    introduced in version 5.6.4. Such upgrade requires table
    rebuild using COPY algorithm and hence it is not a 'fast'
    operation, as it can be expected in some cases
    (for example, when adding elements to the end of enum type).
    
    Fix:
    ---
    1) A new global dynamic variable 'avoid_temporal_upgrade'
       and corresponding start-up option is introduced.
       Values accepted: Boolean values.
       Default value: 0 or FALSE.
       Note: The variable/option is deprecated and will be removed
       in the future release.
    
       When the variable is set to 1, ALTER TABLE will not try to upgrade
       the old temporal columns (unless temporal column definition
       is changed). Thus the ALTER TABLE will behave in the same way
       as it did in MySQL-5.6.15.
    
       When the variable is set to 0, ALTER TABLE will try to upgrade
       old temporal columns to new format for ALTER TABLE requests
       ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE operation i.e the
       behavior introduced from MySQL-5.6.16.
    
    2) Also a new per-session dynamic system variable
      'show_old_temporals' and a corresponding start-up option is
       introduced.
       Values accepted: Boolean values.
       Default value: 0 or FALSE.
       Note: The variable/option is deprecated and will be removed
       When this variable set to 1 for the session, SHOW CREATE
       TABLE behavior for that session is changed to mark temporal
       columns using old binary format in the following way:
    
       CREATE TABLE `ts` (
       `t` timestamp /* 5.5 binary format */ NULL DEFAULT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       Also a similar comment is added to the COLUMN_TYPE field of
       I_S.COLUMNS table.
    
       When this variable is set to 0, both SHOW CREATE TABLE and
       I_S.COLUMNS won't mark the old temporals and continue with
       the existing behavior.
    cad840e6
    BUG#18985760: "FAST" ALTER TABLE CHANGE ON ENUM COLUMN TRIGGERS
    Nisha Gopalakrishnan authored
                   FULL TABLE REBUILD
    
    Analysis:
    --------
    Certain ALTER TABLE operations on tables with temporal columns
    in pre-5.6.4 binary format were forced to use 'slow' COPY
    algorithm, even though in theory such operations could have
    been executed using 'fast' INPLACE algorithm. Thus for large
    tables such ALTER TABLEs took a lot of time while they were
    expected to be 'fast'.
    
    Starting from version 5.6.16, ALTER TABLE statements with
    ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE clauses upgrades
    the temporal columns in the old binary format to the new format
    introduced in version 5.6.4. Such upgrade requires table
    rebuild using COPY algorithm and hence it is not a 'fast'
    operation, as it can be expected in some cases
    (for example, when adding elements to the end of enum type).
    
    Fix:
    ---
    1) A new global dynamic variable 'avoid_temporal_upgrade'
       and corresponding start-up option is introduced.
       Values accepted: Boolean values.
       Default value: 0 or FALSE.
       Note: The variable/option is deprecated and will be removed
       in the future release.
    
       When the variable is set to 1, ALTER TABLE will not try to upgrade
       the old temporal columns (unless temporal column definition
       is changed). Thus the ALTER TABLE will behave in the same way
       as it did in MySQL-5.6.15.
    
       When the variable is set to 0, ALTER TABLE will try to upgrade
       old temporal columns to new format for ALTER TABLE requests
       ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE operation i.e the
       behavior introduced from MySQL-5.6.16.
    
    2) Also a new per-session dynamic system variable
      'show_old_temporals' and a corresponding start-up option is
       introduced.
       Values accepted: Boolean values.
       Default value: 0 or FALSE.
       Note: The variable/option is deprecated and will be removed
       When this variable set to 1 for the session, SHOW CREATE
       TABLE behavior for that session is changed to mark temporal
       columns using old binary format in the following way:
    
       CREATE TABLE `ts` (
       `t` timestamp /* 5.5 binary format */ NULL DEFAULT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       Also a similar comment is added to the COLUMN_TYPE field of
       I_S.COLUMNS table.
    
       When this variable is set to 0, both SHOW CREATE TABLE and
       I_S.COLUMNS won't mark the old temporals and continue with
       the existing behavior.
Loading