ProxySQL Admin Interface Is Not Your Typical MySQL Server!

ProxySQL AdminIn 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

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

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

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:


SHOW STATUS doesn’t work as expected:

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!

Share this post

Comment (1)

  • Jouni Järvinen

    It’s brainless. You’re using Norwegian to talk yourself through Swedish territory. Not exactly correct analogy, but the point should get across.

    June 7, 2017 at 2:21 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.