ERROR when SQL export from MariaDB to MySQL

[NigelGomm]

Description

trying to export a table from a MariaDB server to a MYSQL one. Heidi generates this

CREATE TABLE IF NOT EXISTS `audittrail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Adatetime` datetime DEFAULT NULL,
  `Tablename` varchar(50) DEFAULT NULL,
  `Acontext` varchar(250) DEFAULT NULL,
  `Pk` int(11) DEFAULT NULL,
  `Jsondata` mediumtext /*!100301 COMPRESSED*/ DEFAULT NULL,
  `Verb` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pk` (`Pk`),
  KEY `verb` (`Verb`),
  KEY `tablename` (`Tablename`),
  KEY `adatetime` (`Adatetime`)
) ENGINE=InnoDB AUTO_INCREMENT=32429 DEFAULT CHARSET=latin1;

and the embedded "/!100301 COMPRESSED/" comment causes an error in mySQL.

HeidiSQL version

12.6.0.6849

Database server version

MariaDB 10.3

Error/Backtrace

/* [rentman21] SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 COMPRESSED*/ DEFAULT NULL,
  `Verb` varchar(10) DEFAULT NULL,
  PRIMARY KEY ' at line 7 */

Reproduction recipe

SQL EXPORT from mariadb to mysql server. a table with a medium text column.

[ansgarbecker]

That's an incompatibility between MariaDB and MySQL, not a bug directly in HeidiSQL. The CREATE code is taken as it is from MariaDB, which contains the problematic conditional comment, and MySQL does not support that compressed option.

However, I can probably fix that in HeidiSQL's yet small TSqlTranspiler.

[ansgarbecker]

Please note v12.6 is an old version. 12.8 is the current one.

[NigelGomm]

yes, i did update to 12.8 already just to see if it had been fixed. Looking forward to 12.9. thanks

[NigelGomm]

and similar trying to export to a postgres server ?

[ansgarbecker]

I just tried to reproduce the issue from MariaDB 11.3 to MySQL 8.3, but that works fine. The conditional comment /*!100301 COMPRESSED*/ should anyway only be executed on servers with v10.3.1 or higher - which is not the case on MySQL 8 (or 5 or whatever). Please post the MySQL server version you are using here.