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

Change SHOW CREATE TABLE to display default collations

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

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19123 Change default charset from latin1 to utf8mb4

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29561 SHOW CREATE TABLE produces syntactically incorrect structure

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30101 Some tests still need result updates after MDEV-29446

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34700 Connect SQLite3 MTR test fails due to various charset/collation related output changes

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33788 HEX(COLUMN_CREATE(.. AS CHAR ...)) fails with --view-protocol

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            elenst Elena Stepanova made changes -
            marko Marko Mäkelä made changes -
            issue.field.resolutiondate 2022-09-21 08:42:37.0 2022-09-21 08:42:37.811
            marko Marko Mäkelä made changes -
            Fix Version/s 10.3.37 [ 28404 ]
            Fix Version/s 10.4.27 [ 28405 ]
            Fix Version/s 10.5.18 [ 28421 ]
            Fix Version/s 10.6.11 [ 28441 ]
            Fix Version/s 10.7.7 [ 28442 ]
            Fix Version/s 10.8.6 [ 28443 ]
            Fix Version/s 10.9.4 [ 28444 ]
            Fix Version/s 10.10.2 [ 28410 ]
            Fix Version/s 10.11.0 [ 28411 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -

            People

              bar Alexander Barkov
              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.