New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

`use db` cause SELECT DATABASE() fail when no schema given #988

Closed
seanlook opened this Issue Apr 13, 2017 · 4 comments

Comments

Projects
None yet
2 participants
@seanlook

seanlook commented Apr 13, 2017

I test proxysql sharding with schemaname to route to different hostgroup. Whenever I send use db[012...] command to switch databases, a SELECT DATABASE() command is sent to current database. Then this come out:

  1. Connect to proxysql in client side with no database given.
  2. use db0 to specify database.
    It take about 10s to response because no rules match and I have no default host group 0 .

In my circumstances, I don't want to configure defualt HG 0. Every queries should be match, default HG 0 may result in the wrong data.

My Test:

-- proxy server (rules & hits)
(admin@127.0.0.1:7032) [(none)]> select rule_id,active,hits,  match_pattern, schemaname,negate_match_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id limit 10;
+---------+--------+------+---------------+------------+----------------------+-----------+-------+--------+---------+
| rule_id | active | hits | match_pattern | schemaname | negate_match_pattern | cache_ttl | apply | flagIN | flagOUT |
+---------+--------+------+---------------+------------+----------------------+-----------+-------+--------+---------+
| 51      | 1      | 1    | ^select       | NULL       | 0                    | NULL      | 0     | 0      | 20      |
| 52      | 1      | 2    | ^select       | NULL       | 1                    | NULL      | 0     | 0      | 21      |
| 53      | 1      | 0    | NULL          | db0        | 0                    | NULL      | 1     | 20     | 20      |
| 54      | 1      | 2    | NULL          | db0        | 0                    | NULL      | 1     | 21     | 21      |
| 55      | 1      | 0    | NULL          | db1        | 0                    | NULL      | 1     | 20     | 20      |
| 56      | 1      | 0    | NULL          | db1        | 0                    | NULL      | 1     | 21     | 21      |
| 57      | 1      | 0    | NULL          | db2        | 0                    | NULL      | 1     | 20     | 20      |
| 58      | 1      | 0    | NULL          | db2        | 0                    | NULL      | 1     | 21     | 21      |
| 59      | 1      | 0    | NULL          | db3        | 0                    | NULL      | 1     | 20     | 20      |
| 60      | 1      | 0    | NULL          | db3        | 0                    | NULL      | 1     | 21     | 21      |
+---------+--------+------+---------------+------------+----------------------+-----------+-------+--------+---------+

-- mysql client (no --database )
# mysql -uxxxx -p -hx.x.x.x -P6033
(xxxx@x.x.x.x:6033) [(none)]> 
(xxxx@x.x.x.x:6033) [(none)]> use db0;
Database changed   -- take long to response

-- proxy server
(admin@127.0.0.1:6032) [(none)]> select hostgroup,schemaname,digest_text,count_star from stats_mysql_query_digest;
+-----------+--------------------+----------------------------------+------------+
| hostgroup | schemaname         | digest_text                      | count_star |
+-----------+--------------------+----------------------------------+------------+
| 0         | information_schema | select USER()                    | 1          |
| 0         | information_schema | select @@version_comment limit ? | 1          |
+-----------+--------------------+----------------------------------+------------+

-- mysql client again
(xxx@x.x.x.x:6033) [db0]>use db1  --  very quick

-- proxysql server
(admin@127.0.0.1:7032) [(none)]> select hostgroup,schemaname,digest_text,count_star from stats_mysql_query_digest;                                                                                       
+-----------+--------------------+----------------------------------+------------+
| hostgroup | schemaname         | digest_text                      | count_star |
+-----------+--------------------+----------------------------------+------------+
| 100       | db0                | show tables                      | 1          |
| 100       | db0                | show databases                   | 1          |
| 0         | information_schema | SELECT DATABASE()                | 1          |
| 0         | information_schema | select USER()                    | 1          |
| 0         | information_schema | select @@version_comment limit ? | 1          |
+-----------+--------------------+----------------------------------+------------+

The first time client login with no schema given, it asign HG 0 information_schema. Then use db0 send SELECT DATABASE(), apparently no rule match. After it fail, use db0 match rule 52

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Apr 13, 2017

Contributor

