[MDEV-19906] Show internal type for TIMESTAMP, DATETIME, and TIME columns Created: 2019-06-28  Updated: 2023-09-01  Resolved: 2020-01-17

Status: Closed
Project: MariaDB Server
Component/s: Data types, Variables
Fix Version/s: 10.5.1

Type: Task Priority: Critical
Reporter: Geoff Montee (Inactive) Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-21497 Make Field_time, Field_datetime, Fiel... Closed
Relates
relates to MDEV-32063 view of timestamp returning datetime ... Open
relates to MDEV-5377 Row-based replication of MariaDB temp... Closed
relates to MDEV-5528 Command line variable to choose Maria... Closed
relates to MDEV-6001 Merge tests for MySQL WL#946 TIME/DAT... Stalled
relates to MDEV-9967 Convert old temporal types on ALTER T... Closed
relates to MDEV-11351 Not able to rebuild tables with old t... Confirmed
relates to MDEV-15225 Can't import .ibd file with temporal ... Closed
relates to MDEV-15228 Document how to upgrade old temporal ... Closed
relates to MDEV-16542 Fix ALTER TABLE FORCE to upgrade temp... Closed

 Description   

MySQL 5.6 and later supports the show_old_temporals system variable that configures the server to show internal type for TIMESTAMP, DATETIME, and TIME columns:

show_old_temporals

Property Value
Command-Line Format --show-old-temporals[=

Unknown macro: {OFF|ON}

]
Introduced 5.6.24
Deprecated 5.6.24
System Variable show_old_temporals
Scope Global, Session
Dynamic Yes
Type Boolean
Default Value OFF

Whether SHOW CREATE TABLE output includes comments to flag temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). This variable is disabled by default. If enabled, SHOW CREATE TABLE output looks like this:

