List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:March 30 2011 2:51am
Subject:Re: getting procedure code via mysqldump
View as plain text  
On 3/29/2011 19:09, John G. Heim wrote:
> I would like to use mysqldump to get a copy of the code for a stored
> procedure in a format that is similar to the code I used to create it.
> The problem is that I'm blind and I have to listen to the code to debug
> it. I think I have a file containing the code that I used to create the
> stored procedure but I want to make absolutely sure.
>
> This is what I've tried:
>
> mysqldump --p --routines --no-create-info --no-data --no-create-db
> --skip-opt --skip-comments --compatible=ansi --result=routines.sql
> <database>
>
> My problem is that generates a file with a lot of lines I don't
> understand. for example:
>
> /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003
> PROCEDURE `TIMETABLE_SYNC`()
>
> That appears to be the line to create the stored procedure
> 'timetable_sync'. But what's with all the other stuff on that line? Can
> i get rid of it?
>
>
>

As Claudio mentioned, those are version-sensitive comments. In order for 
a MySQL server to not ignore the comment, it must be a version equal to 
or greater than the value tagged in the comment.

For example, stored procedures did not exist before version 5.0.3 . So 
all of the stored procedure will be enclosed with comments that look like

/*!50003   */

We enhanced the security of the stored procedures themselves by adding 
the DEFINER= option to the definition. We did this in version 5.0.20. 
That is why that part of the stored procedure was dumped using the 
comment tags

/*!50020   */

Unfortunately, I have no way at this time to separate the 
version-specific comments from the rest of the dump. Perhaps someone 
better than I at using grep, sed, or awk could produce a script to strip 
those comments and share with the list?

Yours,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
getting procedure code via mysqldumpJohn G. Heim30 Mar
  • Re: getting procedure code via mysqldumpClaudio Nanni30 Mar
  • Re: getting procedure code via mysqldumpMySQL)30 Mar
    • Re: getting procedure code via mysqldumpClaudio Nanni30 Mar
      • Re: getting procedure code via mysqldumpJohan De Meersman30 Mar
  • Re: getting procedure code via mysqldumpJohn G. Heim30 Mar
    • Re: getting procedure code via mysqldumpJoerg Bruehe30 Mar