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.", ¤t_db,
> - ¤t_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=1
--
Sergey Vojtovich <svoj@stripped>
MySQL AB, Software Engineer
Izhevsk, Russia, www.mysql.com
