'USE' doesn't return an error if a keyspace doesn't exist

[guidoiaquinti]

Right now if we execute a USE against a keyspace/db that doesn't exist, we don't return any error to the client (we only do that after failing a query):

mysql> use this_db_doesnt_exist;
Database changed

vtgate only logs an error: E0814 13:47:41.481496 24441 resilient_srv_topo_server.go:280] WatchSrvKeyspace failed for test/this_db_doesnt_exist: node doesn't exist

mysql> show tables;
ERROR 1105 (HY000): vtgate: http://vitess:15001/: keyspace this_db_doesnt_exist fetch error: node doesn't exist

My proposal is to align Vitess to what MySQL already offers:

mysql> use this_db_doesnt_exist;
ERROR 1049 (42000): Unknown database 'this_db_doesnt_exist'

[sougou]

This sounds good.

[guidoiaquinti]

Adding a minor note here to support as well system schemas:

mysql> use information_schema;
Database changed

mysql> SHOW TABLES FROM `information_schema`;
ERROR 1105 (HY000): vtgate: http://vitess:15001/: keyspace information_schema fetch error: node doesn't exist

the last query works fine if use information_schema; is skipped

[sougou]

We could add support for use <sys-schema> construct, but there are not many use cases right now. I believe most use cases want to use <keyspace> and select ... from information_schema.<table>.

It will become very powerful if vitess could show a consolidated view. Then this will become a powerful feature. It's something we can keep in mind.

[morgo]

I think this is important to fix, because in general Vitess is quite strict about how it handles edge-cases. This makes it easier to validate that it is working correctly (vs. MySQL's traditional behavior of parse but ignore etc.)

[morgo]

There is another use-case why this is important. In MySQL (depending on platform) the schema name may be case-insensitive. So when you move to vitess, you may get this error.. which won't make sense to you. The semantics are much clearer if the initial selecting of the database fails.

[llibdude]

I had commented on the wrong issue and have deleted the message. Please disregard :)

[saifalharthi]

I was looking debugging this issue and looking at query logs , this query does not invoke handleUse in the executor. However, I think mysql client sends a com_init_db and at this time we don't process it.

[deepthi]

@saifalharthi can you document the full list of commands generated by use?

[saifalharthi]

These are the commands I logged

select @@version_comment limit 1
SELECT DATABASE()
show databases
show tables

[harshit-gangal]

Can this be closed?

[deepthi]

Can this be closed?

do you know which PR fixed it? Then we can refer to it here and close it.

[harshit-gangal]

If someone issues use <target>. It exists.
It is not implemented for ComInitDB call from mysql driver.

[sougou]

I don't think it's fixed. We might even have a regression of some sort. From the local example:

mysql> show databases;
+-----------+
| Databases |
+-----------+
| commerce  |
+-----------+
1 row in set (0.00 sec)

mysql> use aaa
Database changed
mysql> select * from product;
Empty set (0.00 sec)

mysql> 

[deepthi]

That is a regression, it used to error out on the select, now it silently uses a keyspace.