List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 7 2003 3:27am
Subject:Re: mysqldump and InnoDB tables
View as plain text  
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

Thread
mysqldump and InnoDB tablesRick Mann6 Mar
  • Re: mysqldump and InnoDB tablesDaniel Kasak6 Mar
  • Re: mysqldump and InnoDB tablesPaul DuBois7 Mar
  • Re: mysqldump and InnoDB tablesDyego Souza do Carmo7 Mar
  • re: mysqldump and InnoDB tablesVictoria Reznichenko7 Mar