By nakhli

2011-07-25 15:58:51 8 Comments

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.


@redguy 2015-01-05 11:18:34

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'

@RolandoMySQLDBA 2011-07-25 16:20:17

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.

@nakhli 2011-07-25 16:51:33

Bonus question: how to get rid of the annoying c style comments? /*!50003 etc

@Derek Downey 2011-07-25 17:51:20

But...why? They are used to configure the mysql session.

@RolandoMySQLDBA 2011-07-25 17:56:06

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.

@nakhli 2011-07-28 08:09:03

@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 */;;

@RolandoMySQLDBA 2011-07-28 12:14:37

@Chaker Unfortunately, Stored Procedures came into existence in MySQL 5.0. Therefore, the directives have to remain.

@nakhli 2011-07-28 12:28:28

Makes sense. Thank you for sharing all this information. It was very helpful.

@DougW 2014-11-14 19:27:07

Here's a one-liner to remove those directives, just in case you know that you actually don't want them. mysqldump -uusername -p --routines --no-create-info --no-data --no-create-db databasename | grep -v "^/\*.* SET" | grep -v "^--" | sed -E "s|^/\*.* (DROP.*) \*/;|\1;|"

Related Questions

Sponsored Content

1 Answered Questions

Error 1304 when creating a procedure after a drop if exists

2 Answered Questions

3 Answered Questions

[SOLVED] Users complain that system runs slow when mysqldump is in progress

2 Answered Questions

[SOLVED] How to log verbose output from mysqldump?

1 Answered Questions

1 Answered Questions

[SOLVED] MySQLDump issue with a special characters

2 Answered Questions

[SOLVED] MySQL : How to restore mysql dump (.sql) ignoring DROP TABLE statement?

1 Answered Questions

2 Answered Questions

Sponsored Content