MySQL dump import into MariaDB syntax error to use near ‘*/’

This is the result from mysqldump 10.13 and MySQL 8.0.28 Server.

/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) DEFAULT NULL COMMENT 'user name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='User table';

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`admin`@`%`*/ /*!50003 TRIGGER `user_trigger` AFTER INSERT ON `user` FOR EACH ROW
INSERT INTO user_language(user_id, language_id) VALUES (1,1); */;;

If I try to import this piece with mysql <db> < dump.sql into a MariaDB 10.6.7, it results into ERROR 1064 (42000) at line 9: 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 '*/' at line 1

I would appreciate any info on why this is happening and how to avoid it.

There are significant differences in the mysql and information_schema databases between 8.0 and MariaDB. Avoid trying to move any data either direction. (Ditto for performance_schema.)

The dump formats are not completely compatible.

This particular issue is explained in “MariaDB Server Documentation » SQL Statements & Structure » SQL Statements » Comment Syntax“:

The following executable statement will not work due to the delimiter inside the executable portion:

/*M!100100 select 1 ; */

ERROR 1064 (42000): 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 ” at line 1

Instead, the delimiter should be placed outside the executable portion:

/*M!100100 select 1 */;

+---+
| 1 |
+---+
| 1 |
+---+

Seems like BEGIN ... END solves the issue.

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`admin`@`%`*/ /*!50003 TRIGGER `user_trigger` AFTER INSERT ON `user` FOR EACH ROW
BEGIN
    INSERT INTO user_language(user_id, language_id) VALUES (1,1);
END */;;