Stephen, in <= 4.0, you should put
SET FOREIGN_KEY_CHECKS=0; at the start of the dump file. In 4.1, mysqldump knows to add to a dump file a line /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/ to disable foreign key checks in the dump file. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html ----- Original Message ----- From: "Stephen Cuppett" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, March 05, 2004 8:11 PM Subject: MySQL Dump Command Does Not Consider Foreign Key Dependencies > >Description: > The current mysqldump utility does an alphabetical dump of the > tables as well as a row by row dump of the data following each > table declaration. This is adequate enough for most > databases; however, if there are strict foreign key > constraints, the ordering will not always be conducive to > utilizing mysqldump output to restore databases causing major > headaches in order to restore when the table structures are > sophisticated and/or if there is considerable BLOB output in > the output. > >How-To-Repeat: > I apologize for any syntactical mistakes, but I'm sure you can > get the idea. Consider two tables: a,b. After creating > these tables, defining several columns, and then implementing > foreign keys, if "a" has a > foreign key constraint on yet-to-be-created "b" in dump output, > then "a" will fail to create. Further, even if "a" were to be > created, data input into "a" would fail without the supporting > rows in "b". > >Fix: > Ultimately, a dependency graph needs to be considered before > dumping in order to dump the tables and data in an order that > will allow the output to be used to restore the data rather > than alphabetical. However, if we assume that the data > existing now is in a normal state (simplified to mean, > fulfills foreign key constraints), then we could dump > structure and data in same order, but suppress details about > the foreign key constraints and have those appended to the > dump output as ALTER TABLE statements. I believe the latter > to be a much simpler fix and would eliminate this problem. > > >Submitter-Id: <submitter ID> > >Originator: Stephen Cuppett > >Organization: > > >MySQL support: none > >Synopsis: Dump Data ABC order Inconsiderate of Foreign Key > Constraints > >Severity: serious > >Priority: medium > >Category: mysql > >Class: change-request > >Release: mysql-4.0.17-standard (Official MySQL RPM) > > >C compiler: 2.95.3 > >C++ compiler: 2.95.3 > >Environment: > > System: Linux cuppett1 2.4.21-192-smp #1 SMP Wed Feb 18 19:31:29 UTC > 2004 i686 athlon i386 GNU/Linux > Architecture: i686 > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > /usr/bin/cc > GCC: Reading specs from /usr/lib/gcc-lib/i586-suse-linux/3.3.1/specs > Configured with: ../configure --enable-threads=posix --prefix=/usr > --with-local-prefix=/usr/local --infodir=/usr/share/info > --mandir=/usr/share/man --libdir=/usr/lib > --enable-languages=c,c++,f77,objc,java,ada --disable-checking > --enable-libgcj --with-gxx-include-dir=/usr/include/g++ > --with-slibdir=/lib --with-system-zlib --enable-shared > --enable-__cxa_atexit i586-suse-linux > Thread model: posix > gcc version 3.3.1 (SuSE Linux) > Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 > -fno-strength-reduce' CXX='g++' CXXFLAGS='-O2 -mcpu=i486 > -fno-strength-reduce -felide-constructors -fno-exceptions > -fno-rtti ' LDFLAGS='' ASFLAGS='' > LIBC: > -rwxr-xr-x 1 root root 1469811 2003-09-23 19:05 /lib/libc.so.6 > -rw-r--r-- 1 root root 13553180 2003-09-23 12:04 /usr/lib/libc.a > -rw-r--r-- 1 root root 204 2003-09-23 12:04 /usr/lib/libc.so > -rw-r--r-- 1 root root 982008 2003-09-23 14:29 > /usr/lib/libc-client.a > lrwxrwxrwx 1 root root 20 2004-01-08 19:04 > /usr/lib/libc-client.so -> libc-client.so.2002d > -rwxr-xr-x 1 root root 770436 2003-09-23 14:29 > /usr/lib/libc-client.so.2002d > Configure command: ./configure '--disable-shared' > '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' > '--with-server-suffix=-standard' '--without-embedded-server' > '--without-berkeley-db' '--with-innodb' '--without-vio' > '--without-openssl' '--enable-assembler' '--enable-local-infile' > '--with-mysqld-user=mysql' > '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' > '--with-extra-charsets=complex' '--exec-prefix=/usr' > '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' > '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' > '--infodir=/usr/share/info' '--includedir=/usr/include' > '--mandir=/usr/share/man' '--enable-thread-safe-client' > '--with-comment=Official MySQL RPM' 'CC=' 'CFLAGS=-O2 -mcpu=i486 > -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 > -fno-strength-reduce -felide-constructors -fno-exceptions > -fno-rtti ' 'CXX=' > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
