Skip navigation links

MySQL Forums :: Stored Procedures :: Restore of mysqldump fails consistently


Advanced Search

Restore of mysqldump fails consistently
Posted by: Ken Gregg ()
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

Options: ReplyQuote


Subject Views Written By Posted
Restore of mysqldump fails consistently 2140 Ken Gregg 04/16/2012 10:39AM
Re: SOLVED: Restore of mysqldump fails consistently 953 Ken Gregg 04/16/2012 01:27PM
Re: SOLVED: Restore of mysqldump fails consistently 791 Rick James 04/17/2012 09:22AM
Re: SOLVED: Restore of mysqldump fails consistently 803 Ken Gregg 04/17/2012 09:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.