At 14:06 -0800 3/6/03, Rick Mann wrote:
>I have a DB where all of the tables are InnoDB, and there are a few
>straightforward FOREIGN KEY constraints. I would like to use mysqldump to
>back up the data or move it to another machine.
>
>The problem I'm running into is that when I try to import the data using
>mysql, the data is imported in the order in which it was dumped, that is to
>say, alphabetically by table.
>
>This ends up violating the key constraints. Is there a way to specify the
>order in which tables are dumped from mysqldump?
No, but you can disable key checking while you're reloading the file.
Invoke mysql, then issue these statements:
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE your_dump_file;
If that's all you're going to do, you can exit mysql. Otherwise,
reenable foreign key checking:
mysql> SET FOREIGN_KEY_CHECKS = 1;
>
>I also notice that in the dump, there are what appear to be statements
>disabling keys, but they're commented out. (I'm dumping from version
>3.23.51). How can I get these statements to be not commented out, and will
>that help? Also, in the comment, there is a !40000. What does that mean?
That means they'll be ignored for servers older than 4.0.0. They're
intended for MyISAM tables, actually, for which enabling/disabling keys
during loading gives you a performance boost. For InnoDB, they'll be
ignored in any case.
>
>Finally, I have INT UNSIGNED AUTO_INCREMENT primary keys in my tables, and
>if I try to mysqlimport from a file that has a value of "0", instead of
>writing the row like that, it picks a new value for that column. Is there
>any way to get mysqlimport to import exactly as written, rather than
>auto-incrementing?
No. You're abusing the AUTO_INCREMENT column by storing 0 in it.
You shouldn't do that, and now you know why.
>
>Thanks very much for any help.
>
>--
>Rick
