getting procedure code via mysqldump

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

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?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: getting procedure code via mysqldump

--90e6ba613a30a8f2c2049fa781c1
Content-Type: text/plain; charset=ISO-8859-1

Hi John,
The lines you mention are comments , the comments in mysql sql files are
enclosed between two delimiters. The first is the sequence /* and the second
is the sequence */ , inside the comments you can have a marker constituted
by a ! and a number that represents a mysql version. These markers are used
to give instructions to specific mysql versions. When a mysql client reads a
sql file executes all commands enclosed plus the commented parts that
correspond to the server version. Usually you can delete those parts, since
in any case they are comments, but you should not need to delete them. I
hope I was enough clear,
My Android mobile soft keyboard is good, but not so inviting for writing
long messages!
Claudio
On Mar 30, 2011 1:10 AM, "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
>
> 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?
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>

--90e6ba613a30a8f2c2049fa781c1--

Re: getting procedure code via mysqldump

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
>
>
> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: getting procedure code via mysqldump

--bcaec51d29929b7508049fadbd55
Content-Type: text/plain; charset=ISO-8859-1

In case you use a linux or unix system, to strip off the comments in linux
bash is very easy, you can use this simple bash command:

grep -v "^/\*" yourdumpfile.sql > yourdumpfilewithoutcomments.sql

this will create a new dump without comments.

Cheers

Claudio


2011/3/30 Shawn Green (MySQL)

> 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
>>
>>
>> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--bcaec51d29929b7508049fadbd55--

Re: getting procedure code via mysqldump

Might it not be easier to use something like "show create procedure" instead? Given that the purpose is debugging, I would assume you want the exact text used to create the procedure, not the one with version-specifics removed.

You can still pump that into a file by using "mysql -e 'show create procedure procname\G' dbname > outputfile.sql". There will still be a bit of superfluous information as this is an information request, but that should be easily removed with some sed hacking.


----- Original Message -----
> From: "Claudio Nanni"
> To: "Shawn Green (MySQL)"
> Cc: "John G. Heim" , mysql@lists.mysql.com
> Sent: Wednesday, 30 March, 2011 9:01:06 AM
> Subject: Re: getting procedure code via mysqldump
>
> In case you use a linux or unix system, to strip off the comments in
> linux
> bash is very easy, you can use this simple bash command:
>
> grep -v "^/\*" yourdumpfile.sql > yourdumpfilewithoutcomments.sql
>
> this will create a new dump without comments.
>
> Cheers
>
> Claudio
>
>
> 2011/3/30 Shawn Green (MySQL)
>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: getting procedure code via mysqldump

From: "Claudio Nanni"
To: "Shawn Green (MySQL)"
Cc: "John G. Heim" ;
Sent: Wednesday, March 30, 2011 2:01 AM
Subject: Re: getting procedure code via mysqldump


> In case you use a linux or unix system, to strip off the comments in linux
> bash is very easy, you can use this simple bash command:
>
> grep -v "^/\*" yourdumpfile.sql > yourdumpfilewithoutcomments.sql

That didn't work because there are comments embedded in the line that
creates the procedure. For example:

/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE
`TIMETABLE_SYNC`()

That's all one line. It will probably wrap in my message. But the line
begins with a /* so its removed by your grep command.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: getting procedure code via mysqldump

Hi all!


John G. Heim wrote:
> From: "Claudio Nanni"
> [[...]]
>
>
>> In case you use a linux or unix system, to strip off the comments in
>> linux
>> bash is very easy, you can use this simple bash command:
>>
>> grep -v "^/\*" yourdumpfile.sql > yourdumpfilewithoutcomments.sql
>
> That didn't work because there are comments embedded in the line that
> creates the procedure. For example:
>
> /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003
> PROCEDURE `TIMETABLE_SYNC`()
>
> That's all one line. It will probably wrap in my message. But the line
> begins with a /* so its removed by your grep command.

No member of the "grep" family is the right tool for this, as they
always take (or ignore) whole lines.


This is a job for "sed" - try this (tested just on that single line):

sed '1,$s=/\*![3-6][0-9]* \([^*]*\)\*/=\1 =g' noversion.sql


HTH,
Joerg

--
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org