[MDEV-17363] Compressed columns cannot be restored from dump - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3.9
Fix Version/s: 10.3.18, 10.4.6
Component/s: Admin statements, Data Definition - Create Table
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

Attachments

Activity

Ascending order - Click to sort in descending order

Oli Sennhauser created issue - 2018-10-03 14:31

Elena Stepanova made changes - 2018-10-03 14:51

Field	Original Value	New Value
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	Test case: {code:sql} 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 {code} {noformat} # 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 {noformat} {code:sql} 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; {code} --> fails with the same error {code:sql} 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; {code} --> this works

Elena Stepanova made changes - 2018-10-03 14:54

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2018-10-03 14:56

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)

Elena Stepanova added a comment - 2018-10-03 14:56 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)

Elena Stepanova made changes - 2018-10-03 14:56

Component/s		Admin statements [ 11400 ]
Component/s	Backup [ 13902 ]
Component/s	Data Definition - Alter Table [ 10114 ]
Fix Version/s		10.3 [ 22126 ]
Assignee		Sergey Vojtovich [ svoj ]

Sergei Golubchik made changes - 2019-03-29 12:04

Fix Version/s

10.4 [ 22408 ]

Sergey Vojtovich added a comment - 2019-06-13 11:45

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

Sergey Vojtovich added a comment - 2019-06-13 11:45 bar , please review fix for this bug in bb-10.3-svoj- MDEV-17363 .

Sergey Vojtovich made changes - 2019-06-13 11:45

Assignee	Sergey Vojtovich [ svoj ]	Alexander Barkov [ bar ]
Status	Confirmed [ 10101 ]	In Review [ 10002 ]

Alexander Barkov made changes - 2019-06-15 08:24

Assignee	Alexander Barkov [ bar ]	Sergey Vojtovich [ svoj ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergey Vojtovich made changes - 2019-06-17 15:42

Assignee

Sergey Vojtovich [ svoj ]

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2019-06-18 03:50

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2019-06-18 03:50

Fix Version/s		10.4.6 [ 23412 ]
Fix Version/s		10.3.18 [ 23719 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 89883 ]

MariaDB v4 [ 155011 ]

People

Assignee:: Alexander Barkov

Reporter:: Oli Sennhauser

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-10-03 14:31

Updated:: 2019-06-19 18:26

Resolved:: 2019-06-18 03:50

Git Integration

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

MariaDB Server