0

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.

1
  • File a bug report with MariaDB.com.
    – Rick James
    Mar 10 at 15:32

3 Answers 3

0

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.)

2
  • Do you have an idea how to import a MySQL dump to MariaDB?
    – Aley
    Mar 10 at 10:01
  • @Aley - If you already have the extra databases in the dump file, the best I can suggest is to edit the file to get rid of loading them. (Maybe someone else has a better idea.)
    – Rick James
    Mar 10 at 15:29
0

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 |
+---+
7
  • The snippet from the question is just an excerpt. The actual dump is huge and contains a lot more triggers. Do you know a way how to make the dump compatible with MariaDB?
    – Aley
    Mar 9 at 16:57
  • Also the interesting part is that the error points to line 9, which is the forelast line and it does not contain any semicolons.
    – Aley
    Mar 9 at 17:00
  • @Aley the error points to line 9, which is the forelast line and it does not contain any semicolons Empty lines are not counted, maybe? Do you know a way how to make the dump compatible with MariaDB? Try to use mariadb-dump (really - mysqldump) taken from MariaDB installation.
    – Akina
    Mar 9 at 18:00
  • Empty lines count. I tested it with another empty line. It shows line 10 now.
    – Aley
    Mar 10 at 9:56
  • Exporting it with mariadb-dump shows the same error on reimport.
    – Aley
    Mar 10 at 9:59
0

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 */;;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.