>Description:
When dumping a database with mysqldump using the "--opt"
parameters, certain databases fail being dumped:
/usr/bin/mysqldump: Got error: 1017: Can't find file: './db33862090/CKNPRJ.frm'
(errno: 24) when using LOCK TABLES
By replacing "--opt" with
"--add-drop-table --add-locks --all --extended-insert --quick"
(same as --opt except --lock-tables), these dumps do work fine.
When viewing this process in "show processlist" or in the querylog,
mysqldump tries to lock all tables with one "LOCK TABLES" before any
table is being dumped. Locking 8000 tables at once simply has to fail,
when only 1024 files can be opened in parallel: that's the problem.
>How-To-Repeat:
Create a database with 8000 tables and try a "mysqldump --opt"
with it. After a few seconds, the dump fails.
>Fix:
Locking all tables at the same time is fine for most backup
applications and protects from data inconsistencies like
"table x has been updated during dump, but not table y".
However, when the dump always fails, no backup can be created.
Solution:
Create a Fallback for mysqldump by using a new option "--relaxed":
try to lock all tables for read and dump as usual. When this
"LOCK TABLES" fails, spew out a warning and try to dump each table
without any locks instead of aborting the whole dump.
Depending on your situation, you like the consistency provided by
locking all tables, but you'd also prefer to have a maybe-inconsistent
backup than no backup at all.
Since other people do need a completly consistent database and might
want to manually fix it (by dropping tables and sorting out unneeded
data) when such a problem occurs, this "relaxed" has to be
an option.
Written in pseudo-code:
lock_tables_failed=0
sql("LOCK TABLES $table1 READ, $table2 READ, ..") if ("--lock-tables")
if ($?) then
if ($cmdoption "--relaxed") then
$lock_tables_failed=1
warn ("--lock-tables failed, ignoring locks in relaxed mode")
else
error ("--lock-tables failed, Dump aborted")
endif
endif
foreach $table (@all_tables) do
dump ($table)
done
sql ("UNLOCK TABLES") unless ($lock_tables_failed)
This gives certain advantages: Those who want dumps no matter if
the --lock-tables worked but prefer "complete" backups, can perform
such a "relaxed" backup with "--opt --relaxed".
Who strictly wants the complete database-integrity offered by
--lock-tables can use "--opt" as before, but also receives a much
more detailed error description.
>Submitter-Id: <submitter ID>
>Originator: Anders Henke, [EMAIL PROTECTED]
>Organization:
Schlund+Partner AG
>MySQL support: none
>Synopsis: mysqldump --lock-tables fails when trying to lock thousands of tables
>Severity: serious
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.46 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.46, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.46-Max-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 24 days 18 hours 18 min 22 sec
Threads: 1 Questions: 87659909 Slow queries: 2950 Opens: 9884754 Flush tables: 1
Open tables: 64 Queries per second avg: 40.972
>Environment:
System: Linux rdb19 2.4.13 #1 SMP Fri Nov 2 13:46:04 CET 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.2/specs
gcc version 2.95.2 20000220 (Debian GNU/Linux)
Compilation info: CC='gcc' CFLAGS='-O6 -fomit-frame-pointer' CXX='gcc'
CXXFLAGS='-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti'
LDFLAGS='-static'
LIBC:
lrwxrwxrwx 1 root root 13 Sep 4 18:35 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 888192 Jun 9 2001 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 2090160 Jun 9 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jun 9 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --libexecdir=/usr/sbin
--localstatedir=/var/lib/mysql --enable-shared --without-perl --without-readline
--without-docs --without-bench --with-mysqld-user=mysql --with-extra-charsets=all
--enable-assembler --with-raid --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --with-charset=latin1 --with-bench
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
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
