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

Compressed columns cannot be restored from dump

    XMLWordPrintable

Details

    Description

      Test case:

      SQL> use test;
       
      SQL> CREATE TABLE `mail` (
        `subject` varchar(255) DEFAULT NULL,
        `body` varchar(255) DEFAULT NULL,
        `attachement` longblob DEFAULT NULL,
        `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      ;
       
      SQL> ALTER TABLE mail MODIFY COLUMN `metadata` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMPRESSED DEFAULT NULL;
       
      SQL> SHOW CREATE TABLE mail\G
      *************************** 1. row ***************************
             Table: mail
      Create Table: CREATE TABLE `mail` (
        `subject` varchar(255) DEFAULT NULL,
        `body` varchar(255) DEFAULT NULL,
        `attachement` longblob DEFAULT NULL,
        `metadata` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      

      # mysqldump -u root test > test_dump.sql
      # mysql -uroot test < test_dump.sql 
      ERROR 1064 (42000) at line 25: 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 'CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT C' at line 5
      

      CREATE TABLE `mail2` (
        `subject` varchar(255) DEFAULT NULL,
        `body` varchar(255) DEFAULT NULL,
        `attachement` longblob DEFAULT NULL,
        `metadata` longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      

      --> fails with the same error

      CREATE TABLE `mail3` (
        `subject` varchar(255) DEFAULT NULL,
        `body` varchar(255) DEFAULT NULL,
        `attachement` longblob DEFAULT NULL,
        `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin /*!100301 COMPRESSED*/ DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      

      --> this works

      Attachments

        Activity

          People

            bar Alexander Barkov
            oli Oli Sennhauser
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.