Bug #25516 Export from mysqldump with --routines fails on import
Submitted: 10 Jan 2007 3:08 Modified: 11 Jan 2007 0:00
Reporter: Peter Morgan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5+ OS:Any (all)
Assigned to: CPU Architecture:Any
Tags: mysqldump, routines, stored procedures

[10 Jan 2007 3:08] Peter Morgan
Description:
Exporting a database with mysqldump uses the delimiters of ;;, This causes an error on import as mysql complaints with :-

Error while executing query ;; Query was empty (errno: 1065) which is the following line
DELIMITER ;; 

Luckily on the forums I got the solution with the following command

mysqldump -u root -p --routines --databases my_database \
| sed -e "s/;;/\$\$/g" \
> myfile.sql

How to repeat:
Export a database with

mysqldump -u root -p --routines --databases my_database > myfile.sql

and then import that on any client

Suggested fix:
Somehow change the delimiters in the stored procedures automatically. The mysql query browser uses $$ so I would use them to be consistent.

An implementation of this as a pipe
| sed -e "s/;;/\$\$/g"
[10 Jan 2007 8:22] Sveta Smirnova
Thank you for the report.

I can not repeat error using last BK sources
[11 Jan 2007 0:00] Peter Morgan
Sveta
Can you let me know which version of mysqldump you are using

Mine is
mysqldump  Ver 10.10 Distrib 5.0.24a, for pc-linux-gnu (i486)
[5 Jun 2007 14:31] David Benavides
When user tries to edit an existing stored procedure, MySQL Query browser add the clause DEFINER='user' and stored procedure can't be edit.