Error when importing virtual column from MySQL to MariaDB

user_1764771 0
Viewed 484

I'm moving a database from MySQL to MariaDB, and testing export/import. One issue that's come up consistent is when a table has virtual columns. SHOW CREATE TABLE in MySQL returns this:

CREATE TABLE `table1` (
  `colA` varchar(50) NOT NULL,
  `colB' varchar(50) NOT NULL,
  'vir1` GENERATED ALWAYS AS (concat_ws(' ', `colA`, `colB`)) VIRTUAL NOT NULL,
  `colC` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MariaDB then reports an error when importing it:

#1064 - 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 'NOT NULL,
  `colC` varchar(50) DEFAULT NULL

The problem appears to be in the "VIRTUAL NOT NULL" part of the virtual column definition. If I edit the import sql file by hand, to this:

CREATE TABLE `table1` (
  `colA` varchar(50) NOT NULL,
  `colB' varchar(50) NOT NULL,
  'vir1` GENERATED ALWAYS AS (concat_ws(' ', `colA`, `colB`)) VIRTUAL,
  `colC` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

then it imports just fine, so I guess MariaDB doesn't like being told whether a virtual field is allowed to be null or not (which seems logical, since it wouldn't know whether the inputs are null or not), but the exported MySQL file always has either VIRTUAL NULL or VIRTUAL NOT NULL as part of the exported table definitions.

Is there a way to avoid this? I could grep through the exported file to s&r those definitions, but that seems kludgey and at risk of running into other issues later if it's a compatibility issue with a known solution.

mariadb mysql
2 Answers

The syntax you show works in MySQL, I just tested with MySQL 5.7 and it does not cause an error.

The MariaDB syntax is not compatible. This has been reported as a bug: https://jira.mariadb.org/browse/MDEV-10964

You could vote for that bug, or even contribute a patch to resolve it.

The bottom line is that MariaDB forked from MySQL in 2010, and the two products have been growing further and further apart ever since then. They should no longer be considered compatible.

Just like if you were to migrate from a MySQL database to PostgreSQL or Microsoft SQL Server, there will be some edits needed to make MySQL syntax work on different brands of RDBMS.

Bill_Karwin_208600

The problem is that null / not null is a part of mysql's definition of generated columns, therefore mysqldump exports these properties as part of dumping the table structures. This is the right thing to do as mysqldump is designed to work with mysql and not with mariadb.

You should use a proper ETL tool for migrating data between different database products, even if those pruducts are as closely related to each other as mysql and mariadb are.

user_53899970
Related