Hi @seanlook .
I suspect it is just a mis-configuration.
Can you please let me know:

  • which exact version of ProxySQL are you using
  • if there is warning/error logged in error log (/var/lib/proxysql/proxysql.log)
  • the output of SELECT hostgroup_id, 'XXX'||SUBSTR(hostname,-4) hostname FROM runtime_mysql_servers
  • the output of SELECT * FROM runtime_mysql_query_rules ORDER BY rule_id LIMIT 50
Contributor

renecannao commented Apr 13, 2017

Hi @seanlook .
I suspect it is just a mis-configuration.
Can you please let me know:

  • which exact version of ProxySQL are you using
  • if there is warning/error logged in error log (/var/lib/proxysql/proxysql.log)
  • the output of SELECT hostgroup_id, 'XXX'||SUBSTR(hostname,-4) hostname FROM runtime_mysql_servers
  • the output of SELECT * FROM runtime_mysql_query_rules ORDER BY rule_id LIMIT 50
@seanlook

This comment has been minimized.

Show comment
Hide comment
@seanlook

seanlook Apr 13, 2017

  • Version: 1.3.5
  • No warning or error log in log file. But I see this in client console
(xxxx@proxysql-server:6033) [(none)]> SELECT DATABASE();
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 0 after 10000ms
(ecdba@10.0.201.36:7033) [(none)]> use db0;   --  seems take long, actually runing SELECT DATABASE() I guess
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(ecdba@10.0.201.36:7033) [db0]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db0        |
+------------+
  • runtime_mysql_servers. As you see, I do not configure HG 0.
    If I insert HG 0, use db0 above is quick. (The very first time use db[])
+--------------+----------+
| hostgroup_id | hostname |
+--------------+----------+
| 100          | XXX.195  |
| 101          | XXX.196  |
| 102          | XXX.199  |
| 103          | XXX.112  |
| 1000         | XXX1.94  |
| 1001         | XXX1.94  |
| 1002         | XXX1.94  |
| 1003         | XXX1.94  |
+--------------+----------+
  • runtime_mysql_query_rules.
SELECT * FROM runtime_mysql_query_rules ORDER BY rule_id;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | mirror_flagOUT | mirror_hostgroup | error_msg | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| 51      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^select       | 0                    | 20      | NULL            | NULL                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 0     | NULL    |
| 52      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^select       | 1                    | 21      | NULL            | NULL                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 0     | NULL    |
| 53      | 1      | NULL     | db0        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 54      | 1      | NULL     | db0        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 55      | 1      | NULL     | db1        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 56      | 1      | NULL     | db1        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 57      | 1      | NULL     | db2        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 58      | 1      | NULL     | db2        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 59      | 1      | NULL     | db3        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 60      | 1      | NULL     | db3        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 61      | 1      | NULL     | db4        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 62      | 1      | NULL     | db4        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 63      | 1      | NULL     | db5        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 64      | 1      | NULL     | db5        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 65      | 1      | NULL     | db6        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 66      | 1      | NULL     | db6        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 67      | 1      | NULL     | db7        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 68      | 1      | NULL     | db7        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 69      | 1      | NULL     | db8        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 70      | 1      | NULL     | db8        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 71      | 1      | NULL     | db9        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 72      | 1      | NULL     | db9        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 73      | 1      | NULL     | db10       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 74      | 1      | NULL     | db10       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 75      | 1      | NULL     | db11       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 76      | 1      | NULL     | db11       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 77      | 1      | NULL     | db12       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 78      | 1      | NULL     | db12       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 79      | 1      | NULL     | db13       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 80      | 1      | NULL     | db13       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 81      | 1      | NULL     | db14       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 82      | 1      | NULL     | db14       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 83      | 1      | NULL     | db15       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 84      | 1      | NULL     | db15       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+

seanlook commented Apr 13, 2017

  • Version: 1.3.5
  • No warning or error log in log file. But I see this in client console
(xxxx@proxysql-server:6033) [(none)]> SELECT DATABASE();
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 0 after 10000ms
(ecdba@10.0.201.36:7033) [(none)]> use db0;   --  seems take long, actually runing SELECT DATABASE() I guess
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(ecdba@10.0.201.36:7033) [db0]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db0        |
+------------+
  • runtime_mysql_servers. As you see, I do not configure HG 0.
    If I insert HG 0, use db0 above is quick. (The very first time use db[])
