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

Functions in default values in tables with some character sets break SHOW CREATE (and mysqldump)

Details

    Description

      CREATE TABLE t (a CHAR(8) DEFAULT REVERSE('aha')) CHARACTER SET utf32;
       
      --echo # In MTR
      SHOW CREATE TABLE t;
      --echo # In the command line client
      --exec $MYSQL test -e "SHOW CREATE TABLE t"
      --echo # In mysqldump
      --exec $MYSQL_DUMP test > $MYSQL_TMP_DIR/1.dump
      --cat_file $MYSQL_TMP_DIR/1.dump
      DROP TABLE t;
      

      In MTR, the output of SHOW CREATE looks all right, but the client and the dump demonstrate the problem:

      10.3 92be8d20

      # In the command line client
      Table	Create Table
      t	CREATE TABLE `t` (\n  `a` char(8) DEFAULT \0\0\0r\0\0\0e\0\0\0v\0\0\0e\0\0\0r\0\0\0s\0\0\0e('aha')\n) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_general_ci
       
      # In mysqldump
      ...
      CREATE TABLE `t` (
        `a` char(8) DEFAULT ;
      /*!40101 SET character_set_client = @saved_cs_client */;
      

      Reproducible on all of 10.3-10.11, and 10.2 too; seemingly with any function for a default of a string column.

      Attachments

        Issue Links

          Activity

            Reproducible in the command line client:

            MariaDB [test]> CREATE OR REPLACE TABLE t (a CHAR(8) DEFAULT REVERSE('aha')) CHARACTER SET utf32;
            Query OK, 0 rows affected (6.158 sec)
             
            MariaDB [test]> SHOW CREATE TABLE t;
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table                                                                                                                                |
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
            | t     | CREATE TABLE `t` (
              `a` char(8) DEFAULT    r   e   v   e   r   s   e('aha')
            ) ENGINE=InnoDB DEFAULT CHARSET=utf32 COLLATE=utf32_general_ci |
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
            

            bar Alexander Barkov added a comment - Reproducible in the command line client: MariaDB [test]> CREATE OR REPLACE TABLE t (a CHAR(8) DEFAULT REVERSE('aha')) CHARACTER SET utf32; Query OK, 0 rows affected (6.158 sec)   MariaDB [test]> SHOW CREATE TABLE t; +-------+---------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `a` char(8) DEFAULT r e v e r s e('aha') ) ENGINE=InnoDB DEFAULT CHARSET=utf32 COLLATE=utf32_general_ci | +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
            bar Alexander Barkov added a comment - serg , please review a patch: https://github.com/MariaDB/server/commit/551272203961c2c17b1cd87ffde4358dc762aec3

            The problem with mysqldump is not fixed. I've modified your test by

            • rename all tables to t1, t2, ..., t5
            • move all DROP TABLE to the end
            • adding

              exec $MYSQL_DUMP test;
              exec $MYSQL_DUMP test|$MYSQL test;
              

              before DROP.

            This shows that mysqldump starts with

            /*!40101 SET NAMES utf8mb4 */;
            

            but then adds

            /*!40101 SET character_set_client = utf8 */;
            

            around table definitions. Which obviously corrupts your emojis and the dump fails to load back.

            serg Sergei Golubchik added a comment - The problem with mysqldump is not fixed. I've modified your test by rename all tables to t1, t2, ..., t5 move all DROP TABLE to the end adding exec $MYSQL_DUMP test; exec $MYSQL_DUMP test|$MYSQL test; before DROP . This shows that mysqldump starts with /*!40101 SET NAMES utf8mb4 */ ; but then adds /*!40101 SET character_set_client = utf8 */ ; around table definitions. Which obviously corrupts your emojis and the dump fails to load back.

            Hi serg,

            I fixed the dump/restore problem and added a test for it. Please review:

            https://github.com/MariaDB/server/commit/d7329200de04e80e43835140a99e7df47e367916

            bar Alexander Barkov added a comment - Hi serg , I fixed the dump/restore problem and added a test for it. Please review: https://github.com/MariaDB/server/commit/d7329200de04e80e43835140a99e7df47e367916

            d7329200de04e80e43835140a99e7df47e367916 is ok to push

            serg Sergei Golubchik added a comment - d7329200de04e80e43835140a99e7df47e367916 is ok to push

            People

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