MySQL Bugs: #25516: Export from mysqldump with --routines fails on import

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

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"

Thank you for the report.

I can not repeat error using last BK sources

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)

When user tries to edit an existing stored procedure, MySQL Query browser add the clause DEFINER='user' and stored procedure can't be edit.