>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