The MySQL 5.6.4 release introduced support for fractional values within the temporal datatypes: TIME, DATETIME, and TIMESTAMP. Hence the storage requirement and encoding differs for them in comparison to older (5.5 and earlier) temporal datatypes. The storage format for the temporal datatypes in the old format are not space efficient either, and recreating tables having both the new and old formats can be a long and tedious process. For these reasons, we wanted to make it easier for users to identify precisely which tables, if any, need to be upgraded.
In my previous blog post, where we looked at the process of upgrading old MySQL-5.5 format temporals to the MySQL-5.6 format, there was the question about how one would go about identifying whether a table actually contained temporal columns in the old format or not (thus needing to be upgraded). Based on the feedback we received from one of our customers, and also for the benefit of all our MySQL users who plan to upgrade tables having such columns to the new format, we have introduced a new server option in 5.6.24 called show_old_temporals. When this variable is enabled, the SHOW CREATE TABLE behavior for that session is changed so that we use comments to clearly mark the temporal columns that are using the old binary format. For example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> SET SESSION show_old_temporals=ON; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------+ | Warning | 1287 | '@@show_old_temporals' is deprecated and will be removed in a future release. | +---------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE ts; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ts | CREATE TABLE `ts` ( `f_time` time /* 5.5 binary format */ DEFAULT NULL, `f_timestamp` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `f_datetime` datetime /* 5.5 binary format */ DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
Also a similar comment is added to the ‘COLUMN_TYPE’ field in the Information_Schema.COLUMNS table:
|
1
2
3
4
5
6
7
8
9
|
mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='ts'; +---------------+--------------+------------+-------------+------------------+-------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-----------------------------------+------------+-----------------------------+---------------------------------+----------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | +---------------+--------------+------------+-------------+------------------+-------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-----------------------------------+------------+-----------------------------+---------------------------------+----------------+ | def | test | ts | f_time | 1 | NULL | YES | time | NULL | NULL | NULL | NULL | 0 | NULL | NULL | time /* 5.5 binary format */ | | | select,insert,update,references | | | def | test | ts | f_timestamp | 2 | CURRENT_TIMESTAMP | NO | timestamp | NULL | NULL | NULL | NULL | 0 | NULL | NULL | timestamp /* 5.5 binary format */ | | on update CURRENT_TIMESTAMP | select,insert,update,references | | | def | test | ts | f_datetime | 3 | NULL | YES | datetime | NULL | NULL | NULL | NULL | 0 | NULL | NULL | datetime /* 5.5 binary format */ | | | select,insert,update,references | | +---------------+--------------+------------+-------------+------------------+-------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-----------------------------------+------------+-----------------------------+---------------------------------+----------------+ 3 rows in set (0.00 sec) |
When show_old_temporals is OFF (the default), then both SHOW CREATE TABLE and Information_Schema.COLUMNS will provide the standard behavior and output.
As listed in my previous blog post, there are disadvantages of having tables with temporal columns in the old format, and hence we will remove support for them entirely in a future release. For this reason, the show_old_temporals option is already deprecated and will also be removed in a future release. Its value is only temporary, and it will be removed at the same time that we remove support for the old temporal formats.
We really hope that this new feature makes users’ lives easier when upgrading to MySQL 5.6 and later! We also look forward to your feedback! You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.
As always, THANK YOU for using MySQL!