Menu
I use the mysqldump tool to make copies of my database. The problem is, when I use the --routines parameter to output my stored procedures along with my data, the generated output causes an error when I try to import it.
It goes something like this:
% mysqldump --routines MyDB | mysql MyDB2
(where MyDB2 already exists but is empty)
The error I get is the following:
ERROR 1064 (42000) at line 307: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 23
Everything works correctly if I omit the --routines.
Has anyone else encountered this?
202
Importing your stored procedures, functions and triggers By default, mysqldump imports views and triggers. However it does not import procedures, functions and events. To import procedures and functions, the --routines option should be specified, and to import events, the --events option should be specified.
MySQL stored procedures are pre-compiled SQL statements stored in a database. They are subroutines containing a name, a parameter list, and SQL statements.
I was able to get this to work by splitting it into two calls:
% mysqldump MyDB | mysql MyDB2
% mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt MyDB | mysql MyDB2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With