Bug #19246 Backup Stored Proc Mysql server 5.0.15-nt
Submitted: 21 Apr 2006 6:57 Modified: 12 May 2006 11:25
Reporter: Lim Boon Ting Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.15-nt OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[21 Apr 2006 6:57] Lim Boon Ting
Description:
I found MySQL version 5.0.15-nt cannot backup stored procedure.
When i try to run backup script in mysql cmd

mysql console
C:\Program Files\xampp\mysql\bin>mysqldump -uroot -ppassword testdb>E:\backup.txt

In the backup.txt : it able to backup all the tables and views but excluded the stored procedures.

This caused another problem, cannot create stored procedure in mysql console through script.

I assume the problem of MySQL version 5.0.15-nt doesn't recognised syntax of stored procedure.
Therefore it caused cannot create stored procedure through script in mysql cmd
Eg:
I save my stored proc query syntax into temp_sp.sql
C:\Program Files\xampp\mysql\bin>mysql -uroot -ppassword -D testDB <"C:temp_sp.sql";

But error display 
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to user near 'DROP PROCEDURE IF EXISTS `ifcasp_collection`' 
at line 1 

But i can create the stored proc using mysql query browser. 
And mysql cmd when i copy the syntax and paste into cmd console it able to create stored proc. 

That means mysql query for stored procedure is correct.
Any solution or suggestion for me?
If i want to create stored procedure through script.

How to repeat:
I need to backup databases.
My day end program will control and execute backup database script every nite.
THe problem found, when i try to create store procedure using mysql cmd through script.
[21 Apr 2006 6:57] Lim Boon Ting
Error

Attachment: error1.JPG (image/pjpeg, text), 60.73 KiB.

[21 Apr 2006 6:58] Lim Boon Ting
sql console

Attachment: sp1.JPG (image/pjpeg, text), 69.37 KiB.

[21 Apr 2006 6:59] Lim Boon Ting
sp

Attachment: sp2.JPG (image/pjpeg, text), 80.10 KiB.

[21 Apr 2006 6:59] Lim Boon Ting
mysql version 5.0.15-nt
[21 Apr 2006 7:54] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.20, and use -R mysqldump option (read http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html for the details).
[12 May 2006 11:25] Lim Boon Ting
Thanks alot