Restore of mysqldump fails consistently
Date: April 16, 2012 10:39AM
MySQL 5.5.13, mysqldump 10.13
Using:
mysqldump --user=root --password=PwPwPw --no-data --routines Example_DB > "BU.sql"
mysqldump is misplacing one */ for stored procedures so that following source produces SQL (pasted below source below) which will not restore (syntax errors) unless I manually fix it.
Here's the Source:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_example1`(
-- Table columns:
Arg1 varchar(100),
Arg2 varchar(100),
Arg3 varchar(100),
)
BEGIN
/*
Here are some general comments about this procedure
*/
-- Here is the code itself - has been stripped out for brevity because it has no effect on the behavior
END
Here's the SQL produced which produces syntax errors when trying to run:
- MySQL dump 10.13 Distrib 5.5.13, for Win64 (x86)
--
-- Host: localhost Database: Example_DB
-- ------------------------------------------------------
-- Server version 5.5.13
/*!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 utf8 */;
/*!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 */;
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 */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_example1` */;
/*!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 = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost` PROCEDURE `sp_example1`(
-- Table columns:
Arg1 varchar(100),
Arg2 varchar(100),
Arg3 varchar(100),
)
BEGIN
/*
Here are some general comments about this procedure
*/
-- Here is the code itself - has been stripped out for brevity because it has no effect on the behavior
END */;;
-----
Note that the ending '*/' on the line following 'DELIMITER ;;' is missing and it appears instead at the end in the 'END */;;' line.
If I manually add the missing '*/' and remove the one on the 'END' statement, all is well.
This happens in virtually all of my stored procedures. This worked fine in previous version of MySQL with no changes in code. Can anybody help? I have tried various combinations of mysqldump parameters for different kinds of dumps to no avail.
Thanks,
Ken