+--------------+----------+
| hostgroup_id | hostname |
+--------------+----------+
| 100          | XXX.195  |
| 101          | XXX.196  |
| 102          | XXX.199  |
| 103          | XXX.112  |
| 1000         | XXX1.94  |
| 1001         | XXX1.94  |
| 1002         | XXX1.94  |
| 1003         | XXX1.94  |
+--------------+----------+
  • runtime_mysql_query_rules.
SELECT * FROM runtime_mysql_query_rules ORDER BY rule_id;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | mirror_flagOUT | mirror_hostgroup | error_msg | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| 51      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^select       | 0                    | 20      | NULL            | NULL                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 0     | NULL    |
| 52      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^select       | 1                    | 21      | NULL            | NULL                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 0     | NULL    |
| 53      | 1      | NULL     | db0        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 54      | 1      | NULL     | db0        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 55      | 1      | NULL     | db1        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 56      | 1      | NULL     | db1        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 57      | 1      | NULL     | db2        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 58      | 1      | NULL     | db2        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 59      | 1      | NULL     | db3        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 60      | 1      | NULL     | db3        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 61      | 1      | NULL     | db4        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 62      | 1      | NULL     | db4        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 63      | 1      | NULL     | db5        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 64      | 1      | NULL     | db5        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 65      | 1      | NULL     | db6        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 66      | 1      | NULL     | db6        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 67      | 1      | NULL     | db7        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 68      | 1      | NULL     | db7        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 69      | 1      | NULL     | db8        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 70      | 1      | NULL     | db8        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 71      | 1      | NULL     | db9        | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 72      | 1      | NULL     | db9        | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 73      | 1      | NULL     | db10       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 74      | 1      | NULL     | db10       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 75      | 1      | NULL     | db11       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 76      | 1      | NULL     | db11       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 77      | 1      | NULL     | db12       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1000                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 78      | 1      | NULL     | db12       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 100                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 79      | 1      | NULL     | db13       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1001                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 80      | 1      | NULL     | db13       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 101                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 81      | 1      | NULL     | db14       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1002                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 82      | 1      | NULL     | db14       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 102                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 83      | 1      | NULL     | db15       | 20     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 20      | NULL            | 1003                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
| 84      | 1      | NULL     | db15       | 21     | NULL        | NULL       | NULL       | NULL   | NULL         | NULL          | 0                    | 21      | NULL            | 103                   | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Apr 13, 2017

Contributor

I just reproduced it, and analyzed traffic with tcpdump.
mysql clients sends SELECT DATABASE() before execution INIT_DB (use db0) , and immediately after sends show databases and show tables:

11:43:49.242497 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [.], ack 171, win 342, options [nop,nop,TS val 73036348 ecr 73036338], length 0
        0x0000:  4508 0034 3bd5 4000 4006 00e5 7f00 0001  E..4;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a5d 25bb 8755  ..........J]%..U
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 723c  ...V.(.......Zr<
        0x0030:  045a 7232                                .Zr2
11:43:57.063536 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 124:146, ack 171, win 342, options [nop,nop,TS val 73038303 ecr 73036338], length 22
        0x0000:  4508 004a 3bd6 4000 4006 00ce 7f00 0001  E..J;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a5d 25bb 8755  ..........J]%..U
        0x0020:  8018 0156 fe3e 0000 0101 080a 045a 79df  ...V.>.......Zy.
        0x0030:  045a 7232 1200 0000 0353 454c 4543 5420  .Zr2.....SELECT.
        0x0040:  4441 5441 4241 5345 2829                 DATABASE()
11:43:57.102133 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 146, win 342, options [nop,nop,TS val 73038313 ecr 73038303], length 0
        0x0000:  4500 0034 f49e 4000 4006 4823 7f00 0001  E..4..@.@.H#....
        0x0010:  7f00 0001 1902 e6de 25bb 8755 c6e2 4a73  ........%..U..Js
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 79e9  ...V.(.......Zy.
        0x0030:  045a 79df                                .Zy.
11:44:07.063785 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 171:252, ack 146, win 342, options [nop,nop,TS val 73040803 ecr 73038303], length 81
        0x0000:  4500 0085 f49f 4000 4006 47d1 7f00 0001  E.....@.@.G.....
        0x0010:  7f00 0001 1902 e6de 25bb 8755 c6e2 4a73  ........%..U..Js
        0x0020:  8018 0156 fe79 0000 0101 080a 045a 83a3  ...V.y.......Z..
        0x0030:  045a 79df 4d00 0001 ff29 2323 4859 3030  .Zy.M....)##HY00
        0x0040:  304d 6178 2063 6f6e 6e65 6374 2074 696d  0Max.connect.tim
        0x0050:  656f 7574 2072 6561 6368 6564 2077 6869  eout.reached.whi
        0x0060:  6c65 2072 6561 6368 696e 6720 686f 7374  le.reaching.host
        0x0070:  6772 6f75 7020 3020 6166 7465 7220 3130  group.0.after.10
        0x0080:  3030 306d 73                             000ms
11:44:07.063862 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [.], ack 252, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 0
        0x0000:  4508 0034 3bd7 4000 4006 00e3 7f00 0001  E..4;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a73 25bb 87a6  ..........Js%...
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 83a3  ...V.(.......Z..
        0x0030:  045a 83a3                                .Z..
11:44:07.063889 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 146:154, ack 252, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 8
        0x0000:  4508 003c 3bd8 4000 4006 00da 7f00 0001  E..<;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a73 25bb 87a6  ..........Js%...
        0x0020:  8018 0156 fe30 0000 0101 080a 045a 83a3  ...V.0.......Z..
        0x0030:  045a 83a3 0400 0000 0264 6230            .Z.......db0
11:44:07.063899 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 154, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 0
        0x0000:  4500 0034 f4a0 4000 4006 4821 7f00 0001  E..4..@.@.H!....
        0x0010:  7f00 0001 1902 e6de 25bb 87a6 c6e2 4a7b  ........%.....J{
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 83a3  ...V.(.......Z..
        0x0030:  045a 83a3                                .Z..
11:44:07.063947 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 252:263, ack 154, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 11
        0x0000:  4500 003f f4a1 4000 4006 4815 7f00 0001  E..?..@.@.H.....
        0x0010:  7f00 0001 1902 e6de 25bb 87a6 c6e2 4a7b  ........%.....J{
        0x0020:  8018 0156 fe33 0000 0101 080a 045a 83a3  ...V.3.......Z..
        0x0030:  045a 83a3 0700 0001 0000 0002 0002 00    .Z.............
11:44:07.065605 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 154:173, ack 263, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 19
        0x0000:  4508 0047 3bd9 4000 4006 00ce 7f00 0001  E..G;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a7b 25bb 87b1  ..........J{%...
        0x0020:  8018 0156 fe3b 0000 0101 080a 045a 83a3  ...V.;.......Z..
        0x0030:  045a 83a3 0f00 0000 0373 686f 7720 6461  .Z.......show.da
        0x0040:  7461 6261 7365 73                        tabases
11:44:07.102213 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 173, win 342, options [nop,nop,TS val 73040813 ecr 73040803], length 0
        0x0000:  4500 0034 f4a2 4000 4006 481f 7f00 0001  E..4..@.@.H.....
        0x0010:  7f00 0001 1902 e6de 25bb 87b1 c6e2 4a8e  ........%.....J.
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 83ad  ...V.(.......Z..
        0x0030:  045a 83a3                                .Z..
11:44:17.066953 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 263:344, ack 173, win 342, options [nop,nop,TS val 73043304 ecr 73040803], length 81
        0x0000:  4500 0085 f4a3 4000 4006 47cd 7f00 0001  E.....@.@.G.....
        0x0010:  7f00 0001 1902 e6de 25bb 87b1 c6e2 4a8e  ........%.....J.
        0x0020:  8018 0156 fe79 0000 0101 080a 045a 8d68  ...V.y.......Z.h
        0x0030:  045a 83a3 4d00 0001 ff29 2323 4859 3030  .Z..M....)##HY00
        0x0040:  304d 6178 2063 6f6e 6e65 6374 2074 696d  0Max.connect.tim
        0x0050:  656f 7574 2072 6561 6368 6564 2077 6869  eout.reached.whi
        0x0060:  6c65 2072 6561 6368 696e 6720 686f 7374  le.reaching.host
        0x0070:  6772 6f75 7020 3020 6166 7465 7220 3130  group.0.after.10
        0x0080:  3030 316d 73                             001ms
11:44:17.067022 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 173:189, ack 344, win 342, options [nop,nop,TS val 73043304 ecr 73043304], length 16
        0x0000:  4508 0044 3bda 4000 4006 00d0 7f00 0001  E..D;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a8e 25bb 8802  ..........J.%...
        0x0020:  8018 0156 fe38 0000 0101 080a 045a 8d68  ...V.8.......Z.h
        0x0030:  045a 8d68 0c00 0000 0373 686f 7720 7461  .Z.h.....show.ta
        0x0040:  626c 6573                                bles
11:44:17.067036 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 189, win 342, options [nop,nop,TS val 73043304 ecr 73043304], length 0
        0x0000:  4500 0034 f4a4 4000 4006 481d 7f00 0001  E..4..@.@.H.....
        0x0010:  7f00 0001 1902 e6de 25bb 8802 c6e2 4a9e  ........%.....J.
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 8d68  ...V.(.......Z.h
        0x0030:  045a 8d68                                .Z.h
11:44:27.067800 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 344:425, ack 189, win 342, options [nop,nop,TS val 73045804 ecr 73043304], length 81
        0x0000:  4500 0085 f4a5 4000 4006 47cb 7f00 0001  E.....@.@.G.....
        0x0010:  7f00 0001 1902 e6de 25bb 8802 c6e2 4a9e  ........%.....J.
        0x0020:  8018 0156 fe79 0000 0101 080a 045a 972c  ...V.y.......Z.,
        0x0030:  045a 8d68 4d00 0001 ff29 2323 4859 3030  .Z.hM....)##HY00
        0x0040:  304d 6178 2063 6f6e 6e65 6374 2074 696d  0Max.connect.tim
        0x0050:  656f 7574 2072 6561 6368 6564 2077 6869  eout.reached.whi
        0x0060:  6c65 2072 6561 6368 696e 6720 686f 7374  le.reaching.host
        0x0070:  6772 6f75 7020 3020 6166 7465 7220 3130  group.0.after.10
        0x0080:  3030 306d 73                             000ms
11:44:27.107540 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [.], ack 425, win 342, options [nop,nop,TS val 73045814 ecr 73045804], length 0
        0x0000:  4508 0034 3bdb 4000 4006 00df 7f00 0001  E..4;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a9e 25bb 8853  ..........J.%..S
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 9736  ...V.(.......Z.6
        0x0030:  045a 972c                                .Z.,

What happens is that when you connect without specifying any schema (with no database given), it internally assigns the default schema specified in mysql_servers.default_schema : if this is NULL, it uses the global variables mysql-default_schema that defaults to information_schema .
In other words, although ProxySQL allows a client to connect without specifying a schema, it always needs a schema assigned: so if a client doesn't specify one, it will set a default.
Therefore, it will try to execute SELECT DATABASE() against information_schema .

In your case (no default hostgroup) I recommend to always specify a schema when connecting to ProxySQL .
A possible workaround is to create a rule that immediately returns an error (without waiting 10 seconds) when schema is information_schema and query is SELECT DATABASE() .
Note that a lot of applications always specify a schema when connecting, so this shouldn't be a problem.
A third solution is for ProxySQL to reply to SELECT DATABASE() without sending any query to any backend.

Contributor

renecannao commented Apr 13, 2017

I just reproduced it, and analyzed traffic with tcpdump.
mysql clients sends SELECT DATABASE() before execution INIT_DB (use db0) , and immediately after sends show databases and show tables:

11:43:49.242497 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [.], ack 171, win 342, options [nop,nop,TS val 73036348 ecr 73036338], length 0
        0x0000:  4508 0034 3bd5 4000 4006 00e5 7f00 0001  E..4;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a5d 25bb 8755  ..........J]%..U
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 723c  ...V.(.......Zr<
        0x0030:  045a 7232                                .Zr2
11:43:57.063536 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 124:146, ack 171, win 342, options [nop,nop,TS val 73038303 ecr 73036338], length 22
        0x0000:  4508 004a 3bd6 4000 4006 00ce 7f00 0001  E..J;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a5d 25bb 8755  ..........J]%..U
        0x0020:  8018 0156 fe3e 0000 0101 080a 045a 79df  ...V.>.......Zy.
        0x0030:  045a 7232 1200 0000 0353 454c 4543 5420  .Zr2.....SELECT.
        0x0040:  4441 5441 4241 5345 2829                 DATABASE()
11:43:57.102133 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 146, win 342, options [nop,nop,TS val 73038313 ecr 73038303], length 0
        0x0000:  4500 0034 f49e 4000 4006 4823 7f00 0001  E..4..@.@.H#....
        0x0010:  7f00 0001 1902 e6de 25bb 8755 c6e2 4a73  ........%..U..Js
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 79e9  ...V.(.......Zy.
        0x0030:  045a 79df                                .Zy.
11:44:07.063785 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 171:252, ack 146, win 342, options [nop,nop,TS val 73040803 ecr 73038303], length 81
        0x0000:  4500 0085 f49f 4000 4006 47d1 7f00 0001  E.....@.@.G.....
        0x0010:  7f00 0001 1902 e6de 25bb 8755 c6e2 4a73  ........%..U..Js
        0x0020:  8018 0156 fe79 0000 0101 080a 045a 83a3  ...V.y.......Z..
        0x0030:  045a 79df 4d00 0001 ff29 2323 4859 3030  .Zy.M....)##HY00
        0x0040:  304d 6178 2063 6f6e 6e65 6374 2074 696d  0Max.connect.tim
        0x0050:  656f 7574 2072 6561 6368 6564 2077 6869  eout.reached.whi
        0x0060:  6c65 2072 6561 6368 696e 6720 686f 7374  le.reaching.host
        0x0070:  6772 6f75 7020 3020 6166 7465 7220 3130  group.0.after.10
        0x0080:  3030 306d 73                             000ms
11:44:07.063862 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [.], ack 252, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 0
        0x0000:  4508 0034 3bd7 4000 4006 00e3 7f00 0001  E..4;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a73 25bb 87a6  ..........Js%...
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 83a3  ...V.(.......Z..
        0x0030:  045a 83a3                                .Z..
11:44:07.063889 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 146:154, ack 252, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 8
        0x0000:  4508 003c 3bd8 4000 4006 00da 7f00 0001  E..<;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a73 25bb 87a6  ..........Js%...
        0x0020:  8018 0156 fe30 0000 0101 080a 045a 83a3  ...V.0.......Z..
        0x0030:  045a 83a3 0400 0000 0264 6230            .Z.......db0
11:44:07.063899 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 154, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 0
        0x0000:  4500 0034 f4a0 4000 4006 4821 7f00 0001  E..4..@.@.H!....
        0x0010:  7f00 0001 1902 e6de 25bb 87a6 c6e2 4a7b  ........%.....J{
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 83a3  ...V.(.......Z..
        0x0030:  045a 83a3                                .Z..
11:44:07.063947 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 252:263, ack 154, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 11
        0x0000:  4500 003f f4a1 4000 4006 4815 7f00 0001  E..?..@.@.H.....
        0x0010:  7f00 0001 1902 e6de 25bb 87a6 c6e2 4a7b  ........%.....J{
        0x0020:  8018 0156 fe33 0000 0101 080a 045a 83a3  ...V.3.......Z..
        0x0030:  045a 83a3 0700 0001 0000 0002 0002 00    .Z.............
11:44:07.065605 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 154:173, ack 263, win 342, options [nop,nop,TS val 73040803 ecr 73040803], length 19
        0x0000:  4508 0047 3bd9 4000 4006 00ce 7f00 0001  E..G;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a7b 25bb 87b1  ..........J{%...
        0x0020:  8018 0156 fe3b 0000 0101 080a 045a 83a3  ...V.;.......Z..
        0x0030:  045a 83a3 0f00 0000 0373 686f 7720 6461  .Z.......show.da
        0x0040:  7461 6261 7365 73                        tabases
11:44:07.102213 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 173, win 342, options [nop,nop,TS val 73040813 ecr 73040803], length 0
        0x0000:  4500 0034 f4a2 4000 4006 481f 7f00 0001  E..4..@.@.H.....
        0x0010:  7f00 0001 1902 e6de 25bb 87b1 c6e2 4a8e  ........%.....J.
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 83ad  ...V.(.......Z..
        0x0030:  045a 83a3                                .Z..
11:44:17.066953 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 263:344, ack 173, win 342, options [nop,nop,TS val 73043304 ecr 73040803], length 81
        0x0000:  4500 0085 f4a3 4000 4006 47cd 7f00 0001  E.....@.@.G.....
        0x0010:  7f00 0001 1902 e6de 25bb 87b1 c6e2 4a8e  ........%.....J.
        0x0020:  8018 0156 fe79 0000 0101 080a 045a 8d68  ...V.y.......Z.h
        0x0030:  045a 83a3 4d00 0001 ff29 2323 4859 3030  .Z..M....)##HY00
        0x0040:  304d 6178 2063 6f6e 6e65 6374 2074 696d  0Max.connect.tim
        0x0050:  656f 7574 2072 6561 6368 6564 2077 6869  eout.reached.whi
        0x0060:  6c65 2072 6561 6368 696e 6720 686f 7374  le.reaching.host
        0x0070:  6772 6f75 7020 3020 6166 7465 7220 3130  group.0.after.10
        0x0080:  3030 316d 73                             001ms
11:44:17.067022 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [P.], seq 173:189, ack 344, win 342, options [nop,nop,TS val 73043304 ecr 73043304], length 16
        0x0000:  4508 0044 3bda 4000 4006 00d0 7f00 0001  E..D;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a8e 25bb 8802  ..........J.%...
        0x0020:  8018 0156 fe38 0000 0101 080a 045a 8d68  ...V.8.......Z.h
        0x0030:  045a 8d68 0c00 0000 0373 686f 7720 7461  .Z.h.....show.ta
        0x0040:  626c 6573                                bles
11:44:17.067036 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [.], ack 189, win 342, options [nop,nop,TS val 73043304 ecr 73043304], length 0
        0x0000:  4500 0034 f4a4 4000 4006 481d 7f00 0001  E..4..@.@.H.....
        0x0010:  7f00 0001 1902 e6de 25bb 8802 c6e2 4a9e  ........%.....J.
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 8d68  ...V.(.......Z.h
        0x0030:  045a 8d68                                .Z.h
11:44:27.067800 IP 127.0.0.1.6402 > 127.0.0.1.59102: Flags [P.], seq 344:425, ack 189, win 342, options [nop,nop,TS val 73045804 ecr 73043304], length 81
        0x0000:  4500 0085 f4a5 4000 4006 47cb 7f00 0001  E.....@.@.G.....
        0x0010:  7f00 0001 1902 e6de 25bb 8802 c6e2 4a9e  ........%.....J.
        0x0020:  8018 0156 fe79 0000 0101 080a 045a 972c  ...V.y.......Z.,
        0x0030:  045a 8d68 4d00 0001 ff29 2323 4859 3030  .Z.hM....)##HY00
        0x0040:  304d 6178 2063 6f6e 6e65 6374 2074 696d  0Max.connect.tim
        0x0050:  656f 7574 2072 6561 6368 6564 2077 6869  eout.reached.whi
        0x0060:  6c65 2072 6561 6368 696e 6720 686f 7374  le.reaching.host
        0x0070:  6772 6f75 7020 3020 6166 7465 7220 3130  group.0.after.10
        0x0080:  3030 306d 73                             000ms
11:44:27.107540 IP 127.0.0.1.59102 > 127.0.0.1.6402: Flags [.], ack 425, win 342, options [nop,nop,TS val 73045814 ecr 73045804], length 0
        0x0000:  4508 0034 3bdb 4000 4006 00df 7f00 0001  E..4;.@.@.......
        0x0010:  7f00 0001 e6de 1902 c6e2 4a9e 25bb 8853  ..........J.%..S
        0x0020:  8010 0156 fe28 0000 0101 080a 045a 9736  ...V.(.......Z.6
        0x0030:  045a 972c                                .Z.,

What happens is that when you connect without specifying any schema (with no database given), it internally assigns the default schema specified in mysql_servers.default_schema : if this is NULL, it uses the global variables mysql-default_schema that defaults to information_schema .
In other words, although ProxySQL allows a client to connect without specifying a schema, it always needs a schema assigned: so if a client doesn't specify one, it will set a default.
Therefore, it will try to execute SELECT DATABASE() against information_schema .

In your case (no default hostgroup) I recommend to always specify a schema when connecting to ProxySQL .
A possible workaround is to create a rule that immediately returns an error (without waiting 10 seconds) when schema is information_schema and query is SELECT DATABASE() .
Note that a lot of applications always specify a schema when connecting, so this shouldn't be a problem.
A third solution is for ProxySQL to reply to SELECT DATABASE() without sending any query to any backend.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Jun 11, 2018

Contributor

Closing this issue

Contributor

renecannao commented Jun 11, 2018

Closing this issue

@renecannao renecannao closed this Jun 11, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment