导入mysqldump生成的mysql程序--routines

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.

我使用mysqldump工具制作我的数据库的副本。问题是，当我使用--routines参数输出我的存储过程以及我的数据时，生成的输出在我尝试导入时会导致错误。

It goes something like this:

它是这样的：

% mysqldump --routines MyDB | mysql MyDB2

(where MyDB2 already exists but is empty)

（MyDB2已经存在但是空的）

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.

如果省略--routines，一切正常。

Has anyone else encountered this?

有人遇到过这种情况么？

2 个解决方案

#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

#2

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

如果在MyDB2中运行查询时出现错误，最好：

Run mysqldump to save the output to a saved file.
运行mysqldump将输出保存到已保存的文件。
Run the file bit by bit, to identify which part has the problem.
逐位运行文件，以确定哪个部分有问题。
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)?

我曾经遇到过这样的问题，我从一个旧版本的mysql导出并导入到一个更新的版本，它已经宣布我的一个列名称是一个保留字。您的两个数据库是否在不同的服务器上运行不同版本的mysql？或者数据库之间是否存在其他差异（例如字符集）？

#1