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
• Status: Can't repeat
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S2 (Serious)
• Version: 5+
• OS: Any (all)
• 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.