[MDEV-6949] MySQL client does not recognize SET STATEMENT .. FOR USE <db name> as a schema change Created: 2014-10-27  Updated: 2014-11-08  Resolved: 2014-11-08

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-5231 Per query variables from Percona Serv... Closed
relates to MDEV-6923 Testing for SET STATEMENT .. FOR (MDE... Closed

 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

Current bb-10.1-set-statement tree

 
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.



 Comments   
Comment by Sergei Golubchik [ 2014-11-07 ]

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.

Comment by Elena Stepanova [ 2014-11-07 ]

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:

Once upon a time...

 
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)

Bad command day

 
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

Comment by Sergei Golubchik [ 2014-11-08 ]

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.

Generated at Thu Feb 08 07:15:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.