MySQL Bugs: #12200: mysqldump --tables dies if table inexistent

Bug #12200	mysqldump --tables dies if table inexistent
Submitted:	27 Jul 2005 9:30	Modified:	27 Jul 2005 12:21
Reporter:	d c	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.10nt	OS:	Windows (WinXP SP2)
Assigned to:		CPU Architecture:	Any

Description:
mysql server: 4.1.10nt
operatingsystem: WinXP SP2
Filesystem: NTFS
Memory: 1GB
Table types: innoDB

I encounter the error when I want to backup single tables. If I backup a list of tables and one of them is inexistent mysqldump dies without reporting any warning or error. Actually it causes a runtime error. It is then very difficoult to track the cause of this behaviour.

How to repeat:
mysqldump --opt --single-transaction -h 127.0.0.1 -pxxxxxx -u xxxxx databasename --tables tb1 tb2 tb3 tb_notexistent tb4 tb5

Suggested fix:
mysqldump should die and quit the dumping of the tables. Then report which table encountered problems.

Thank you for the bug report. I tried to repeat your test case on a newer 4.1.12 version:

In the test database:

mysql> create table t1 (c1 integer);
Query OK, 0 rows affected (0.22 sec)

mysql> create table t2 (c1 integer);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t2 values(2);
Query OK, 1 row affected (0.04 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------
-----------------+
| Table | Create Table
                 |
+-------+-----------------------------------------------------------------------
-----------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-------+-----------------------------------------------------------------------
-----------------+
1 row in set (0.00 sec)

mysql> show tables;
+------------------------+
| Tables_in_test         |
+------------------------+
| a                      |
| currencies             |
| deadlock_test          |
| details                |
| epos_transaction_lines |
| epos_transactions      |
| foo                    |
| foo2                   |
| master                 |
| t1                     |
| t11812                 |
| t2                     |
+------------------------+
12 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 4.1.12a-nt |
+------------+
1 row in set (0.00 sec)

mysql> exit

So, we have t1 and t2 InnoDB tables, but no t0 table. If i try the following:

C:\Documents and Settings\openxs>mysqldump -uroot -proot test --tables t1 t0 t2
-- MySQL dump 10.9
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       4.1.12a-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1146: Table 'test.t0' doesn't exist when doing LOCK TABLES

I've got just the behaviour you wanted. With additional options: 

C:\Documents and Settings\openxs>mysqldump --opt --single-transaction -uroot -proot test --tables t1 t0 t2
-- MySQL dump 10.9
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       4.1.12a-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

--
-- Dumping data for table `t1`
--

/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (1);
UNLOCK TABLES;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

--
-- Dumping data for table `t2`
--

/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
LOCK TABLES `t2` WRITE;
INSERT INTO `t2` VALUES (2);
UNLOCK TABLES;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

No error messages, but mysqldump does not die. Would you, please, give any additional information on how to reporduce the problem, or just try with a newer version of mysql.

Could be this bug
http://bugs.mysql.com/bug.php?id=9175
fixed in 4.1.12.

the bug has been fixed in version 4.1.13.
Similar to #9175

Thanks