Bug #54899 --one-database option cannot handle DROP/CREATE DATABASE commands
Submitted: 29 Jun 2010 17:50 Modified: 20 Dec 2010 20:12
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.1.47 OS:Any
Assigned to: Nirbhay Choubey CPU Architecture:Any

[29 Jun 2010 17:50] Todd Farmer 
Description:
The --one-database option for the mysql command-line client is useful for restoring a single database from a SQL file containing multiple databases.  However, it when the commands include DROP/CREATE DATABASE, it appears to fail to track the filtering context correctly:

D:\mysql-5.1.47-win32>mysql -uroot -P3309 --one-database test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.1.47-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table t1 (a int);
Ignoring query to other database
mysql> show tables;
Ignoring query to other database

The filter context is not lost when DROP/CREATE DATABASE commands are not used, but "USE db" statements are used to switch default schema:

D:\mysql-5.1.47-win32>mysql -uroot -P3309 --one-database test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.1.47-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.09 sec)

mysql> use mysql
Database changed
mysql> show tables;
Ignoring query to other database
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

This shows that the mysql cli is parsing the string database name in USE db, but apparently not correctly after DROP/CREATE DATABASE has been issued.

How to repeat:
1.  Connect using --one-database
2.  DROP DATABASE connected_db
3.  CREATE DATABASE connected_db
4.  USE connected_db
5.  CREATE TABLE t1(a INT);

Suggested fix:
Track the database context across DROP/CREATE DATABASE commands.
[9 Oct 2010 10:36] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120428

3523 Nirbhay Choubey	2010-10-09
      Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      --one-database option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by introducing a new variable 'opt_db', which persistently stores the
      database name specified at mysql client's command line and fixing the if
      statement that governed the filtering flag by adding a check on whether the
      database name in the USE command is similar to one specified at command line.
     @ client/mysql.cc
        Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        During mysql client start-up, 'opt_db' gets initialized in get_options function
        with the database name specified at the command line.
        
        Now, when a 'USE' command is run after the recreation of database ( i.e. drop
        followed by create database), the function com_use is called which in-turn calls
        get_current_db(), which sets current_db. Now as the current database was dropped
        and recreated, get_current_db() sets current_db to a null value (0), and hence
        skip_updates gets set to 1 for --one_database.
        
        The bug was resolved by checking if the database name used in the 'USE'
        command is same database that was entered at the command line.
[11 Oct 2010 6:32] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120461

3523 Nirbhay Choubey	2010-10-11
      Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      --one-database option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by introducing a new variable 'opt_db', which persistently stores the
      database name specified at mysql client's command line and fixing the if
      statement that governed the filtering flag by adding a check on whether the
      database name in the USE command is similar to one specified at command line.
     @ client/mysql.cc
        Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        During mysql client start-up, 'opt_db' gets initialized in get_options function
        with the database name specified at the command line.
        
        Now, when a 'USE' command is run after the recreation of database ( i.e. drop
        followed by create database), the function com_use is called which in-turn calls
        get_current_db(), which sets current_db. Now as the current database was dropped
        and recreated, get_current_db() sets current_db to a null value (0), and hence
        skip_updates gets set to 1 for --one_database.
        
        The bug was resolved by checking if the database name used in the 'USE'
        command is same database that was entered at the command line.
     @ mysql-test/r/mysql.result
        Added test case for bug#54899.
     @ mysql-test/t/mysql.test
        Added test case for bug#54899.
[18 Oct 2010 8:52] Sergey Vojtovich 
BUG#40477 was marked as duplicate.
[19 Oct 2010 10:48] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121106

3535 Nirbhay Choubey	2010-10-19
      Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      --one-database option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by making get_current_db() a no-op function when one_database is set,
      and hence, under that condition current_db will not get altered.
      Note, however the value of current_db can change when we execute 'connect'
      command with a differnet database to reconnect to the server, in which case,
      the behavior of --one-database will be formulated using this new database.
     @ client/mysql.cc
        Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        Added an if statement at the beginnning of get_current_db() , which makes it
        a no-op function if one-database option is specified, and hence current_db
        remains unchanged. Besides that, mysql client will now output a new warning 
        message when a 'USE' command is executed with a different database.
        
        Changed the help message for one-database option to a more approprite message
        as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and a general test case to check one-database
        option related functionality.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and a general test case to check one-database
        option related functionality.
[31 Oct 2010 5:09] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122367

