The timestamp set in SET TIMESTAMP calls is seen by other connections

[davidscholberg]

When you issue a SET TIMESTAMP command through ProxySQL, other connections that didn't set any timestamp will see the manually set timestamp rather than the current one.

Reproduction steps (tested on Debian 8, ProxySQL 1.4.14):

Run the following through ProxySQL:

$ mysql -h foo.db -B -N -e "select now();"
2019-04-25 21:47:38
$ mysql -h foo.db -e "set timestamp = 1563768000;"
$ mysql -h foo.db -B -N -e "select now();"
2019-07-22 00:00:00

You may need to run the last command a few times before you see the manually set timestamp. The above is not reproducible if you connect directly to the MySQL instance, which is expected behavior since the default scope of SET calls is session.

It looks like this behavior may already be a known issue, but it's not obvious what the workaround is, if any. Do we need to disable multiplexing? What are the consequences of doing so?

[renecannao]

Hi @davidscholberg .
Yes, you need to disable multiplexing.

Note that disabling multiplexing doesn't automatically disable routing, so if a client run set timestamp and select now(), you needs to have rules to send both queries to same hostgroup (and because multiplexing will be disabled, the same connection will be used).

Nonetheless, I think this issue must be considered a bug, because although proxysql shouldn't track set timestamp, multiplexing must be automatically disabled

[renecannao]

As a further note: when multiplexing is disabled, the backend connection is reset when the client disconnects, so no other client will see the timestamp set in the first client connection.

[davidscholberg]

@renecannao thank you for responding so quickly.

I've tried disabling multiplexing globally with the following:

update global_variables set variable_value = 'false' where variable_name = 'mysql-multiplexing';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

But I'm still able to reproduce the above timestamp bug, even after restarting ProxySQL. Any ideas?

Also, is there a non-disruptive way to get rid of the backend connection with the manually set timestamp? You mentioned in #2032 (comment) about purging the connection pool, but I'm not seeing how to do this anywhere.

[renecannao]

Fixed in 2.0.4 .
Thank you for the report.

[davidscholberg]

I also tried disabling multiplexing by creating a query rule:

INSERT INTO mysql_query_rules (active, match_digest, multiplex) VALUES ('1', '^SET TIMESTAMP=?', 0);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

I confirmed that the query rule is being hit by looking at the stats_mysql_query_rules table, but the timestamp bug still occurs.

I can file another bug report for this if you want, but tbh I'm not sure if this is a bug or if I'm just missing something.

[renecannao]

Re-opening, and I will try to reproduce disabling multiplexing with query rules

[renecannao]

I tried to reproduce the issue with the query rule listed above (slightly modified tho, match_digest='^SET TIMESTAMP, to avoid any sort of whitespaces) and ProxySQL 2.0.3 , and I couldn't reproduce the issue.

@davidscholberg : either I missed something in reproducing it, or maybe the issue is only with 1.4 (I didn't try that version).
Do you mind trying 2.0.3?
Or directly 2.0.4 .
Thanks

[davidscholberg]

@renecannao thank you for looking into this.

I updated to 2.0.3 on a Debian Stretch box, and I was unable to reproduce the original issue after disabling multiplexing. However, I am able to reproduce the issue using 2.0.3 on Debian Jessie. I'm not sure if you are still supporting Jessie or not, but I figured I'd mention it in any case.

[renecannao]

@davidscholberg , are you sure you are following exactly the same steps?
Using Debian Stretch or Debian Jessie shouldn't make any difference, that is very weird

[davidscholberg]

@renecannao yes I am following the same steps. I have verified that I am running 2.0.3:

> select * from global_variables where variable_name = 'admin-version';
+---------------+-------------------+
| variable_name | variable_value    |
+---------------+-------------------+
| admin-version | 2.0.3-29-g00f26d5 |
+---------------+-------------------+
1 row in set (0.00 sec)

I have also re-enabled multiplexing and then disabled it again both globally and through the query rule, but the bug still occurs (only on Jessie).

[davidscholberg]

Just wanted to give an update. I was able to get the workaround working on a different jessie server, so it appears that this is not a jessie issue. However, I still can't get the workaround to work on the original server that I tested this on, even after purging and reinstalling several proxysql versions.

[davidscholberg]

@renecannao By way of update, I tested the workaround on multiple MySQL backends of two different versions (5.6 and 5.7). This was all tested from the same ProxySQL instance (version 1.4.14-percona-1.1) with mysql-multiplexing set to false the entire time. What I found was that if the backend was MySQL 5.6, multiplexing appeared to not be disabled (i.e the timestamp bug still occurred), but if the backend was MySQL 5.7, multiplexing appeared to be properly disabled (i.e. the timestamp bug did not occur).