ProxySQL has broken processing of commands in binary protocol

[pali]

When client uses MySQL binary protocol (that one which supports prepared statements) and communicating with ProxySQL, then ProxySQL incorrectly process commands and cause random errors, including data lost, random execution of commands, random errors, and also leaking session private settings to different sessions. Result is that CREATE TABLE or DROP TABLE statements are sometimes not executed on backend server or sometimes sequent INSERT/SELECT commands fails as table does not exist at all. All this happen when using concurrent multiprocess access to database via ProxySQL and is random.

For demonstration, below is simple perl script. When running multiple times, it fails with different errors, like Table 'test.t1' doesn't exist or Table 't1' already exists or in t1 table is stored number 10 multiple times or in t1 table is number 10 missing at all.

Above just makes ProxySQL unusable with server side prepared statement / MySQL binary protocol as ProxySQL just damage data or incorrectly propagates them to backend server database. When server side prepared statements are not used, then everything is OK. But not using prepared statement is a security risk -- SQL injections. Please note that command line mysql tool does not support prepared statement / MySQL binary protocol, therefore mysql tool cannot be used for checking for this problem. And some other scripting language is needed.

Here is that perl script for testing:

use strict;
use warnings;
use DBI;

my @dbhs;

for (1..10) {
        print "STEP $_\n";
        push @dbhs, my $dbh = DBI->connect('DBI:mysql:test:127.0.0.1:6033', 'root', 'root_password', { RaiseError => 1, PrintError => 0, mysql_server_prepare => 1 });
        $dbh->do('DROP TABLE IF EXISTS t1');
        $dbh->do('CREATE TEMPORARY TABLE t1(id INT)');
        $dbh->do('INSERT INTO t1 VALUES(?)', undef, 10);
        $dbh->do('INSERT INTO t1 VALUES(?)', undef, 11);
        $dbh->do('INSERT INTO t1 VALUES(?)', undef, 12);
        $dbh->do('INSERT INTO t1 VALUES(?)', undef, 13);
        my $val = $dbh->selectall_arrayref('SELECT * FROM t1 ORDER BY id LIMIT 1');
        die "INCORRECT VALUE $val->[0]->[0] IN DATABASE\n" unless $val->[0]->[0] == 10;
        $dbh->do('DROP TABLE IF EXISTS t2');
        $dbh->do('CREATE TEMPORARY TABLE t2(id INT)');
        $dbh->do('INSERT INTO t2 VALUES(?)', undef, 10);
        $dbh->do('INSERT INTO t2 VALUES(?)', undef, 11);
        $dbh->do('INSERT INTO t2 VALUES(?)', undef, 12);
        $dbh->do('INSERT INTO t2 VALUES(?)', undef, 13);
        $val = $dbh->selectall_arrayref('SELECT * FROM t2 ORDER BY id LIMIT 1');
        die "INCORRECT VALUE $val->[0]->[0] IN DATABASE\n" unless $val->[0]->[0] == 10;
}

Run it in parallel and multiple times. Replace root and root_password by login credentials. mysql_server_prepare cause that MySQL binary protocol and server side prepared statements (for placeholders) is used.

Below is output from that script called multiple times. You can see that it fails randomly on random lines with bizarre errors about non-existent table or that table exists after dropping it.

18/12/04 12:43:25$ perl test2.pl 
STEP 1
STEP 2
STEP 3
STEP 4
STEP 5
STEP 6
STEP 7
STEP 8
STEP 9
STEP 10

18/12/04 12:43:27$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:28$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t1' doesn't exist at test2.pl line 12.

18/12/04 12:43:29$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t1' doesn't exist at test2.pl line 14.

18/12/04 12:43:30$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:31$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t1' doesn't exist at test2.pl line 14.

18/12/04 12:43:31$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t1' doesn't exist at test2.pl line 14.

18/12/04 12:43:32$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't2' already exists at test2.pl line 19.

18/12/04 12:43:33$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:34$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t1' doesn't exist at test2.pl line 13.

18/12/04 12:43:34$ perl test2.pl 
STEP 1
INCORRECT VALUE 11 IN DATABASE

18/12/04 12:43:35$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:36$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t1' doesn't exist at test2.pl line 12.

18/12/04 12:43:37$ perl test2.pl 
STEP 1
STEP 2
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:37$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:38$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t1' doesn't exist at test2.pl line 14.

18/12/04 12:43:39$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't2' already exists at test2.pl line 19.

18/12/04 12:43:40$ perl test2.pl 
STEP 1
INCORRECT VALUE 11 IN DATABASE

18/12/04 12:43:40$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't2' already exists at test2.pl line 19.

18/12/04 12:43:41$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t2' doesn't exist at test2.pl line 20.

18/12/04 12:43:42$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:43$ perl test2.pl 
STEP 1
STEP 2
DBD::mysql::db do failed: Table 't2' already exists at test2.pl line 19.

18/12/04 12:43:45$ perl test2.pl 
STEP 1
INCORRECT VALUE 11 IN DATABASE

18/12/04 12:43:46$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't2' already exists at test2.pl line 19.

18/12/04 12:43:47$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

18/12/04 12:43:47$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 'test.t2' doesn't exist at test2.pl line 20.

18/12/04 12:43:48$ perl test2.pl 
STEP 1
STEP 2
DBD::mysql::db do failed: Table 't2' already exists at test2.pl line 19.

18/12/04 12:43:49$ perl test2.pl 
STEP 1
DBD::mysql::db do failed: Table 't1' already exists at test2.pl line 11.

[pali]

Sometimes killing proxysql process and starting it again helps to pass test script.

[renecannao]

Thank you for the report. This issue seems caused by multiplexing not being disabled in CREATE TEMPORARY TABLE . Is surely needs more investigation.

[pali]

And important is that this problem happens only when using MySQL binary protocol (prepared statements). When using MySQL text protocol I was not able to reproduce this problem.

What else for investigation is needed? I have there reproducer script together with its output.

[renecannao]

I haven't reproduce it yet, but I think the script you wrote makes it very easy to reproduce it. Thank you!

I am pretty confident the issue is caused by multiplexing not being disabled in CREATE TEMPORARY TABLE because the need of disabling multiplexing is not stored in prepared statements metadata . This behavior largely differs from text protocol, where there is no metadata cache and each single query is sent to Query Processor for evaluation.

[pali]

Hi! Is there any progress on this issue?

[dsn]

Any updates?

[jstewart612]

from https://proxysql.com/documentation/multiplexing/:

Note: disabling multiplexing doesn’t disable routing, so it might happen that after a CREATE TEMPORARY TABLE is executed, a SELECT query on the same table returns a 'schemaname.temporary_tablename' doesn't exist error message. The reason for this is that while multiplexing is disabled, routing isn’t – and if the two statements are sent to two different hostgroups, the error message will appear. To prevent this, it is advised to use query routing. See [MySQL query rules](https://proxysql.com/documentation/main-runtime/#mysql_query_rules).

Based on my reading of above, if I know these queries hit the same hostgroup, would disabling multiplexing mitigate this issue?