3549 Nirbhay Choubey	2010-10-31
      Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      --one-database option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by making get_current_db() a no-op function when one_database is set,
      and hence, under that condition current_db will not get altered.
      Note, however the value of current_db can change when we execute 'connect'
      command with a differnet database to reconnect to the server, in which case,
      the behavior of --one-database will be formulated using this new database.
     @ client/mysql.cc
        Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        Added an if statement at the beginnning of get_current_db() , which makes it
        a no-op function if one-database option is specified, and hence current_db
        remains unchanged.
        
        Changed the help message for one-database option to a more approprite message
        as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and a general test case to check one-database
        option related functionality.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and a general test case to check one-database
        option related functionality.
[9 Nov 2010 8:04] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123180

3554 Nirbhay Choubey	2010-11-09
            Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
            
            After dropping and recreating the database specified along with --one-database
            option at command line, mysql client keeps filtering the statements even after
            the execution of a 'USE' command on the same database.
            
            --one-database option enables the filtering of statements when the current
            database is not the one specified at the command line. However, when the same
            database is dropped and recreated the variable (current_db) that holds the
            inital database name gets altered. This bug exploits the fact that current_db
            initially gets set to null value (0) when a 'use db_name' follows the recreation
            of same database db_name (speficied at the command line) and hence skip_updates
            gets set to 1, which inturn triggers the further filtering of statements.
            
            Fixed by introducing a new variable 'opt_db', which persistently stores the
            database name specified at mysql client's command line and fixing the if
            statement that governed the filtering flag by adding a check on whether the
            database name in the USE command is similar to one specified at command line.
     @ client/mysql.cc
         Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
                
                During mysql client start-up, 'opt_db' gets initialized with the database
                name specified at the command line. This variable will keep holding 
                the database name throughout the session, unless 'connect' command is
                executed with some other database name. In that case, the behavior of 
                one-database will be formulated using this new database.
                
                The bug was resolved by checking if the database name used in the 'USE'
                command is same database that was entered at the command line (i.e. opt_db).
        
                Also, changed the help message for one-database option to a more approprite
                message as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
[17 Nov 2010 10:10] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124127

3554 Nirbhay Choubey	2010-11-17
            Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
            
            After dropping and recreating the database specified along with --one-database
            option at command line, mysql client keeps filtering the statements even after
            the execution of a 'USE' command on the same database.
            
            --one-database option enables the filtering of statements when the current
            database is not the one specified at the command line. However, when the same
            database is dropped and recreated the variable (current_db) that holds the
            inital database name gets altered. This bug exploits the fact that current_db
            initially gets set to null value (0) when a 'use db_name' follows the recreation
            of same database db_name (speficied at the command line) and hence skip_updates
            gets set to 1, which inturn triggers the further filtering of statements.
            
            Fixed by introducing a new variable 'opt_db', which persistently stores the
            database name specified at mysql client's command line and fixing the if
            statement that governed the filtering flag by adding a check on whether the
            database name in the USE command is similar to one specified at command line.
     @ client/mysql.cc
         Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
                
                During mysql client start-up, 'opt_db' gets initialized with the database
                name specified at the command line. This variable will keep holding 
                the database name throughout the session, unless 'connect' command is
                executed with some other database name. In that case, the behavior of 
                one-database will be formulated using this new database.
                
                The bug was resolved by checking if the database name used in the 'USE'
                command is same database that was entered at the command line (i.e. opt_db).
        
                Also, changed the help message for one-database option to a more approprite
                message as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
[22 Nov 2010 9:50] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124593

3554 Nirbhay Choubey	2010-11-22
      Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      '--one-database' option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by introducing a new variable 'opt_db', which persistently stores the
      database name specified at mysql client's command line and fixing the if
      statement that governed the filtering flag by adding a check on whether the
      database name in the USE command is similar to one specified at command line.
     @ client/mysql.cc
        Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        During mysql client start-up, 'opt_db' gets initialized with the database
        name specified at the command line. This variable will keep holding 
        the database name throughout the session, unless 'connect' command is
        executed with some other database name. In that case, the behavior of 
        one-database will be formulated using this new database.
                
        The bug was resolved by checking if the database name used in the 'USE'
        command is same database that was entered at the command line (i.e. opt_db).
        
        Also, changed the help message for one-database option to a more approprite
        message as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
[23 Nov 2010 3:24] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124701

