Re: MySQL Dump Command Does Not Consider Foreign Key Dependencies

2004-03-05 Thread Heikki Tuuri 
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:  
>  >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-x1 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
> lrwxrwxrwx1 root root   20 2004-01-08 19:04
> /usr/lib/libc-client.so -> libc-client.so.2002d
> -rwxr-xr-x1 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' '--

RE: MySQL Dump Command Does Not Consider Foreign Key Dependencies

2004-03-05 Thread Carlos Proal 
i turn off the checks before a dump in order to restore it properly.

In my scripts looks like:
echo "SET FOREIGN_KEY_CHECKS=0;" > 
${mysql_backup_directory}/${2}/${database_filename}
mysqldump --opt -h $2 -P $3 -u $db_username --password=$db_password $1 >> 
${mysql_backup_directory}/${2}/${database_filename} 2>> $logfile
}

I would suggest that this can be included as a mysqldump option (--nochecks 
or  similar).

Carlos

Original Message Follows
From: Stephen Cuppett <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: MySQL Dump Command Does Not Consider Foreign Key Dependencies
Date: Fri, 05 Mar 2004 13:06:09 -0500
>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:  
>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-x1 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
lrwxrwxrwx1 root root   20 2004-01-08 19:04 
/usr/lib/libc-client.so -> libc-client.so.2002d
-rwxr-xr-x1 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-