MySQL Bugs: #40477: mysql -o does not import tables if DROP DATABASE included

Bug #40477	mysql -o does not import tables if DROP DATABASE included
Submitted:	3 Nov 2008 14:00	Modified:	14 Oct 2010 10:15
Reporter:	Scott Noyes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.1.29	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
If the mysqldump output includes a DROP DATABASE statement, then loading that SQL back into the server while using the --one-database option will not load tables.

How to repeat:
CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (id int);

mysqldump --add-drop-database -B d1 > test.sql
mysql --one-database d1 < test.sql
mysql d1 -e "SHOW TABLES"

(last statement returns empty set, should return 't1')

Suggested fix:
Tables should be imported within the named database.

the problem comes from what happens when you drop your current database. the dump file essentially has:

DROP DATABASE d1;
CREATE DATABASE d1;
USE d1;

but because the mysql client is ignoring 'USE' (which is basically what --one-database does), you end up not being able to USE the database that you have just dropped and re-created.

i'm not sure why would want to mix the use of mysqldump --add-drop-database and mysql -o, and then use the same database name for the dump and restore.

If you know that your developers tend to go crazy making new test tables, and when it's time to restore the database you want to just throw it all away and start fresh, it would be reasonable to include --add-drop-database. Or maybe you're not the one that set up the cron backup task, and it's just the file you have to work with. Then, the time comes that you just want to restore one database out of a full server dump, so you try the -o option, and it doesn't work.

The -o option is also broken in that it's just looking at the USE statement. Which means if you dumped the server using -B, then it contains multiple CREATE DATABASE statements. And when you restore it with -o on a brand new server, since CREATE DATABASE comes before USE DATABASE, you'll get database created that you didn't ask for.

The documentation for '--one-database' needs to get improved to explain how limited it is. There's no way it can limit operations to a single database, without either implementing it as some sort of mode on the server that the client would then enable, or implementing a full-blown parser in the client.

It can't even be used to restore one database to another, which i think is the goal here -- it doesn't prevent 'USE db' from being handled, it just switches the client into the mode of rejecting all statements until it hits a 'USE database_from_command_line' statement.

It looks like a pretty seriously half-baked feature to me.

This bug has not been touched in a while. I think the current description is misleading and provides a false sense of safeness when using it, for multiple reasons. In fact, IMHO this option does not work as reasonably expected. It should either be fixed or documented with all the known problems or deprecated. 

An additional problem:

1) It allows you to do cross database statements even if they are writes:
==================================================
shell> mysql --one-database world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.1.47-enterprise-gpl-advanced-log MySQL Enterprise Server - Advanced Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW TABLES FROM test1;
Empty set (0.00 sec)

mysql> CREATE TABLE test1.t1 (id INT);
Query OK, 0 rows affected (0.41 sec)

mysql> SHOW TABLES FROM test1;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

mysql> USE test1;
Database changed
mysql> show tables;
Ignoring query to other database
==================================================

This gives a user a false sense of security, thinking only one database can be changed, when in fact, nothing of the sort happens.

The description is also very out of date:
shell> mysql  Ver 14.14 Distrib 5.1.47, for apple-darwin10.3.0 (i386) using readline 5.1
shell> mysql --help | grep -A1 one-database
  -o, --one-database  Only update the default database. This is useful for
                      skipping updates to other database in the update log.

What is an "update log", seems like this concept was killed back in 4.1
 http://dev.mysql.com/doc/refman/4.1/en/update-log.html

Please at least update the --help message stating this is *not* safe to use.

Updating description of --one-database in the manual as follows:

Ignore statements except those that occur while the default database
is the one named on the command line. This option is rudimentary and
should be used with care. Statement filtering is based only on USE
statements. 

Initially, mysql executes statements in the input because specifying
a database db_name on the command line is equivalent to inserting USE
db_name at the beginning of the input. Then, for each USE statement
encountered, mysql accepts or rejects following statements depending
on whether the database named is the one on the command line. The
content of the statements is immaterial.

Suppose that mysql is invoked to process this set of statements:

DELETE FROM db2.t2;
USE db2;
DROP TABLE db1.t1;
CREATE TABLE db1.t1 (i INT);
USE db1;
INSERT INTO t1 (i) VALUES(1);
CREATE TABLE db2.t1 (j INT);

If the command line is mysql --force --one-database db1, mysql
handles the input as follows:

* The DELETE statement is executed because the default database is db1,
  even though the statement names a table in a different database.

* The DROP TABLE and CREATE TABLE statements are not executed because
  the default database is not db1, even though the statements name a
  table in db1.

* The INSERT and CREATE TABLE statements are executed because the
  default database is db1, even though the CREATE TABLE statement names
  a table in a different database.

The --help message for the mysql client still needs to be fixed, since it refers to the update log that no longer exists. I suggest:

  -o, --one-database  Ignore statements except those that occur while the default database is the one named on the command line.

This bug seems to be a duplicate bug of Bug#54899, as both bugs
point to the same cause. 

Marking it as 'duplicate'.