From: Sergey Vojtovich
Date: November 23 2010 6:20pm
Subject: Re: bzr commit into mysql-5.1-bugteam branch
 (nirbhay.choubey:3554) Bug#54899
List-Archive: http://lists.mysql.com/commits/124762
Message-Id: <20101123182022.GA20009@june>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi Nirbhay,

use case #8:
I have a dump provided by mysqldump tool:
--
  USE `test`;
  CREATE TABLE `table_in_test`(a INT);
  USE `non_existent_db`;
  CREATE TABLE `table_in_non_existent_db`(a INT);
--
Note: I simplified dump for better readability.

I have no `non_existent_db` database.
I have empty `test` database.
I do "mysql --one-database test".
I expect no `table_in_non_existent_db` in `test`.
Without this patch I get no `table_in_non_existent_db` in `test`.
With this patch I get `table_in_non_existent_db` in `test`.

Regards,
Sergey

On Tue, Nov 23, 2010 at 03:23:59AM +0000, Nirbhay Choubey wrote:
> #At file:///home/nirbhay/Project/mysql/repo/wl/mysql-5.1-bugteam-54899/ based on revid:guilhem@stripped
> 
>  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.
> 
>     modified:
>       client/mysql.cc
>       mysql-test/r/mysql.result
>       mysql-test/t/mysql.test
> === modified file 'client/mysql.cc'
> --- a/client/mysql.cc	2010-10-19 22:36:59 +0000
> +++ b/client/mysql.cc	2010-11-23 03:23:55 +0000
> @@ -160,8 +160,8 @@ static uint verbose=0,opt_silent=0,opt_m
>  static uint my_end_arg;
>  static char * opt_mysql_unix_port=0;
>  static int connect_flag=CLIENT_INTERACTIVE;
> -static char *current_host,*current_db,*current_user=0,*opt_password=0,
> -            *current_prompt=0, *delimiter_str= 0,
> +static char *current_host, *current_db, *opt_db, *current_user= 0,
> +            *opt_password= 0, *current_prompt= 0, *delimiter_str= 0,
>              *default_charset= (char*) MYSQL_DEFAULT_CHARSET_NAME;
>  static char *histfile;
>  static char *histfile_tmp;
> @@ -1147,7 +1147,7 @@ int main(int argc,char *argv[])
>    completion_hash_init(&ht, 128);
>    init_alloc_root(&hash_mem_root, 16384, 0);
>    bzero((char*) &mysql, sizeof(mysql));
> -  if (sql_connect(current_host,current_db,current_user,opt_password,
> +  if (sql_connect(current_host, opt_db, current_user, opt_password,
>  		  opt_silent))
>    {
>      quick=1;					// Avoid history
> @@ -1257,6 +1257,7 @@ sig_handler mysql_end(int sig)
>    my_free(opt_mysql_unix_port,MYF(MY_ALLOW_ZERO_PTR));
>    my_free(histfile,MYF(MY_ALLOW_ZERO_PTR));
>    my_free(histfile_tmp,MYF(MY_ALLOW_ZERO_PTR));
> +  my_free(opt_db, MYF(MY_ALLOW_ZERO_PTR));
>    my_free(current_db,MYF(MY_ALLOW_ZERO_PTR));
>    my_free(current_host,MYF(MY_ALLOW_ZERO_PTR));
>    my_free(current_user,MYF(MY_ALLOW_ZERO_PTR));
> @@ -1384,8 +1385,8 @@ static struct my_option my_long_options[
>     GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
>    {"debug-info", 'T', "Print some debug info at exit.", &debug_info_flag,
>     &debug_info_flag, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
> -  {"database", 'D', "Database to use.", &current_db,
> -   &current_db, 0, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
> +  {"database", 'D', "Database to use.", &opt_db,
> +   &opt_db, 0, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
>    {"default-character-set", OPT_DEFAULT_CHARSET,
>     "Set the default character set.", &default_charset,
>     &default_charset, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
> @@ -1449,8 +1450,8 @@ static struct my_option my_long_options[
>     &opt_sigint_ignore,  &opt_sigint_ignore, 0, GET_BOOL,
>     NO_ARG, 0, 0, 0, 0, 0, 0},
>    {"one-database", 'o',
> -   "Only update the default database. This is useful for skipping updates "
> -   "to other database in the update log.",
> +   "Ignore statements except those that occur while the default "
> +   "database is the one named at the command line.",
>     0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
>  #ifdef USE_POPEN
>    {"pager", OPT_PAGER,
> @@ -1840,8 +1841,10 @@ static int get_options(int argc, char **
>    if (argc == 1)
>    {
>      skip_updates= 0;
> -    my_free(current_db, MYF(MY_ALLOW_ZERO_PTR));
> -    current_db= my_strdup(*argv, MYF(MY_WME));
> +
> +    /* Store initial database name specified at command line in opt_db. */
> +    my_free(opt_db, MYF(MY_ALLOW_ZERO_PTR));
> +    opt_db= my_strdup(*argv, MYF(MY_WME));
>    }
>    if (tty_password)
>      opt_password= get_tty_password(NullS);
> @@ -3973,8 +3976,9 @@ com_connect(String *buffer, char *line)
>      tmp= get_arg(buff, 0);
>      if (tmp && *tmp)
>      {
> -      my_free(current_db, MYF(MY_ALLOW_ZERO_PTR));
> -      current_db= my_strdup(tmp, MYF(MY_WME));
> +      my_free(opt_db, MYF(MY_ALLOW_ZERO_PTR));
> +      opt_db= my_strdup(tmp, MYF(MY_WME));
> +
>        tmp= get_arg(buff, 1);
>        if (tmp)
>        {
> @@ -3991,7 +3995,7 @@ com_connect(String *buffer, char *line)
>    }
>    else
>      opt_rehash= 0;
> -  error=sql_connect(current_host,current_db,current_user,opt_password,0);
> +  error= sql_connect(current_host, opt_db, current_user, opt_password, 0);
>    opt_rehash= save_rehash;
>  
>    if (connected)
> @@ -4093,7 +4097,7 @@ static int
>  com_use(String *buffer __attribute__((unused)), char *line)
>  {
>    char *tmp, buff[FN_REFLEN + 1];
> -  int select_db;
> +  int select_db, skip_updates_old;
>  
>    bzero(buff, sizeof(buff));
>    strmake(buff, line, sizeof(buff) - 1);
> @@ -4109,16 +4113,20 @@ com_use(String *buffer __attribute__((un
>      (latter one not yet available by the time the comment was written)
>    */
>    get_current_db();
> +  skip_updates_old= skip_updates;
> +  skip_updates= 0;
> +
> +  /*
> +    In case of one-database, start filtering statements if opt_db (i.e.
> +    database specified at command line) is similar to the database name
> +    provided in the 'USE' command.
> +  */
> +  if (one_database && (!opt_db || cmp_database(charset_info, opt_db, tmp)))
> +    skip_updates= 1;
>  
> -  if (!current_db || cmp_database(charset_info, current_db,tmp))
> +  if (!current_db || cmp_database(charset_info, current_db, tmp))
>    {
> -    if (one_database)
> -    {
> -      skip_updates= 1;
> -      select_db= 0;    // don't do mysql_select_db()
> -    }
> -    else
> -      select_db= 2;    // do mysql_select_db() and build_completion_hash()
> +    select_db= 2;    // do mysql_select_db() and build_completion_hash()
>    }
>    else
>    {
> @@ -4129,7 +4137,6 @@ com_use(String *buffer __attribute__((un
>        change since last USE (see bug#10979).
>        For performance purposes, we'll skip rebuilding of completion hash.
>      */
> -    skip_updates= 0;
>      select_db= 1;      // do only mysql_select_db(), without completion
>    }
>  
> @@ -4144,7 +4151,20 @@ com_use(String *buffer __attribute__((un
>      if (mysql_select_db(&mysql,tmp))
>      {
>        if (mysql_errno(&mysql) != CR_SERVER_GONE_ERROR)
> -        return put_error(&mysql);
> +      {
> +        if(one_database)
> +        {
> +          /* Readjust skip_updates and return gently. */
> +          if (skip_updates_old)
> +            skip_updates= 1;
> +          else
> +            skip_updates= 0;
> +
> +          put_error(&mysql);
> +          return 0;
> +        }
> +      return put_error(&mysql);
> +      }
>  
>        if (reconnect())
>          return opt_reconnect ? -1 : 1;                      // Fatal error
> @@ -4303,6 +4323,10 @@ sql_real_connect(char *host,char *databa
>      return -1;					// Retryable
>    }
>    connected=1;
> +
> +  /* Time to change current_db. */
> +  get_current_db();
> +
>  #ifndef EMBEDDED_LIBRARY
>    mysql.reconnect= debug_info_flag; // We want to know if this happens
>  #else
> 
> === modified file 'mysql-test/r/mysql.result'
> --- a/mysql-test/r/mysql.result	2009-12-17 20:06:36 +0000
> +++ b/mysql-test/r/mysql.result	2010-11-23 03:23:55 +0000
> @@ -235,4 +235,84 @@ Bug #47147: mysql client option --skip-c
>  *************************** 1. row ***************************
>  1
>  
> +#
> +# Bug #54899: --one-database option cannot handle DROP/CREATE DATABASE 
> +#             commands.
> +#
> +CREATE DATABASE connected_db;
> +USE connected_db;
> +SHOW TABLES;
> +Tables_in_connected_db
> +t1
> +DROP DATABASE connected_db;
> +
> +#
> +# Testing --one-database option
> +#
> +CREATE DATABASE connected_db;
> +SHOW TABLES IN connected_db;
> +Tables_in_connected_db
> +t1
> +SHOW TABLES IN test;
> +Tables_in_test
> +t1
> +USE test;
> +DROP TABLE t1;
> +DROP DATABASE connected_db;
> +
> +CREATE DATABASE connected_db;
> +SHOW TABLES IN connected_db;
> +Tables_in_connected_db
> +SHOW TABLES IN test1;
> +Tables_in_test1
> +DROP DATABASE test1;
> +DROP DATABASE connected_db;
> +
> +#
> +# Checking --one-database option followed by the execution of 
> +# connect command.
> +#
> +CREATE DATABASE connected_db;
> +SHOW TABLES IN connected_db;
> +Tables_in_connected_db
> +t1
> +t2
> +SHOW TABLES IN test;
> +Tables_in_test
> +t1
> +t2
> +DROP TABLE test.t1;
> +DROP TABLE test.t2;
> +DROP DATABASE connected_db;
> +
> +#
> +# Checking --one-database option with no database specified
> +# at command-line.
> +#
> +CREATE DATABASE connected_db;
> +SHOW TABLES IN connected_db;
> +Tables_in_connected_db
> +SHOW TABLES IN test;
> +Tables_in_test
> +DROP DATABASE connected_db;
> +#
> +# Checking --one-database option with non_existent_db 
> +# specified with USE command
> +#
> +SHOW TABLES IN test;
> +Tables_in_test
> +t1
> +t2
> +DROP DATABASE test;
> +CREATE DATABASE test;
> +SHOW TABLES IN test;
> +Tables_in_test
> +t3
> +t4
> +DROP DATABASE test;
> +CREATE DATABASE test;
> +CREATE DATABASE non_existent_db;
> +SHOW TABLES IN test;
> +Tables_in_test
> +
>  End of tests
> 
> === modified file 'mysql-test/t/mysql.test'
> --- a/mysql-test/t/mysql.test	2009-12-17 20:06:36 +0000
> +++ b/mysql-test/t/mysql.test	2010-11-23 03:23:55 +0000
> @@ -413,4 +413,163 @@ drop table t1;
>  --exec $MYSQL --skip-column-names --vertical test -e "select 1 as a"
>  
>  --echo
> +
> +--echo #
> +--echo # Bug #54899: --one-database option cannot handle DROP/CREATE DATABASE 
> +--echo #             commands.
> +--echo #
> +--write_file $MYSQLTEST_VARDIR/tmp/bug54899.sql
> +DROP DATABASE connected_db;
> +CREATE DATABASE connected_db;
> +USE connected_db;
> +CREATE TABLE t1(a INT);
> +EOF
> +
> +CREATE DATABASE connected_db;
> +--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/bug54899.sql
> +USE connected_db;
> +SHOW TABLES;
> +DROP DATABASE connected_db;
> +--remove_file $MYSQLTEST_VARDIR/tmp/bug54899.sql
> +
> +--echo
> +
> +--echo #
> +--echo # Testing --one-database option
> +--echo #
> +--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +CREATE TABLE t1 (i INT);
> +CREATE TABLE test.t1 (i INT);
> +USE test;
> +# Following statements should be filtered.
> +CREATE TABLE connected_db.t2 (i INT);
> +CREATE TABLE t2 (i INT);
> +EOF
> +
> +CREATE DATABASE connected_db;
> +--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/one_db.sql
> +SHOW TABLES IN connected_db;
> +SHOW TABLES IN test;
> +USE test;
> +DROP TABLE t1;
> +DROP DATABASE connected_db;
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +--echo
> +--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +CREATE DATABASE test1;
> +USE test1;
> +USE test1;
> +# Following statements should be filtered.
> +CREATE TABLE connected_db.t1 (i INT);
> +EOF
> +
> +CREATE DATABASE connected_db;
> +--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/one_db.sql
> +SHOW TABLES IN connected_db;
> +SHOW TABLES IN test1;
> +DROP DATABASE test1;
> +DROP DATABASE connected_db;
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +
> +--echo
> +
> +--echo #
> +--echo # Checking --one-database option followed by the execution of 
> +--echo # connect command.
> +--echo #
> +--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +CREATE TABLE t1 (i INT);
> +CREATE TABLE test.t1 (i INT);
> +CONNECT test;
> +CREATE TABLE connected_db.t2 (i INT);
> +CREATE TABLE t2 (i INT);
> +USE connected_db;
> +# Following statements should be filtered.
> +CREATE TABLE connected_db.t3 (i INT);
> +CREATE TABLE t3 (i INT);
> +EOF
> +
> +CREATE DATABASE connected_db;
> +--exec $MYSQL --one-database connected_db < $MYSQLTEST_VARDIR/tmp/one_db.sql
> +SHOW TABLES IN connected_db;
> +SHOW TABLES IN test;
> +DROP TABLE test.t1;
> +DROP TABLE test.t2;
> +DROP DATABASE connected_db;
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +
> +--echo
> +
> +--echo #
> +--echo # Checking --one-database option with no database specified
> +--echo # at command-line.
> +--echo #
> +--write_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +# Following statements should be filtered.
> +CREATE TABLE t1 (i INT);
> +CREATE TABLE test.t1 (i INT);
> +USE connected_db;
> +CREATE TABLE connected_db.t2 (i INT);
> +CREATE TABLE t2 (i INT);
> +EOF
> +
> +CREATE DATABASE connected_db;
> +--exec $MYSQL --one-database < $MYSQLTEST_VARDIR/tmp/one_db.sql
> +SHOW TABLES IN connected_db;
> +SHOW TABLES IN test;
> +DROP DATABASE connected_db;
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db.sql
> +
> +--echo #
> +--echo # Checking --one-database option with non_existent_db 
> +--echo # specified with USE command
> +--echo #
> +
> +# CASE 1 : When 'test' database exists.
> +--write_file $MYSQLTEST_VARDIR/tmp/one_db_1.sql
> +CREATE TABLE t1(i INT);
> +USE non_existent_db;
> +# Following statement should not be filtered out.
> +CREATE TABLE t2(i INT);
> +EOF
> +
> +# CASE 2 : When 'test' database does not exist.
> +--write_file $MYSQLTEST_VARDIR/tmp/one_db_2.sql
> +DROP DATABASE test;
> +CREATE DATABASE test;
> +# Following statements should not be filtered out.
> +USE non_existent_db;
> +CREATE TABLE test.t3(i INT);
> +USE test;
> +CREATE TABLE t4(i INT);
> +EOF
> +
> +# CASE 3 : When 'non_existent_db' is dropped.
> +--write_file $MYSQLTEST_VARDIR/tmp/one_db_3.sql
> +# Following statements should not be filtered out.
> +DROP DATABASE `non_existent_db`;
> +USE `test`;
> +# Following statements should be filtered out.
> +CREATE TABLE `table_in_test`(a INT);
> +USE `non_existent_db`;
> +CREATE TABLE `table_in_non_existent_db`(a INT);
> +EOF
> +
> +--exec $MYSQL --one-database test < $MYSQLTEST_VARDIR/tmp/one_db_1.sql
> +SHOW TABLES IN test;
> +DROP DATABASE test;
> +CREATE DATABASE test;
> +--exec $MYSQL --one-database test < $MYSQLTEST_VARDIR/tmp/one_db_2.sql
> +SHOW TABLES IN test;
> +DROP DATABASE test;
> +CREATE DATABASE test;
> +CREATE DATABASE non_existent_db;
> +--exec $MYSQL --one-database non_existent_db < $MYSQLTEST_VARDIR/tmp/one_db_3.sql
> +SHOW TABLES IN test;
> +
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db_1.sql
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db_2.sql
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db_3.sql
> +
> +--echo
>  --echo End of tests
> 


> 
> -- 
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe:    http://lists.mysql.com/commits?unsub=svoj@stripped


-- 
Sergey Vojtovich <svoj@stripped>
MySQL AB, Software Engineer
Izhevsk, Russia, www.mysql.com