CREATE TABLE `mytbl` (
`ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP,
`dt` datetime /* 5.5 binary format */ DEFAULT NULL,
`t` time /* 5.5 binary format */ DEFAULT NULL
) DEFAULT CHARSET=latin1
Output for the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS table is affected similarly.

This variable was added in MySQL 5.6.24. It is deprecated and will be removed in a future MySQL release.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_show_old_temporals

Mixing old temporal types and new temporal types can cause a lot of issues with replication and with importing InnoDB tablespaces. It would be useful to have some easy way tell if a temporal column uses the MariaDB 5.3 format or the MySQL 5.6 format or the pre-MySQL 5.6 format. We should probably port this feature from MySQL, or re-implement a similar feature.

 Comments   
Comment by Geoff Montee (Inactive) [ 2019-11-08 ]

For InnoDB tables, there is currently a hacky way that you can tell the difference between the MariaDB 5.3 `DATETIME` and `TIMESTAMP` columns and MySQL 5.6 `DATETIME` and `TIMESTAMP` columns.

For example, let's say that we create two tables:

SET GLOBAL mysql56_temporal_format=OFF;
 
CREATE TABLE tab_mariadb53_temporals (
 
   id int primary key, 
   dt DATETIME,
 
   ts TIMESTAMP
 
);
 
 
 
SET GLOBAL mysql56_temporal_format=ON;
 
CREATE TABLE tab_mysql56_temporals (
 
   id int primary key, 
   dt DATETIME,
 
   ts TIMESTAMP
 
);

We can query information_schema.COLUMNS:

SELECT *
 
FROM information_schema.COLUMNS
 
WHERE TABLE_NAME LIKE 'tab%temporals'
 
AND COLUMN_NAME IN ('dt', 'ts')\G

We can also query information_schema.INNODB_SYS_COLUMNS:

SELECT ist.NAME AS table_name, isc.NAME AS column_name, isc.MTYPE
 
FROM information_schema.INNODB_SYS_COLUMNS isc
 
JOIN information_schema.INNODB_SYS_TABLES ist
 
ON isc.TABLE_ID = ist.TABLE_ID
 
WHERE ist.NAME LIKE '%/tab%temporals'
 
AND isc.NAME IN ('dt', 'ts');

That does show some useful information:

MariaDB [db1]> SELECT ist.NAME AS table_name, isc.NAME AS column_name, isc.MTYPE
 
    -> FROM information_schema.INNODB_SYS_COLUMNS isc
 
    -> JOIN information_schema.INNODB_SYS_TABLES ist
 
    -> ON isc.TABLE_ID = ist.TABLE_ID
 
    -> WHERE ist.NAME LIKE '%/tab%temporals'
 
    -> AND isc.NAME IN ('dt', 'ts');
 
+-----------------------------+-------------+-------+
 
| table_name                  | column_name | MTYPE |
 
+-----------------------------+-------------+-------+
 
| db1/tab_mariadb53_temporals | dt          |     6 |
 
| db1/tab_mariadb53_temporals | ts          |     6 |
 
| db1/tab_mysql56_temporals   | dt          |     3 |
 
| db1/tab_mysql56_temporals   | ts          |     3 |
 
+-----------------------------+-------------+-------+
 
4 rows in set (0.001 sec)

So we can see that for MariaDB 5.3 temporals, the `MTYPE` is 6, and for MySQL 5.6 temporals, the `MTYPE` is 3.

Comment by Geoff Montee (Inactive) [ 2019-11-08 ]

This is a query that can be used to check InnoDB tables:

WITH innodb_temporals AS (
 
   SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME
 
   FROM information_schema.COLUMNS c
 
   JOIN information_schema.TABLES t
 
      ON (c.TABLE_SCHEMA = t.TABLE_SCHEMA)
 
      AND (c.TABLE_NAME = t.TABLE_NAME) 
   WHERE t.TABLE_SCHEMA NOT IN('mysql', 'information_schema', 'performance_schema')
 
      AND t.ENGINE = 'InnoDB'
 
      AND c.COLUMN_TYPE IN ('datetime' ,'timestamp')
 
)
 
SELECT it.TABLE_SCHEMA, it.TABLE_NAME, it.COLUMN_NAME, isc.MTYPE,
 
   CASE isc.MTYPE
 
      WHEN 6 THEN 'mysql56_temporal_format=OFF'
 
      WHEN 3 THEN 'mysql56_temporal_format=ON'
 
      ELSE 'undefined'
 
   END AS mysql56_temporal_format_value
 
FROM innodb_temporals it
 
JOIN information_schema.INNODB_SYS_TABLES ist
 
   ON ist.NAME = CONCAT(it.TABLE_SCHEMA, '/', it.TABLE_NAME)
 
JOIN information_schema.INNODB_SYS_COLUMNS isc
 
   ON ist.TABLE_ID = isc.TABLE_ID
 
   AND it.COLUMN_NAME = isc.NAME;

For example:

MariaDB [db1]> WITH innodb_temporals AS (
 
    ->    SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME
 
    ->    FROM information_schema.COLUMNS c
 
    ->    JOIN information_schema.TABLES t
 
    ->       ON (c.TABLE_SCHEMA = t.TABLE_SCHEMA)
 
    ->       AND (c.TABLE_NAME = t.TABLE_NAME)
 
    ->    WHERE t.TABLE_SCHEMA NOT IN('mysql', 'information_schema', 'performance_schema')
 
    ->       AND t.ENGINE = 'InnoDB'
 
    ->       AND c.COLUMN_TYPE IN ('datetime' ,'timestamp')
 
    -> )
 
    -> SELECT it.TABLE_SCHEMA, it.TABLE_NAME, it.COLUMN_NAME, isc.MTYPE,
 
    ->    CASE isc.MTYPE
 
    ->       WHEN 6 THEN 'mysql56_temporal_format=OFF'
 
    ->       WHEN 3 THEN 'mysql56_temporal_format=ON'
 
    ->       ELSE 'undefined'
 
    ->    END AS mysql56_temporal_format_value
 
    -> FROM innodb_temporals it
 
    -> JOIN information_schema.INNODB_SYS_TABLES ist
 
    ->    ON ist.NAME = CONCAT(it.TABLE_SCHEMA, '/', it.TABLE_NAME)
 
    -> JOIN information_schema.INNODB_SYS_COLUMNS isc
 
    ->    ON ist.TABLE_ID = isc.TABLE_ID
 
    ->    AND it.COLUMN_NAME = isc.NAME
 
    -> WHERE it.TABLE_NAME LIKE 'tab%temporals';
 
+--------------+-------------------------+-------------+-------+-------------------------------+
 
| TABLE_SCHEMA | TABLE_NAME              | COLUMN_NAME | MTYPE | mysql56_temporal_format_value |
 
+--------------+-------------------------+-------------+-------+-------------------------------+
 
| db1          | tab_mariadb53_temporals | dt          |     6 | mysql56_temporal_format=OFF   |
 
| db1          | tab_mariadb53_temporals | ts          |     6 | mysql56_temporal_format=OFF   |
 
| db1          | tab_mysql56_temporals   | dt          |     3 | mysql56_temporal_format=ON    |
 
| db1          | tab_mysql56_temporals   | ts          |     3 | mysql56_temporal_format=ON    |
 
+--------------+-------------------------+-------------+-------+-------------------------------+
 
4 rows in set (0.002 sec)

Comment by Alexander Barkov [ 2019-11-10 ]

GeoffMontee, good catch. Thanks for your findings.

Comment by Alexander Barkov [ 2020-01-17 ]

It's been implemented this way:

Unlike MySQL, we don't implement the show_old_temporals variable.

Instead, old temporal data types (created with a pre-10.0 version of MariaDB)
are now always displayed with a /* mariadb-5.3 */ comment in:

  • SHOW CREATE TABLE
  • DESCRIBE
  • INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE

For example:

CREATE TABLE `t1` (
 
  `t0` datetime /* mariadb-5.3 */ DEFAULT NULL,
 
   `t6` datetime(6) /* mariadb-5.3 */ DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Note, new temporal data types are displayed without a format comment.

These manual articles have been updated accordingly:

Comment by Geoff Montee (Inactive) [ 2020-01-17 ]

Hi bar,

Thanks for implementing this!

The comments are only for informational purposes, right? I assume that if a user executes the CREATE TABLE statement that is output by SHOW CREATE TABLE, then MariaDB does not parse the comment to determine whether it needs to use the old datetime format. The user would still need to manually set mysql56_temporal_format if they want to recreate the table with the old datetime format. i.e.:

SET GLOBAL mysql56_temporal_format=OFF;
 
 
 
CREATE TABLE `t1` (
 
  `t0` datetime /* mariadb-5.3 */ DEFAULT NULL,
 
   `t6` datetime(6) /* mariadb-5.3 */ DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Is that a correct understanding?

Comment by Alexander Barkov [ 2020-01-19 ]

Hi Geoff,

This is correct. The comment is only for informational purpose, it does not get parsed.

Generated at Thu Feb 08 08:55:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.