Open bitonp opened 7 years ago
I believe the current behavior is indeed correct.
First, let see what happens what happens when MySQL CLI execute USE dbname
:
COM_INIT_DB
command to change the databaseshow 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:
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.
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
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.
Agreed with Rene that this is probably the correct method for proxysql. So all is Ok
Reopening it and setting "documentation" label. This behavior needs to be documented.
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ò notifications@github.com 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.
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
Nice way to quickly promote a documentation issue into a crashing bug! Thank you for the report
About fast_forward=1 : this seems related to #733 , now fixed.
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:
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:
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 :-)