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

mysqldump bad format when using CHECK and COMMENT

Details

    Description

      If the table has a CHECK and a COMMENT and mysqldump is used with this table, then the output will be like:

      ...
      `fieldname2` int(10) unsigned NOT NULL DEFAULT 0 ,
      `fieldname` int(10) unsigned NOT NULL DEFAULT 0 CHECK (`fieldname` <> 0 or `fieldname2` <> 0) COMMENT 'lala',
      ...

      But when trying to import that, this error is shown:

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMMENT 'lala',

      If the order is changed manually, this is, COMMENT first and then CHECK, it works.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17654 Incorrect syntax returned for column with CHECK constraint in the "SHOW CREATE TABLE ..." result

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

          Activity

            danblack Daniel Black added a comment - 

             
            		 
            MariaDB [test]> select version();
            		 
            +-----------------+
            		 
            | version()       |
            		 
            +-----------------+
            		 
            | 10.3.39-MariaDB |
            		 
            +-----------------+
            		 
            1 row in set (0.000 sec)
            		 
             
            		 
            MariaDB [test]> create table t ( `fieldname2` int(10) unsigned NOT NULL DEFAULT 0 , `fieldname` int(10) unsigned NOT NULL DEFAULT 0 comment 'lalal' CHECK (`fieldname` <> 0 or `fieldname2` <> 0)  );
            		 
            Query OK, 0 rows affected (0.002 sec)
            		 
             
            		 
             
            		 
            MariaDB [test]> show create table t\G
            		 
            *************************** 1. row ***************************
            		 
                   Table: t
            		 
            Create Table: CREATE TABLE `t` (
            		 
              `fieldname2` int(10) unsigned NOT NULL DEFAULT 0,
            		 
              `fieldname` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'lalal' CHECK (`fieldname` <> 0 or `fieldname2` <> 0)
            		 
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
            		 
            1 row in set (0.000 sec)

            mysqldump just uses show create table. This appears to be fixed in at least the 10.3 instance about to be released.

            https://dbfiddle.uk/jhbJ8KGC - 10.3.36 has this fixed.

            danblack Daniel Black added a comment -   MariaDB [test]> select version(); +-----------------+ | version() | +-----------------+ | 10.3.39-MariaDB | +-----------------+ 1 row in set (0.000 sec)   MariaDB [test]> create table t ( `fieldname2` int(10) unsigned NOT NULL DEFAULT 0 , `fieldname` int(10) unsigned NOT NULL DEFAULT 0 comment 'lalal' CHECK (`fieldname` <> 0 or `fieldname2` <> 0) ); Query OK, 0 rows affected (0.002 sec)     MariaDB [test]> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `fieldname2` int(10) unsigned NOT NULL DEFAULT 0, `fieldname` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'lalal' CHECK (`fieldname` <> 0 or `fieldname2` <> 0) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 1 row in set (0.000 sec) mysqldump just uses show create table . This appears to be fixed in at least the 10.3 instance about to be released. https://dbfiddle.uk/jhbJ8KGC - 10.3.36 has this fixed.

            People

              danblack Daniel Black
              elvena Martín Alfano
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.