Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm dumping my stored procedures only using the following command:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql

but the resulting dump file doesn't include a DROP PROCEDURE IF EXISTS before each procedure declaration.

How to add the drop query to my dump?

Thank you.

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

I dumped my stored procedures with the following

C:\>mysqldump -u... -p... -n -d --routines --triggers --all-databases > Z:\stuff.sql

Here is one the procedures with the DROP PROCEDURE included:

--
-- Dumping routines for database 'lovesh'
--
/*!50003 DROP PROCEDURE IF EXISTS `LoadMyData` */;
/*!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  = cp850 */ ;
/*!50003 SET character_set_results = cp850 */ ;
/*!50003 SET collation_connection  = cp850_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`lwdba`@`127.0.0.1`*/ /*!50003 PROCEDURE `LoadMyData`()
    DETERMINISTIC
BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
    END WHILE;
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 */ ;

When you used --skip-opt, that is what caused it because --opt includes --create-options and DROP PROCEDURE is considered MySQL-specific.

  -a, --create-options 
                      Include all MySQL specific create options.
                      (Defaults to on; use --skip-create-options to disable.)
  --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                      --create-options, --quick, --extended-insert,
                      --lock-tables, --set-charset, and --disable-keys.

--skip-opt would undo --create-options and thus remove DROP PROCEDURE.

Mystery solved !!!

Answer to Bonus Question

Those are not comments; those are MySQL directives. Whenever MySQL runs a Command, it looks for these directives encased in Comments

The number 50003 indicates that this command will execute if and only if the version of MySQL is 5.0.3 or greater.

Here is another example from a mysqldump:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `datas` (
  `ID` int(2) DEFAULT NULL,
  `CATEGORY` int(2) DEFAULT NULL,
  `ORD` int(1) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;

The number 40101 indicates that this command will execute if and only if the version of MySQL is 4.1.1 or greater.

These SQL directives are there for your protection if you load these mysqldumps into earlier versions. These directives allow full acceptance of certain commands. Please do not remove them.

However, if you only work with MySQL 5.0+ and plan to dump your stored procedures separately, you can strip them using Perl or awk. Personally I would leave them be.

share|improve this answer
    
Bonus question: how to get rid of the annoying c style comments? /*!50003 etc –  Chaker Nakhli Jul 25 '11 at 16:51
2  
But...why? They are used to configure the mysql session. –  Derek Downey Jul 25 '11 at 17:51
    
That's why I said I would it leave everything be. One could take a chance and strip those directives leaving just the commands. Then, one could just stay stuck in whatever version of MySQL one is using at the time. I once had a boss about 5 years ago that wanted Stored Procedures in PVCS with the directives stripped. It was needless to me, but that's what my boss wanted. The scripts worked on reload but were no longer guaranteed to be portable to other versions of MySQL. Again, I would just leave it be. –  RolandoMySQLDBA Jul 25 '11 at 17:56
    
@DTest The comments around the SET directive are ok and they are useful for portability. What annoys me are the comments around the creation of the stored procedure itself: /*!50003 CREATE*/ and /*!50003 PROCEDURE LoadMyData() ... END */;; –  Chaker Nakhli Jul 28 '11 at 8:09
    
@Chaker Unfortunately, Stored Procedures came into existence in MySQL 5.0. Therefore, the directives have to remain. –  RolandoMySQLDBA Jul 28 '11 at 12:14
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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