Details
Minor
Description
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):
9 Query SELECT DATABASE()
9 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.
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.1.1-MariaDB-wsrep-debug-log Source distribution, wsrep_25.10.r4123 Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | | t3 | | t4 | +----------------+ 4 rows in set (0.00 sec) MariaDB [test]> set statement lock_wait_timeout=1 for use mysql; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> -- attempt name completion with t<tab> and then choose t1 MariaDB [test]> select * from t t1 t1.i t2 t2.i t3 t3.j t4 t4.i tee test tmp MariaDB [test]> select * from t1; ERROR 1146 (42S02): Table 'mysql.t1' doesn't exist
commit e64f5d8f758bcc1a8856ba9fba01780533f80747
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Sun Oct 26 16:27:54 2014 +0100
Fixed test suite global variable saving
All the same in Percona server/client.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
That's exactly what the bug was about, that the client does not recognize the command. It wasn't a report about the server (yeah we still don't have the category "Client").
If you think it's not worth fixing, let it be so, but that's how I envision somebody's bad day:
MariaDB [test]> create database work; Query OK, 1 row affected (0.00 sec) MariaDB [test]> create table work.t1 (i int); Query OK, 0 rows affected (0.64 sec) MariaDB [test]> insert into work.t1 values (1),(2),(3); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> create database sandbox; Query OK, 1 row affected (0.00 sec) MariaDB [test]> create table sandbox.t1 (i int); Query OK, 0 rows affected (1.03 sec)
MariaDB [test]> use work; MariaDB [work]> # do work... MariaDB [work]> # do more work... MariaDB [work]> # do even more work... MariaDB [work]> use sandbox; MariaDB [sandbox]> # go play a bit MariaDB [sandbox]> set statement character_set_database=utf8 for use work; Query OK, 0 rows affected (0.00 sec) MariaDB [sandbox]> # go get coffee MariaDB [sandbox]> # .... MariaDB [sandbox]> # .... MariaDB [sandbox]> # .... MariaDB [sandbox]> # come back caffeinated MariaDB [sandbox]> # okay we're in the sandbox MariaDB [sandbox]> drop table t1; Query OK, 0 rows affected (0.38 sec) MariaDB [sandbox]> # lets get back to work MariaDB [sandbox]> use work MariaDB [work]> show tables; Empty set (0.00 sec) MariaDB [work]> # ooops
There's no easy way of fixing it. There was a similar issue with DROP DATABASE. It was fixed by extending the protocol — the server sets the SERVER_STATUS_DB_DROPPED flag when a database is dropped and the client can do something about it (mysql command line client re-reads the current database in this case).
A clean fix for this issue would be to extend the protocol again and add SERVER_STATUS_DB_CHANGED. But I'm not sure this issue is worth the troubles.
USE db is mysql command line client command, it is not sent to the server. As you have pointed out yourself, there is no "USE" statement in the general log, so "USE" was not sent to the server as an SQL statement. It was run as a protocol level command.
But if you put it into SET STATEMENT ... FOR then the client doesn't recognize it and it is sent as an SQL statement. Basically you change the database behind mysql command client back, it doesn't know the database was changed.