re: mysqldump and InnoDB tables
On Friday 07 March 2003 00:06, 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. Use SET FOREIGN_KEY_CHECKS=0 command. > 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? No. > Also, in the comment, there is a !4. What does that mean? It means that it will work only since 4.0 version. > 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, if you insert NULL or 0 into auto_increment column, column value will be incremented. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump and InnoDB tables
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 !4. 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump and InnoDB tables
Respondendo, quinta-feira, 6 de março de 2003, 19:06:06, Mensagem Original: RM> I have a DB where all of the tables are InnoDB, and there are a few RM> straightforward FOREIGN KEY constraints. I would like to use mysqldump to RM> back up the data or move it to another machine. RM> The problem I'm running into is that when I try to import the data using RM> mysql, the data is imported in the order in which it was dumped, that is to RM> say, alphabetically by table. before import the MySQLDump file type: SET FOREIGN_KEY_CHECKS=0; after import: SET FOREIGN_KEY_CHECKS=1; This command disable and enable FOREIGN KEY CHECKS ;) RM> This ends up violating the key constraints. Is there a way to specify the RM> order in which tables are dumped from mysqldump? Yes. The mysqldump not respect the rules of key constraints. - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into "my eyes" Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump and InnoDB tables
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 doesn't sound right at all. mysqldump should also be dumping the primary keys, so it doesn't matter what order they're dumped / imported in, because they get the same primary key. Are you sure this isn't happening? Maybe post the table def and a few lines of a mysqldump file. This ends up violating the key constraints. Is there a way to specify the order in which tables are dumped from mysqldump? You can specify a particular table, eg: mysqldump databasename tablename As for the order of records, no. Can't do that. But you shouldn't have to. 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 !4. What does that mean? I think mysql reads the 'disable keys' command even though they are commented out. Not sure what the !4 means. Maybe has something to do with the version of mysql / mysqldump you have. 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? I was under the impression that auto_increment fields started at 1. Maybe when mysql gets told to put a zero in there it assumes you want it to auto-increment. Dunno. Never tried it. Do you have quotes around the zero? Thanks very much for any help. 's OK -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump and InnoDB tables
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? 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 !4. What does that mean? 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? Thanks very much for any help. -- Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
