[MDEV-19906] Show internal type for TIMESTAMP, DATETIME, and TIME columns - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 10.5.1
Component/s: Data types, Variables
Labels:
None

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.

Attachments

Issue Links

is blocked by

MDEV-21497 Make Field_time, Field_datetime, Field_timestamp abstract

Closed

relates to

MDEV-32063 view of timestamp returning datetime /* 5.3 */ format

Open

MDEV-5377 Row-based replication of MariaDB temporal data types with FSP>0 into a different column type

Closed

MDEV-5528 Command line variable to choose MariaDB-5.3 vs MySQL-5.6 temporal data formats

Closed

MDEV-6001 Merge tests for MySQL WL#946 TIME/DATETIME/TIMESTAMP with fractional precision

Stalled

MDEV-9967 Convert old temporal types on ALTER TABLE ... FORCE

Closed

MDEV-11351 Not able to rebuild tables with old timestamp format

Confirmed

MDEV-15225 Can't import .ibd file with temporal type format differing from mysql56_temporal_format

Closed

MDEV-15228 Document how to upgrade old temporal columns

Closed

MDEV-16542 Fix ALTER TABLE FORCE to upgrade temporal types

Closed

(5 relates to)

Activity

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) added a comment - 2019-11-08 20:13

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.

Geoff Montee (Inactive) added a comment - 2019-11-08 20:13 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.

Geoff Montee (Inactive) added a comment - 2019-11-08 20:44

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)

Geoff Montee (Inactive) added a comment - 2019-11-08 20:44 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)

Alexander Barkov added a comment - 2019-11-10 07:59

GeoffMontee, good catch. Thanks for your findings.

Alexander Barkov added a comment - 2019-11-10 07:59 GeoffMontee , good catch. Thanks for your findings.

Alexander Barkov added a comment - 2020-01-17 07:19 - edited

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:

Alexander Barkov added a comment - 2020-01-17 07:19 - edited 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: https://mariadb.com/kb/en/timestamp/ https://mariadb.com/kb/en/datetime/ https://mariadb.com/kb/en/time/

Geoff Montee (Inactive) added a comment - 2020-01-17 19:34

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?

Geoff Montee (Inactive) added a comment - 2020-01-17 19:34 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?

Alexander Barkov added a comment - 2020-01-19 07:16

Hi Geoff,

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

Alexander Barkov added a comment - 2020-01-19 07:16 Hi Geoff, This is correct. The comment is only for informational purpose, it does not get parsed.

People

Assignee:: Alexander Barkov

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2019-06-28 20:10

Updated:: 2024-09-16 22:20

Resolved:: 2020-01-17 07:48

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server