Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
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?:
Attachments
Issue Links
- relates to
-
MDEV-11658 Simpler, faster IMPORT of InnoDB tables
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-9967 Convert old temporal types on ALTER TABLE ... FORCE
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
Since 5.6.16 MySQL forces conversion of old temporals to new temporals when doing ALTER under certain conditions, including FORCE.
The relevant upstream commit is:
cc1fd36e44c793b85b2cae0117492a34f09dbf1a
BUG#17246318: ALTER TABLE SHOULD NOT ALLOW CREATION OF TABLES WITH BOTH 5.5 AND 5.6 TEMPORALS
The commit comment:
The columns of old temporal types of mysql-5.5 are upgraded to
mysql-5.6 format when ALTER TABLE requests ADD/CHANGE/MODIFY
COLUMN, ADD INDEX or FORCE operation.
The release notes comment:
Previously, ALTER TABLE in MySQL 5.6 could alter a table such
that the result had temporal columns in both 5.5 and 5.6
format. Now ALTER TABLE upgrades old temporal columns to 5.6
format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD
INDEX, and FORCE operations. This conversion cannot be done
using the INPLACE algorithm, so specifying ALGORITHM=INPLACE
in these cases results in an error. (Bug #17246318)
In MariaDB we do not force such conversion. Such conversion is normally not needed.
To force conversion one can use:
- ALTER TABLE MODIFY colname with the "same" data type.
- dump followed by restore
In all cases, make sure that the system variable @@mysql56_temporal_format is enabled.
However, automatic conversion (e.g. when FORCE is specified) would be useful:
- for replication purposes, as old-TIME(N) does not replicate well to new-TIME(M) when N<>M. The same problem happens for DATETIME(N) and TIMESTAMP(N) replicating to a column with the same type but a different fractional precision.
- for importing INNODB tablespaces easier (as the associated support issue demonstrates)
Adding automatic conversion looks dangerous for 10.2 or 10.3. It can be done in 10.4, if needed.
I created a new task MDEV-16542 to fix ALTER TABLE FORCE to convert columns.
Ian, please go ahead with documenting 10.2 / 10.3 behavior (about MODIFY with "same" type, and dump/restore).
We'll update the article when I'm done with MDEV-16542 in 10.4.
Thanks!
This would become simpler, once MDEV-11658 has been implemented. Something like:
- Copy exported .ibd and .frm file to the data directory.
- Issue an SQL statement and let InnoDB discover the files.
Unfortunately, this would require changes to the .ibd file format, making the files incompatible with MySQL or older MariaDB versions.
Assigning to bar - for now to clarify how this is supposed to work, with examples. You can assign back to me for final documentation afterwards.