[MDEV-17363] Compressed columns cannot be restored from dump Created: 2018-10-03  Updated: 2019-06-19  Resolved: 2019-06-18

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Data Definition - Create Table
Affects Version/s: 10.3.9
Fix Version/s: 10.3.18, 10.4.6

Type: Bug Priority: Critical
Reporter: Oli Sennhauser Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

linux, ubuntu 16.04, n.a.


 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

 Comments   
Comment by Elena Stepanova [ 2018-10-03 ]

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)

Comment by Sergey Vojtovich [ 2019-06-13 ]

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

Generated at Thu Feb 08 08:35:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.