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

Compressed columns cannot be restored from dump

    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

            • Assignee:
              bar Alexander Barkov
              Reporter:
              oli Oli Sennhauser
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: