Use <database> causing multiple issues if DB does not exist

[bitonp]

If I connect through the mysql command line to proxsql, and then issue:
mysql>use tst;
and db tst does not exist I get the following errors:

1. In the mysql cli (correctly probably):
   mysql> use tst;
   Database changed, 2 warnings
2. In the output from proxysql:

    2017-01-12 14:54:51 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 14:54:51 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 14:54:51 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 14:54:51 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 14:54:51 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 14:54:51 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.

This is due to proxysql doing a 'show tables' at the sane time, which is probably superfluous, and will slow any system down.

Secondly, and 'show databases' command also fails from this point, until a valid 'use DBname' is issued.
For example:

mysql>use tst;
Database changed, 2 warnings

proxysql output (as before):
2017-01-12 15:11:53 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:11:53 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:11:53 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:11:53 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:11:53 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:11:53 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.

2. mysql>show tables from mysql;
ERROR 1049 (#4200): Unknown database 'tst'

proxysql Output:
2017-01-12 15:12:13 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:12:13 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:12:13 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:12:13 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:12:13 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.
2017-01-12 15:12:13 mysql_connection.cpp:599:handler(): [ERROR] Failed to mysql_real_connect() on 127.0.0.1:3306 , FD (Conn:35 , MyDS:35) , 1049: Unknown database 'tst'.

If you then issue a correct 'use mysql;' , then all reverts to normal.

Its unlikely he mods I have rrinning on my system are the cause of this... I have done very little.. but am wiling to be proved incorrect :-)

[renecannao]

I believe the current behavior is indeed correct.

First, let see what happens what happens when MySQL CLI execute USE dbname:

• it sends a COM_INIT_DB command to change the database
• it sends a query show tables

The superfluous show tables is not executed by proxysql: it is a request coming from the client (mysql cli) that proxysql is trying to forward.
To suppress the show tables command, mysql cli should be executed using -A to disable auto-refresh

When a client issue a USE command or COM_INIT_DB proxysql does NOT forward the request to any backend: it only internally track which one is the desired default schema for that specific client.
Why does that? The default schema becomes relevant only when running a query and not before, and routing depends from it.
Assume you have two servers, with one schema each:

• schemaA on serverA
• schemaB on serverB
  You would configured proxysql to send all queries on schemaA to serverA, while all queries on schemaB to serverB : this is a classic sharding.
  It a client has schemaA as default schema and is sending queries to serverA, and now it issues USE schemaB, this request cannot be executed on serverA as schemaB doesn't exist there.
  In this case, ProxySQL will only reply OK to the client, and will wait for the client to sends a query. When the client sends a query, ProxySQL will decide what to do with it: as the default schema is now schemaB, it will send the request to serverB.

So the behavior you are seeing is expected: USE database will always succeed, but the queries will fail until a valid schema is selected.

[bitonp]

Oh.. flip me! I keep forgetting about -A .. sorry. So point (1) agreed.

The second point is going to be an issue with those using mysql cli or any front end with a command window . such as 'phpMyAdmin' (sorry... but its used a lot.. but never by me... it is evil personified ;-) )

mysql> use tst; // typo.. fails correctly
mysql>select distinct table_schema from information_schema.tables; // fails due to issue above.

Those are a likely result, to try and correct the typo. However, the second one will continue to fail until the correct DB is issued in a 'use' statement.
In effect... all queries fail at this point. So even a typo in code will cause failures.

Examples of failing queries:

select 'fred' from dual;
select * from mysql.user;
select count(*) from test.test;

Any actually valid query will fail, if the 'use' is incorrect.
While maybe syntactically 'correct'... it will result in many support calls to DBAs I suspect

[renecannao]

About the second point, I think is not worse than a typo in the connect string, specifying a wrong database name.
If the client tries to connect to the wrong schema, the error will arise during connect.
I think that an error like Unknown database 'tst' should be quite clear (btw, this is the error coming from mysql server).
With ProxySQL, the error will arise during the execution of the query: ProxySQL cannot know to which server the client wants to communicate from just USE command.
Thinking in term of sharding (you can have thousands of database server behind a single proxy), makes clear that proxysql cannot know ahead of time which is a valid schema.

[bitonp]

Agreed with Rene that this is probably the correct method for proxysql. So all is Ok

[renecannao]

Reopening it and setting "documentation" label.
This behavior needs to be documented.

[bitonp]

Ahhh... great idea...didn't think of that ...thanks Peter Colclough T:01963 220217 e: biton@compuserve.com Skype: peter.colclough

…

On 16 Jan 2017, at 12:56, René Cannaò ***@***.***> wrote: Reopening it and setting "documentation" label. This behavior needs to be documented. — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub, or mute the thread.

[maxozerov]

also if "fast_forward=1" and db does not exist I get the following errors - ProxySQL crashed

output from proxysql:

2017-03-01 17:33:52 mysql_connection.cpp:560:handler(): [ERROR] Failed to mysql_real_connect() on 10.0.2.15:3306 , FD (Conn:41 , MyDS:41) , 1049: Unknown database 'test_test'.
proxysql: MySQL_Protocol.cpp:572: bool MySQL_Protocol::generate_pkt_ERR(bool, void**, unsigned int*, uint8_t, uint16_t, char*, char*): Assertion `0' failed.
Error: signal 6:
proxysql(_Z13crash_handleri+0x1a)[0x439a1a]
/lib/x86_64-linux-gnu/libc.so.6(+0x350e0)[0x7f3d1b7f50e0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7f3d1b7f5067]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f3d1b7f6448]
/lib/x86_64-linux-gnu/libc.so.6(+0x2e266)[0x7f3d1b7ee266]
/lib/x86_64-linux-gnu/libc.so.6(+0x2e312)[0x7f3d1b7ee312]
proxysql(_ZN14MySQL_Protocol16generate_pkt_ERREbPPvPjhtPcS3_+0x180)[0x468e50]
proxysql(_ZN13MySQL_Session40handler_again___status_CONNECTING_SERVEREPi+0x42b)[0x4635db]
proxysql(_ZN13MySQL_Session7handlerEv+0x12e6)[0x466016]
proxysql(_ZN12MySQL_Thread20process_all_sessionsEv+0x1d0)[0x4546b0]
proxysql(_ZN12MySQL_Thread3runEv+0x1653)[0x45c743]
proxysql(_Z24mysql_worker_thread_funcPv+0x54)[0x4382c4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8064)[0x7f3d1d019064]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f3d1b8a862d]
2017-03-01 17:33:53 main.cpp:642:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
2017-03-01 17:33:53 [INFO] Angel process is waiting 1 seconds before starting a new ProxySQL process
2017-03-01 17:33:54 [INFO] Angel process started ProxySQL process 2721

mysql cli:

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: test_test

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.2.15' (111)
ERROR: 
Can't connect to the server

[renecannao]

Nice way to quickly promote a documentation issue into a crashing bug!
Thank you for the report

[renecannao]

About fast_forward=1 : this seems related to #733 , now fixed.