ProxySQL Admin Interface Is Not Your Typical MySQL Server!

June 7, 2017

Author

Peter Zaitsev

MySQL

Share this Post:

In this blog post, I’ll look at how ProxySQL Admin behaves in some unusual and unexpected ways from a MySQL perspective.

ProxySQL allows you to connect to its admin interface using the MySQL protocol and use familiar tools, like the MySQL command line client, to manage its configuration as a set of configuration tables. This ability may trick you into thinking that you’re working with a stripped-down MySQL server – and expect it to behave like MySQL.

It would be a mistake to think this! In fact, ProxySQL embeds the SQLite database to store its configuration. As such, it behaves much closer to SQLite!

Below, I’ll show you a few things that confused me at first. All of these are as of ProxySQL 1.3.6 (in case behavior changes in the future).

Fake support for Use command

mysql> show databases;<br>+-----+---------+-------------------------------+<br>| seq | name    | file                          |<br>+-----+---------+-------------------------------+<br>| 0   | main    |                               |<br>| 2   | disk    | /var/lib/proxysql/proxysql.db |<br>| 3   | stats   |                               |<br>| 4   | monitor |                               |<br>+-----+---------+-------------------------------+<br>4 rows in set (0.00 sec)<br>mysql> select database();<br>+------------+<br>| DATABASE() |<br>+------------+<br>| admin      |<br>+------------+<br>1 row in set (0.00 sec)<br>mysql> use stats;<br>Reading table information for completion of table and column names<br>You can turn off this feature to get a quicker startup with -A<br>Database changed<br>mysql> select database();<br>+------------+<br>| DATABASE() |<br>+------------+<br>| admin      |<br>+------------+<br>1 row in set (0.00 sec)<br>mysql> use funkydatabase;<br>Reading table information for completion of table and column names<br>You can turn off this feature to get a quicker startup with -A<br>Database changed

So here we can see that:

There is a concept of multiple databases in the ProxySQL admin interface

The ProxySQL admin interface supports the select database(); function, which is always same value independent of the database you tried to set. Typically it will be “admin” or “stats”, depending on what user you use to connect to the database.

You can use the “use” command to change the database – but it does not really change the database. This is a required command, because if you don’t support it many MySQL clients will not connect.

Invisible tables

mysql> show tables;<br>+--------------------------------------+<br>| tables                               |<br>+--------------------------------------+<br>| global_variables                     |<br>| mysql_collations                     |<br>| mysql_query_rules                    |<br>| mysql_replication_hostgroups         |<br>| mysql_servers                        |<br>| mysql_users                          |<br>| runtime_global_variables             |<br>| runtime_mysql_query_rules            |<br>| runtime_mysql_replication_hostgroups |<br>| runtime_mysql_servers                |<br>| runtime_mysql_users                  |<br>| runtime_scheduler                    |<br>| scheduler                            |<br>+--------------------------------------+<br>13 rows in set (0.00 sec)<br>mysql> show tables from stats;<br>+--------------------------------+<br>| tables                         |<br>+--------------------------------+<br>| global_variables               |<br>| stats_mysql_commands_counters  |<br>| stats_mysql_connection_pool    |<br>| stats_mysql_global             |<br>| stats_mysql_processlist        |<br>| stats_mysql_query_digest       |<br>| stats_mysql_query_digest_reset |<br>| stats_mysql_query_rules        |<br>+--------------------------------+<br>8 rows in set (0.00 sec)<br>mysql> select count(*) from stats_mysql_commands_counters;<br>+----------+<br>| count(*) |<br>+----------+<br>| 52       |<br>+----------+<br>1 row in set (0.00 sec)

We can query a list of tables in our default database (which can’t change), and we also get lists of tables in the “stats” database with very familiar MySQL syntax. But we can also query the “stats” table directly without specifying the “stats” database, even if it is not shown in “show tables” for our current database.

Again this is SQLite behavior! 🙂

Strange Create Table syntax

mysql> show create table scheduler G<br>*************************** 1. row ***************************<br>      table: scheduler<br>Create Table: CREATE TABLE scheduler (<br>   id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,<br>   active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,<br>   interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,<br>   filename VARCHAR NOT NULL,<br>   arg1 VARCHAR,<br>   arg2 VARCHAR,<br>   arg3 VARCHAR,<br>   arg4 VARCHAR,<br>   arg5 VARCHAR,<br>   comment VARCHAR NOT NULL DEFAULT '')<br>1 row in set (0.00 sec)

mysql> show create table scheduler G *************************** 1. row *************************** table: scheduler Create Table: CREATE TABLE scheduler ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL, filename VARCHAR NOT NULL, arg1 VARCHAR, arg2 VARCHAR, arg3 VARCHAR, arg4 VARCHAR, arg5 VARCHAR, comment VARCHAR NOT NULL DEFAULT '') 1 row in set (0.00 sec)

If we look into the ProxySQL Admin interface table structure, we see it is not quite MySQL. It uses CHECK constraints and doesn’t specify the length for VARCHAR. This is because it is SQLite table definition.

SHOW command nuances

The ProxySQL Admin interface supports SHOW PROCESSLIST and even SHOW FULL PROCESSLIST commands, but not all the commands match the MySQL server output:

mysql> show processlist;<br>+-----------+---------------+--------+-----------+---------+---------+--------+<br>| SessionID | user          | db     | hostgroup | command | time_ms | info   |<br>+-----------+---------------+--------+-----------+---------+---------+--------+<br>| 129       | proxysql_user | sbtest | 10        | Query   | 14      | COMMIT |<br>| 130       | proxysql_user | sbtest | 10        | Query   | 16      | COMMIT |<br>| 131       | proxysql_user | sbtest | 10        | Query   | 9       | COMMIT |<br>| 133       | proxysql_user | sbtest | 10        | Query   | 0       | COMMIT |<br>| 134       | proxysql_user | sbtest | 10        | Query   | 5       | COMMIT |<br>….<br>| 191       | proxysql_user | sbtest | 10        | Query   | 4       | COMMIT |<br>| 192       | proxysql_user | sbtest | 10        | Query   | 1       | COMMIT |<br>+-----------+---------------+--------+-----------+---------+---------+--------+<br>62 rows in set (0.01 sec)

SHOW VARIABLES works, as does SHOW GLOBAL VARIABLES, but not SHOW SESSION VARIABLES.

SHOW STATUS doesn’t work as expected:

mysql> show status;<br>ERROR 1045 (#2800): near "show": syntax error

1	mysql> show status;<br>ERROR 1045 (#2800): near "show": syntax error

As you can see, while some typical MySQL commands and constructs work, others don’t. This is by design: ProxySQL implemented some of the commands to make it easy and familiar for MySQL users to navigate the ProxySQL interface. But don’t get fooled! It is not MySQL, and doesn’t always behave as you would expect.

You’ve been warned!