3554 Nirbhay Choubey	2010-11-23
      Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      '--one-database' option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by introducing a new variable 'opt_db', which persistently stores the
      database name specified at mysql client's command line and fixing the if
      statement that governed the filtering flag by adding a check on whether the
      database name in the USE command is similar to one specified at command line.
     @ client/mysql.cc
        Bug#54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        During mysql client start-up, 'opt_db' gets initialized with the database
        name specified at the command line. This variable will keep holding 
        the database name throughout the session, unless 'connect' command is
        executed with some other database name. In that case, the behavior of 
        one-database will be formulated using this new database.
                
        The bug was resolved by checking if the database name used in the 'USE'
        command is same database that was entered at the command line (i.e. opt_db).
        
        Also, changed the help message for one-database option to a more approprite
        message as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and test cases to check other one-database
        option related behaviors.
[25 Nov 2010 9:48] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124998

3518 Nirbhay Choubey	2010-11-25
      Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      --one-database option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by making get_current_db() a no-op function when one_database is set,
      and hence, under that condition current_db will not get altered.
      Note, however the value of current_db can change when we execute 'connect'
      command with a differnet database to reconnect to the server, in which case,
      the behavior of --one-database will be formulated using this new database.
     @ client/mysql.cc
        Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        Added an if statement at the beginnning of get_current_db() , which makes it
        a no-op function if one-database option is specified, and hence current_db
        remains unchanged.
        
        Changed the help message for one-database option to a more appropriate message
        as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and some more test cases to
        check other one-database option related behaviors.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and some more test cases to
        check other one-database option related behaviors.
[26 Nov 2010 13:58] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125151

3521 Nirbhay Choubey	2010-11-26
      Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
      
      After dropping and recreating the database specified along with --one-database
      option at command line, mysql client keeps filtering the statements even after
      the execution of a 'USE' command on the same database.
      
      --one-database option enables the filtering of statements when the current
      database is not the one specified at the command line. However, when the same
      database is dropped and recreated the variable (current_db) that holds the
      inital database name gets altered. This bug exploits the fact that current_db
      initially gets set to null value (0) when a 'use db_name' follows the recreation
      of same database db_name (speficied at the command line) and hence skip_updates
      gets set to 1, which inturn triggers the further filtering of statements.
      
      Fixed by making get_current_db() a no-op function when one_database is set,
      and hence, under that condition current_db will not get altered.
      Note, however the value of current_db can change when we execute 'connect'
      command with a differnet database to reconnect to the server, in which case,
      the behavior of --one-database will be formulated using this new database.
     @ client/mysql.cc
        Bug #54899 : --one-database option cannot handle DROP/CREATE DATABASE commands
        
        Added an if statement at the beginnning of get_current_db() , which makes it
        a no-op function if one-database option is specified, and hence current_db
        remains unchanged.
        
        Changed the help message for one-database option to a more appropriate message
        as specified in mysql documentation.
     @ mysql-test/r/mysql.result
        Added a test case for bug#54899 and some more test cases to
        check other one-database option related behaviors.
     @ mysql-test/t/mysql.test
        Added a test case for bug#54899 and some more test cases to
        check other one-database option related behaviors.
[30 Nov 2010 13:48] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125501

3527 Nirbhay Choubey	2010-11-30
      Additional fix in the test for bug#54899.
     @ mysql-test/r/mysql.result
        Additional fix in the test for bug#54899.
     @ mysql-test/t/mysql.test
        Additional fix in the test for bug#54899.
[1 Dec 2010 6:56] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125597

3532 Nirbhay Choubey	2010-12-01
      Additional fix for bug#54899
      
      Fixing the testcase to use the database name
      as connected_db instead of 'test' database.
     @ mysql-test/r/mysql.result
        Additional fix in the test for bug#54899.
     @ mysql-test/t/mysql.test
        Additional fix in the test for bug#54899.
[5 Dec 2010 12:39] Bugs System 
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[17 Dec 2010 12:49] Bugs System 
Pushed into mysql-5.1 5.1.55 (revid:georgi.kodinov@oracle.com-20101217124435-9imm43geck5u55qw) (version source revid:mats.kindahl@oracle.com-20101201193331-1c07sjno2g7m46ix) (merge vers: 5.1.55) (pib:24)
[17 Dec 2010 12:52] Bugs System 
Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:nirbhay.choubey@sun.com-20101201065821-tzmx16rp33gr407v) (merge vers: 5.5.8) (pib:24)
[20 Dec 2010 20:12] Paul DuBois 
Noted in 5.1.55, 5.5.9 changelogs.

CREATE DATABASE and DROP DATABASE caused mysql --one-database to lose
track of the statement-filtering context.