Personal blog of Yzmir Ramirez

In this blog post, we’ll look at a solving Sudoku using MySQL 8.0 recursive common table expression.

Vadim was recently having a little Saturday morning fun solving Sudoku using MySQL 8. The whole idea comes from SQLite, where Richard Hipp has come up with some outlandish recursive query examples — WITH clause.

The SQLite query:

WITH RECURSIVE
 input(sud) AS (
   VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
 ),
 digits(z, lp) AS (
   VALUES('1', 1)
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
 ),
 x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
     substr(s, 1, ind-1) || z || substr(s, ind+1),
     instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
    FROM x, digits AS z
   WHERE ind>0
     AND NOT EXISTS (
           SELECT 1
             FROM digits AS lp
            WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
               OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
               OR z.z = substr(s, (((ind-1)/3) % 3) * 3
                       + ((ind-1)/27) * 27 + lp
                       + ((lp-1) / 3) * 6, 1)
        )
 )
SELECT s FROM x WHERE ind=0;

Which should provide the answer: 534678912672195348198342567859761423426853791713924856961537284287419635345286179.

The modified query to run on MySQL 8.0.3 release candidate and MariaDB Server 10.2.9 stable GA courtesy of Vadim:

WITH RECURSIVE
 input(sud) AS (
   SELECT '53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79'
 ),
 digits(z, lp) AS (
   SELECT '1', 1
   UNION ALL SELECT
   CAST(lp+1 AS CHAR), lp+1 FROM digits WHERE lp<9
 ),
 x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
     concat(substr(s, 1, ind-1) , z , substr(s, ind+1)),
     instr( concat(substr(s, 1, ind-1) ,z ,substr(s, ind+1)), '.' )
    FROM x, digits AS z
   WHERE ind>0
     AND NOT EXISTS (
           SELECT 1
             FROM digits AS lp
            WHERE z.z = substr(s, ((ind-1) DIV 9)*9 + lp, 1)
               OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
               OR z.z = substr(s, (((ind-1) DIV 3) % 3) * 3
                       + ((ind-1) DIV 27) * 27 + lp
                       + ((lp-1) DIV 3) * 6, 1)
        )
 )
SELECT s FROM x WHERE ind=0;

The test environment for the setup is a standard Linode 1024 instance, with one CPU core and 1GB of RAM. The base OS was Ubuntu 17.04. MySQL and MariaDB Server were installed via their respective tarballs. No configuration is done beyond a basic out-of-the-box install inside of the MySQL sandbox. This is similar for sqlite3. Remember to run “.timer on” for sqlite3.

Note that initially they were done on separate instances, but because of the variance you get in cloud instances, it was decided that it would be better to run on the same instance using the MySQL Sandbox.

MySQL 8 first run time: 0.16s. 5 runs: 0.16, 0.16, 0.17, 0.16, 0.16
MariaDB Server 10.2 first run time: 0.20s. 5 runs: 0.22, 0.22, 0.21, 0.21, 0.20
MariaDB Server 10.3.2 first run time: 0.206s. 5 runs: 0.237, 0.199, 0.197, 0.198, 0.192
SQLite3 first run time: Run Time: real 0.328 user 0.323333 sys 0.003333 / Run Time: real 0.334 user 0.333333 sys 0.000000

Trying a more complex Sudoku routine, “..41..2.3……..12…..8..82.6.43…..8.9…..67.2.48..5…..64……..3.7..69..” to produce the result “574198263638425791219367854821654379743819625956732148195273486462981537387546912″, the results are:

MySQL 8 first run time: 4.87s. 5 runs: 5.43, 5.35, 5.10, 5.19, 5.05
MariaDB Server 10.2 first run time: 6.65s. 5 runs: 7.03, 6.57, 6.61, 6.59, 7.12
MariaDB Server 10.3.2 first run time: 6.121s. 5 runs: 5.701, 6.043, 6.043, 5.849, 6.199
SQLite3 first run time: Run Time: real 10.105 user 10.099999 sys 0.000000 / Run Time: real 11.305 user 11.293333 sys 0.000000

Conclusions from this fun little exercise? SQL, even though it’s a standard is not portable between databases. Thankfully, MySQL and MariaDB are syntax-compatible in this case! MySQL and MariaDB Server are both faster than sqlite3 when returning a recursive CTE. It would seem that the MySQL 8.0.3 release candidate is faster at solving these Sudoku routines compared to the MariaDB Server 10.2 stable GA release. It also seems that MariaDB Server 10.3.2 alpha is marginally quicker than MariaDB Server 10.2.

Kudos to Team MariaDB for getting recursive common table expression support first in the MySQL ecosystem, and kudos to Team MySQL for making it fast!

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;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)
mysql> select database();
+------------+
| DATABASE() |
+------------+
| admin      |
+------------+
1 row in set (0.00 sec)
mysql> use stats;
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
mysql> select database();
+------------+
| DATABASE() |
+------------+
| admin      |
+------------+
1 row in set (0.00 sec)
mysql> use funkydatabase;
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

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;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| mysql_collations                     |
| mysql_query_rules                    |
| mysql_replication_hostgroups         |
| mysql_servers                        |
| mysql_users                          |
| runtime_global_variables             |
| runtime_mysql_query_rules            |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers                |
| runtime_mysql_users                  |
| runtime_scheduler                    |
| scheduler                            |
+--------------------------------------+
13 rows in set (0.00 sec)
mysql> show tables from stats;
+--------------------------------+
| tables                         |
+--------------------------------+
| global_variables               |
| stats_mysql_commands_counters  |
| stats_mysql_connection_pool    |
| stats_mysql_global             |
| stats_mysql_processlist        |
| stats_mysql_query_digest       |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules        |
+--------------------------------+
8 rows in set (0.00 sec)
mysql> select count(*) from stats_mysql_commands_counters;
+----------+
| count(*) |
+----------+
| 52       |
+----------+
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
*************************** 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;
+-----------+---------------+--------+-----------+---------+---------+--------+
| SessionID | user          | db     | hostgroup | command | time_ms | info   |
+-----------+---------------+--------+-----------+---------+---------+--------+
| 129       | proxysql_user | sbtest | 10        | Query   | 14      | COMMIT |
| 130       | proxysql_user | sbtest | 10        | Query   | 16      | COMMIT |
| 131       | proxysql_user | sbtest | 10        | Query   | 9       | COMMIT |
| 133       | proxysql_user | sbtest | 10        | Query   | 0       | COMMIT |
| 134       | proxysql_user | sbtest | 10        | Query   | 5       | COMMIT |
….
| 191       | proxysql_user | sbtest | 10        | Query   | 4       | COMMIT |
| 192       | proxysql_user | sbtest | 10        | Query   | 1       | COMMIT |
+-----------+---------------+--------+-----------+---------+---------+--------+
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;
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!