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 added a comment - - edited

            serg, can you please review this patch:
            https://github.com/MariaDB/server/commit/68ebe0e2f5eec98c2fcf44ac6ad18524fc6e7a91

            ?
            Thanks.

            bar Alexander Barkov added a comment - - edited serg , can you please review this patch: https://github.com/MariaDB/server/commit/68ebe0e2f5eec98c2fcf44ac6ad18524fc6e7a91 ? Thanks.
            serg Sergei Golubchik added a comment -

            68ebe0e2f5e is ok to push

            serg Sergei Golubchik added a comment - 68ebe0e2f5e is ok to push
            bar Alexander Barkov added a comment - - edited

            Pushed to 10.4

            In 10.9 we'll additionally need to fix json_table code inside a big if() condition:

                      ...
            		 
                      (!(m_explicit_cs->state & MY_CS_PRIMARY) &&
            		 
                       (str->append(STRING_WITH_LEN(" COLLATE ")) ||
            		 
                        str->append(&m_explicit_cs->coll_name))))) ||
            		 
                      ...

            bar Alexander Barkov added a comment - - edited Pushed to 10.4 In 10.9 we'll additionally need to fix json_table code inside a big if() condition: ... (!(m_explicit_cs->state & MY_CS_PRIMARY) && (str->append(STRING_WITH_LEN( " COLLATE " )) || str->append(&m_explicit_cs->coll_name))))) || ...
            bar Alexander Barkov added a comment -

            Reopening the issue as 10.9 still needs an additional fix into the JSON_TABLE related code mentioned above.

            bar Alexander Barkov added a comment - Reopening the issue as 10.9 still needs an additional fix into the JSON_TABLE related code mentioned above.

            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.