-
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.
Nisha Gopalakrishnan authoredFULL 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