[MDEV-15228] Document how to upgrade old temporal columns Created: 2018-02-06 Updated: 2020-08-25 Resolved: 2018-09-28 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Data Definition - Alter Table, Documentation - Support, Temporal Types |
| Fix Version/s: | N/A |
| Type: | Task | Priority: | Major |
| Reporter: | Geoff Montee (Inactive) | Assignee: | Kenneth Dyer (Inactive) |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | documentation | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||
| 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:
This is shown in the MySQL 5.6 release notes:
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:
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/ |
| Comments |
| Comment by Ian Gilfillan [ 2018-02-12 ] |
|
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. |
| Comment by Alexander Barkov [ 2018-06-19 ] |
|
Since 5.6.16 MySQL forces conversion of old temporals to new temporals when doing ALTER under certain conditions, including FORCE. The commit comment:
The release notes comment:
In MariaDB we do not force such conversion. Such conversion is normally not needed.
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:
Adding automatic conversion looks dangerous for 10.2 or 10.3. It can be done in 10.4, if needed. |
| Comment by Alexander Barkov [ 2018-06-21 ] |
|
I created a new task 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 Thanks! |
| Comment by Marko Mäkelä [ 2018-10-05 ] |
|
This would become simpler, once MDEV-11658 has been implemented. Something like:
Unfortunately, this would require changes to the .ibd file format, making the files incompatible with MySQL or older MariaDB versions. |