ProxySQL

ProxySQL is an application which allows you to distribute SQL queries across several SQL servers (eg: MySQL, MariaDB and PostgreSQL) which it is assumed are synchronised with each other in some way (which ProxySQL does not do for you), so that you get the same answer no matter which server the queries goes to.

ProxySQL also checks on the health and responsiveness of the back-end servers, sending queries only to the ones which are expected to be able to answer, and, depending on your configuration, favouring higher-performance servers which can cope with a higher workload of queries.

It seems to work pretty well.

ProxySQL has a configuration file, which it only ever uses once even if you restart it multiple times.

Every time after the first startup, it takes its configuration from its own internal database (which uses SQLite, interestingly enough), and will totally ignore the configuration file even if you've made changes to it which are newer than the settings in its database.

You can over-ride this by starting ProxySQL with the --initial flag, which causes it to wipe its internal database entirely and re-create it from the configuration file.

The next bit of weirdness is that because the internal database uses SQLite, you configure ProxySQL (which, remember, is acting as a front-end for MySQL, MariaDB or PostgreSQL) using SQL-like statements which differ in meaningful ways from proper SQL such as you would use with any of those back-end systems.

Therefore the syntax of the configuration commands is subtly unlike the SQL you might be familiar with, because you're running a cluster of proper SQL servers.

However, it has (at least) one amazingly bad design choice - it puts log files in /var/lib/proxysql, it gives them numerically increasing filename extensions, and there is nothing you can do about this.

This means that:

• you cannot get it to log to anything under /var/log
  • well, you could, by setting up a symbolic link, but then you'd end up with the ProxySQL configuration database, its PKI certificates and its PID file all under /var/log as well
• you cannot perform standard logfile rotation (for example, giving each file a date-based extension so you know which file to look in for transactions in a particular time period) because the current file keeps on having a new name
• you cannot configure fail2ban for example, again because it doesn't know which file to look in for the current log records

I find this amazing and appalling, so I decided to try to do something about it.

I work on Devuan machines (Debian without systemd), so I downloaded the current source tree from https://github.com/sysown/proxysql/archive/refs/tags/v2.7.1.tar.gz and expected to be able to build a .deb package from it.

Looking at the Makefile seemed reasonably encouraging, because there were commands in there for building deb packages, and the INSTALL.md file even contained instructions telling you which pre-requisite build packages you need to install in order to proceed.

The INSTALL.md file even says:

Download a release source code archive from:
https://github.com/sysown/proxysql/releases
or get the latest development sources from:
https://github.com/sysown/proxysql/archive/refs/heads/v2.x.zip

which may well make you think you can build this thing from the source tree you downloaded from the github link.

Looking at the Makefile, it seems that make debian12 may be the way to create a .deb file for Debian 12.

Oh, no; nothing so simple.

The build instructions are designed only to work with a version downloaded using git - they don't work with the standard source tree, failing with multiple occurrences of

touch: invalid date format '@'

for example. This turns out to come from a command

find . -not -path "./binaries/*" -not -path "./.git/*" -exec touch -h --date=@`git show -s --format=%ct HEAD` {} \;

which fails with the error

fatal: not a git repository (or any of the parent directories): .git

Sure enough, neither of the above downloads contains a .git directory - they both contain a .github directory, but that's clearly not the same.

So, neither of the documented instructions work. Not a good start.

It turns out that instead of downloading the .tar.gz file (or the .zip file) as documented, you need to go up one level on the github site, find the Code - Clone URL, and then fetch using git from that:

$ git clone https://github.com/sysown/proxysql.git

Note that this will (currently, at the time of writing, when ProxySQL 2.7.1 is the current stable release) fetch version 2.7.2 for you.

You can now try make debian12 once again, and find out that the build process fails for a different reason:

make[1]: docker: No such file or directory

What?? I don't want to use docker; I just want to build a .deb package.

How difficult can they make this process?

How do they build their own .deb packages?

It looks like trying to build specifically for Debian 12 may not be the right approach (even though that's what I need to end up with). A simple make starts doing what looks like a pretty comprehensive build of the source code. It'll be interesting to see whether a .deb appears amongst the results.

No, a .deb file is not amongst the results. The results contain

  Could NOT find ZLIB (missing: ZLIB_LIBRARY ZLIB_INCLUDE_DIR)

So, I tried installing the zlib1g-dev package (not mentioned amongst the Debian pre-requisites) and tried again.

This time the compilation completed, but no .deb file was created.

Go up
Return to main index.