Problem importing mysql procedures generated from mysqldump --routines

Question

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?

Answer 1

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

Answer 2

If something's erroring when running the queries in MyDB2, it's best to:

1. Run mysqldump to save the output to a saved file.
2. Run the file bit by bit, to identify which part has the problem.
3. Fix that bit.

I once had a problem like this where I was exporting from an old version of mysql and importing into a newer one, which had declared one of my column names a reserved word. Are your two databases on different servers running different versions of mysql? Or is there some other difference between the databases (e.g. character set)?