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.

2 Answers 2


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              = '' */ ;
/*!50003 CREATE*/ /*!50020 DEFINER=`lwdba`@``*/ /*!50003 PROCEDURE `LoadMyData`()
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
    SET NDX = NDX + 1;
END */;;
/*!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,
/*!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.

  • Bonus question: how to get rid of the annoying c style comments? /*!50003 etc
    – nakhli
    Jul 25, 2011 at 16:51
  • 2
    But...why? They are used to configure the mysql session. Jul 25, 2011 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. Jul 25, 2011 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 */;;
    – nakhli
    Jul 28, 2011 at 8:09
  • @Chaker Unfortunately, Stored Procedures came into existence in MySQL 5.0. Therefore, the directives have to remain. Jul 28, 2011 at 12:14

Actually, it seems that the necessary option to add DROP PROCEDURE in mysqldump output (at least in mysqldump Ver 10.13 Distrib 5.6.21-70.1, for debian-linux-gnu (x86_64)) is --add-drop-table:

mysqldump --quick --no-create-db --no-create-info --no-data --triggers --routines --no-data --all-databases --skip-opt --add-drop-trigger --create-options | grep -c 'DROP PROCEDURE'

mysqldump --quick --no-create-db --no-create-info --no-data --triggers --routines --no-data --all-databases --skip-opt --add-drop-trigger --create-options --add-drop-table | grep -c 'DROP PROCEDURE'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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