Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.25, 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
-
Ubuntu 20.04.1 LTS, 10.3.25+maria~focal
Description
Since version 10.3.5 MariaDB has supported for Oracle packages and when creating one with:
SET SQL_MODE='ORACLE';
|
DELIMITER ;;
|
CREATE PACKAGE "jobsched" AS
|
PROCEDURE test;
|
END ;;
|
DELIMITER ;
|
and doing backup with:
mysqldump --opt --single-transaction --default-character-set=utf8mb4 --disable-keys --routines --events --triggers greyhat_dkdefault > greyhat_dkdefault.sql
|
I get
- MySQL dump 10.17 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64)
|
--
|
-- Host: localhost Database: greyhat_dkdefault
|
-- ------------------------------------------------------
|
-- Server version 10.3.25-MariaDB-1:10.3.25+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 events for database 'greyhat_dkdefault'
|
--
|
|
|
--
|
-- Dumping routines for database 'greyhat_dkdefault'
|
--
|
/*!50003 DROP PACKAGE IF EXISTS `jobsched` */;
|
/*!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 = utf8 */ ;
|
/*!50003 SET character_set_results = utf8 */ ;
|
/*!50003 SET collation_connection = utf8_general_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="greyhat_dkdefault"@"%" PACKAGE "jobsched" AS
|
PROCEDURE test;
|
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 2021-01-18 8:11:28
|
but when restoring it I get an syntax error on DROP PACKAGE because the Oracle mode is not set
ERROR 1064 (42000) at line 25 in file: 'greyhat_dkdefault.sql': 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 `jobsched` */' at line 1
|
If I manually change the DROP PACKAGE to:
SET SQL_MODE='ORACLE';
|
/*!50003 DROP PACKAGE IF EXISTS `jobsched` */;
|
The restore works
Attachments
Activity
It would be much appreciated if it was automatic. We use MariaDB for hosting so its near impossible to know what the user is creating in the database and our workaround is that when restoring we inject the
SET @myold_sql_mode = @@sql_mode
SET SQL_MODE='ORACLE'
in before and then restore the mode once the package has been restored but its quite hacky. It would be preferred that if a backup is created it could be restored again without prior knowledge of that is actually created within.
There is an option for mysqldump which is meant to take care of it, -
compatible=<mode>(in this case it would be -compatible=oracle; and it seems to do the trick.Still, I wonder if it can be done better, more automatically. mysqldump detects the necessary sql_mode for CREATE statement, but the previous DROP still goes with the default mode. It's somewhat inconsistent.