MySQL – DROP PROCEDURE IF EXISTS Not Included in mysqldump

backupdumpMySQLmysqldumpstored-procedures

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.

Best Answer

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.

Related Question