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

Change SHOW CREATE TABLE to display default collations

    XMLWordPrintable

    Details

      Description

      Currently MariaDB reduces SHOW CREATE TABLE output: COLLATE clause is not displayed if the collation is default for its character set.

      CREATE OR REPLACE TABLE t1 (
        a VARCHAR(10),
        b VARCHAR(10) CHARACTER SET ucs2
      ) CHARACTER SET utf8mb4;
      SHOW CREATE TABLE t1;
      

      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                  |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` varchar(10) DEFAULT NULL,
        `b` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
      

      Notice:

      • the definition of the column `b` does not have a COLLATE clause, because it's collation is ucs2_general_ci, which is default for the character set ucs2.
      • the table option does not have a COLLATE clause, because it's collation is utf8mb4_general_ci, which is default for the chracter set utf8mb4.

      Under terms of MDEV-19123, we're going to change the default collation for Unicode charcter sets to uca1400_ai_ci.
      This will break dump-restore:

      mariadb-dump | mariadb
      

      e.g. utf8mb4_general_ci will be erroneously replaced to uca1400_ai_ci.

      To avoid this, SHOW CREATE TABLE should always diplay collations after CHARACTER SET clause, even default ones.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.