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:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0d
                0d
                Logged:
                Time Spent - 1d 7.5h
                1d 7.5h

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.