MySQL client does not recognize SET STATEMENT .. FOR USE <db name> as a schema change

[Elena Stepanova]

MySQL client does not recognize the change of schema via SET STATEMENT ... FOR USE <db name>. On normal USE <db name> it does at least the following (example for USE mysql):

                    3 Query SELECT DATABASE()
                    3 Init DB mysql

and it changes the DB name in the prompt if it's configured to be displayed.
If it's run with auto-rehash, it additionally reads table names and field names.

For SET STATEMENT ... FOR USE ... it does none of those. The default schema is still changed, but the client doesn't know about it – the prompt shows the old name, name completion does not work etc.

$ client/mysql -uroot --protocol=tcp --port=3308 test
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 2
Server version: 5.6.21-69.0-debug Source distribution

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> prompt \d>
PROMPT set to '\d> '
test> show tables;
+----------------+
| Tables_in_test |
+----------------+
| count |
| t1 |
+----------------+
2 rows in set (0.00 sec)

test> set statement lock_wait_timeout=1 for use mysql;
Query OK, 0 rows affected (0.00 sec)

test> --attempt name completion for select * from t<tab> and then choose t1
test> select * from t
t1 t1.i tee test tmp
test> select * from t1;
ERROR 1146 (42S02): Table 'mysql.t1' doesn't exist
test> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)

[Valerii Kravchuk (valerii-kravchuk)]

This is easy to confirm:

[openxs@centos ~]$ mysql -uroot test
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 1
Server version: 5.6.21-69.0 Percona Server (GPL), Release 69.0, Revision 675

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show tables;
+------------------+
| Tables_in_test |
+------------------+
| count |
| node_group_state |
| t1 |
+------------------+
3 rows in set (0.00 sec)

mysql> prompt \d>
PROMPT set to '\d>'
test>set statement lock_wait_timeout=1 for use mysql;
Query OK, 0 rows affected (0.04 sec)

test>select * from t
t1 t1.i tee test
test>select * from t1;
ERROR 1146 (42S02): Table 'mysql.t1' doesn't exist
test>
...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1572