>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]
