Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15228

Document how to upgrade old temporal columns

    XMLWordPrintable

    Details

      Description

      The default format for temporal types changed to the same format as MySQL 5.6 with the introduction of mysql56_temporal_format.

      https://mariadb.com/kb/en/library/server-system-variables/#mysql56_temporal_format

      In MySQL 5.6 and above, old temporal columns can be "upgraded" to the new format with:

      ALTER TABLE tab FORCE;
      

      This is shown in the MySQL 5.6 release notes:

      Incompatible change: For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from storage required for tables created in 5.6.4 and later. This is due to a change in 5.6.4 that permits these temporal types to have a fractional part. This change can affect the output of statements that depend on the row format, such as CHECKSUM TABLE. After upgrading from MySQL 5.5 to MySQL 5.6.4 or later, it is recommended that you also upgrade from MySQL 5.5 to MySQL 5.6 TIME, DATETIME, and TIMESTAMP types. ALTER TABLE currently allows the creation of tables containing temporal columns in both MySQL 5.5 and MySQL 5.6.4 (or later) binary format but this makes it more difficult to recreate tables in cases where .frm files are not available. Additionally, as of MySQL 5.6.4, the aforementioned temporal types are more space efficient. For more information about changes to temporal types in MySQL 5.6.4, see Date and Time Type Storage Requirements.

      As of MySQL 5.6.16, ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. Hence, the following statement upgrades a table containing columns in the old format:

      ALTER TABLE tbl_name FORCE;

      This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary.

      When ALTER TABLE does produce a temporal-format conversion, it generates a message that can be displayed with SHOW WARNINGS: TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

      When upgrading to MySQL 5.6.4 or later, be aware that CHECK TABLE ... FOR UPGRADE does not report temporal columns that use the pre-MySQL 5.6.4 format (Bug #73008, Bug #18985579). In MySQL 5.6.24, two new system variables, avoid_temporal_upgrade and show_old_temporals, were added to provide control over temporal column upgrades (Bug #72997, Bug #18985760).

      https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

      This doesn't seem to work exactly the same way in MariaDB. ALTER TABLE ... FORCE does not seem to change the format at all. However, modifying the column to the "same" data type does seem to upgrade it to the new format. e.g. if the column "dt" is a datetime column in the old format, then the following statement will "upgrade" it:

      ALTER TABLE tab MODIFY dt datetime;
      

      Also unlike MySQL, there is no warning to tell the user that the column was "upgraded".

      Maybe we should document the specifics of this somewhere? And especially how it differs from MySQL? Maybe on a page in this section?:

      https://mariadb.com/kb/en/library/date-and-time-data-types/

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              KennethDyer Kenneth Dyer
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: