Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6.4, 10.3(EOL), 10.4(EOL), 10.5, 10.7(EOL), 10.8(EOL)
-
None
-
10.6.4-1-MariaDB-enterprise-log
Description
A dump was taken on a system that has PL/SQL packages with the below version and command.
-- MariaDB dump 10.19 Distrib 10.6.4-1-MariaDB, for Linux (x86_64) |
mysqldump my_schema_name -R -e --triggers --single-transaction
|
The dump contains the following line:
/*!50003 DROP PACKAGE IF EXISTS `SomePackageName` */; |
Loading the dump into a blank schema on another MariaDB server of the same version and default config (incl. default SQL mode) fails:
ERROR 1064 (42000) at line 5745: 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 'PACKAGE IF EXISTS `SomePackageName` */' at line 1
|
The dump seems to set up the proper mode for most of what is does, like CREATE PACKAGE statements - but it fails on the above comment. Here is a line form the dump which sets the SQL mode to include Oracle (found multiple times inside the dump).
/*!50003 SET sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT' */ ; |
Attachments
Activity
How to reproduce:
mysql test -e "SET sql_mode=ORACLE;
|
DELIMITER $$
|
CREATE OR REPLACE PACKAGE pkg AS
|
END;
|
$$
|
DELIMITER ;"
|
|
|
mysqldump --routines test | mysql test
|
Result:
ERROR 1064 (42000) at line 21: 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 'PACKAGE IF EXISTS `pkg` */' at line 1
|
This should fix it, the line that sets the SQL mode for the CREATE part just needs to be moved up a bit to also cover the DROP IF EXISTS part:
diff --git a/client/mysqldump.c b/client/mysqldump.c
|
index 17e793d3a9e..e4b046f1c94 100644
|
--- a/client/mysqldump.c
|
+++ b/client/mysqldump.c
|
@@ -2861,6 +2861,9 @@ static uint dump_routines_for_db(char *db)
|
create_caption_xml[i]);
|
continue;
|
}
|
+
|
+ switch_sql_mode(sql_file, ";", row[1]);
|
+
|
if (opt_drop)
|
fprintf(sql_file, "/*!50003 DROP %s IF EXISTS %s */;\n",
|
routine_type[i], routine_name);
|
@@ -2900,9 +2903,6 @@ static uint dump_routines_for_db(char *db)
|
"--\n");
|
}
|
|
-
|
- switch_sql_mode(sql_file, ";", row[1]);
|
-
|
fprintf(sql_file,
|
"DELIMITER ;;\n"
|
"%s ;;\n"
|
|
Pull request against 10.3 branch
This patch is OK to push:
https://github.com/MariaDB/server/pull/2107/commits/a0af3937e574973f8e72ceb57a36296b11a9720a
The SQL Mode is not set correctly before DROP PACKAGE
MariaDB [(none)]> use testMariaDB [test]> DELIMITER $$MariaDB [test]> CREATE OR REPLACE PACKAGE p_t AS PROCEDURE dummy(id INT); END;$$MariaDB [test]> DELIMITER ;# mariadb-dump -R test-- MariaDB dump 10.19 Distrib 10.8.2-MariaDB, for debian-linux-gnu (x86_64)---- Host: localhost Database: test-- -------------------------------------------------------- Server version 10.8.2-MariaDB-1:10.8.2+maria~focal/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8mb4 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Dumping routines for database 'test'--/*!50003 DROP PACKAGE IF EXISTS `p_t` */;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = latin1 */ ;/*!50003 SET character_set_results = latin1 */ ;/*!50003 SET collation_connection = latin1_swedish_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT' */ ;DELIMITER ;;CREATE DEFINER="root"@"localhost" PACKAGE "p_t" AS PROCEDURE dummy(id INT); END ;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2022-04-29 5:31:10