Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19906

Show internal type for TIMESTAMP, DATETIME, and TIME columns

    XMLWordPrintable

Details

    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

          Activity

            People

              bar Alexander Barkov
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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