List:Commits« Previous MessageNext Message »
From:Sergey Vojtovich Date:November 22 2010 6:45pm
Subject:Re: bzr commit into mysql-5.1-bugteam branch
(nirbhay.choubey:3554) Bug#54899
View as plain text  
Hi Nirbhay,

please note that you're always free to dispute my use cases.
As well as note that I still didn't manage to draw whole
picture of new approach in my mind. Thus use cases, but not
code reviews.

Use case #7:
I have SQL script:
--
  DROP DATABASE `non_existent_db`;
  USE `test`;
  CREATE TABLE `table_in_test`(a INT);
  USE `non_existent_db`;
  CREATE TABLE `table_in_non_existent_db`(a INT);
--
I have empty `test` database.
I have empty `non_existent_db` database.
I do "mysql --one-database non_existent_db".
I expect no `non_existent_db` and empty `test`.
Without this patch I get no `non_existent_db` and empty `test`.
With this path I get no `non_existent_db` and `table_in_non_existent_db` in `test`.

Though this use case doesn't seem to be reasonable, we may fall into this
situation in certain cases when loading dump provided by mysqldump. E.g.
DROP DATABASE succeeded, but following CREATE DATABASE failed. Or DROP DATABASE
was executed in parallel thread.

Regards,
Sergey

On Mon, Nov 22, 2010 at 09:39:21AM +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-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.
> 
>     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-22 09:39:13 +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,18 @@ 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 == 0)
> +            skip_updates= 0;
> +
> +          put_error(&mysql);
> +          return 0;
> +        }
> +      return put_error(&mysql);
> +      }
>  
>        if (reconnect())
>          return opt_reconnect ? -1 : 1;                      // Fatal error
> @@ -4303,6 +4321,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-22 09:39:13 +0000
> @@ -235,4 +235,81 @@ 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;
> +
>  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-22 09:39:13 +0000
> @@ -413,4 +413,147 @@ 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
> +
> +--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;
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db_1.sql
> +--remove_file $MYSQLTEST_VARDIR/tmp/one_db_2.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=1


-- 
Sergey Vojtovich <svoj@stripped>
MySQL AB, Software Engineer
Izhevsk, Russia, www.mysql.com
Thread
bzr commit into mysql-5.1-bugteam branch (nirbhay.choubey:3554)Bug#54899Nirbhay Choubey22 Nov
  • Re: bzr commit into mysql-5.1-bugteam branch(nirbhay.choubey:3554) Bug#54899Sergey Vojtovich22 Nov
    • Re: bzr commit into mysql-5.1-bugteam branch (nirbhay.choubey:3554)Bug#54899Nirbhay Choubey23 Nov