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

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report. In the essence, SHOW CREATE TABLE places the generated executable comment in the wrong place :

          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 COMPRESSED DEFAULT NULL
          		 
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
          		 
           
          		 
          MariaDB [test]> show create table mail3;
          		 
          +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
          		 
          | Table | Create Table                                                                                                                                                                                                                                                                        |
          		 
          +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
          		 
          | mail3 | CREATE TABLE `mail3` (
          		 
            `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 |
          		 
          +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
          		 
          1 row in set (0.00 sec)

          elenst Elena Stepanova added a comment - Thanks for the report. In the essence, SHOW CREATE TABLE places the generated executable comment in the wrong place : 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 COMPRESSED DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;   MariaDB [test]> show create table mail3; + -------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mail3 | CREATE TABLE `mail3` ( `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 | + -------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
          svoj Sergey Vojtovich added a comment -

          bar, please review fix for this bug in bb-10.3-svoj-MDEV-17363.

          svoj Sergey Vojtovich added a comment - bar , please review fix for this bug in bb-10.3-svoj- MDEV-17363 .

          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.