MySQL issues warning when using ProxySQL query annotations

[EagleEyeJohn]

# proxysql --version
2024-11-22 14:22:32 [INFO] Using jemalloc with MALLOC_CONF: config.xmalloc:1, lg_tcache_max:16, opt.prof_accum:1, opt.prof_leak:1, opt.lg_prof_sample:20, opt.lg_prof_interval:30, rc:0
ProxySQL version 2.6.6-8-gc03c5ee, codename Truls

$ mysql --version
mysql  Ver 8.0.37-29 for Linux on x86_64 (Percona Server (GPL), Release 29, Revision 30dc4e71)

When using ProxySQL annotations these are passed through to MySQL causing it to raise a warning. As we capture warnings in ProxySQL, our logs are now littered with unwanted noise, which we would like to prevent.

The same happens when issuing a query that uses MySQL Optimizer hints and ProxySQL query annotations - both work when all MySQL optimizer hints appear first, followed by all ProxySQL query annotations. However, MySQL issues a warning when it sees the first ProxySQL annotation.

Admin> SELECT /*+ ;hostgroup=10 */ @@hostname;
+------------+
| @@hostname |
+------------+
| database-d |
+------------+
1 row in set, 1 warning (0.01 sec)

Warning (Code 1064): Optimizer hint syntax error near ';hostgroup=10 */ @@hostname' at line 1
Admin> SELECT /*+ MAX_EXECUTION_TIME(30000) ;hostgroup=10 */ @@hostname;
+------------+
| @@hostname |
+------------+
| database-d |
+------------+
1 row in set, 1 warning (0.01 sec)

Warning (Code 1064): Optimizer hint syntax error near ';hostgroup=10 */ @@hostname' at line 1

We have tried using separate comments for MySQL and for ProxySQL, but this results in loss of functionality, as only the first comment starting /*+ is obeyed.

Before sending a query to MySQL, would it be possible to have ProxySQL

1. Strip all known annotations from the query, or
2. Accept an additional opening comment instead of /*+ for annotations so as to not clash with MySQL's parsing

[renecannao]

Accept an additional opening comment instead of /*+ for annotations so as to not clash with MySQL's parsing

ProxySQL only needs that the annotation is in the first comment, it doesn't require the same syntax used by the optimizer.
That is, just use a comment like /* annotation goes here */

[EagleEyeJohn]

Testing for us seems to suggest that /*+ is required in order to pick up annotations such as hostgroup=n

[renecannao]

Nope, that is not right.
My guess is that you are testing with mysql client without -c option, thus mysql client itself removes the comment

[EagleEyeJohn]

Thank you, I was.

This query is now correctly routed by the annotation. However, the MySQL optimizer hint is ignored by MySQL as query isn't killed at 3s.

Admin> SELECT /* hostgroup=41 */ /*+ MAX_EXECUTION_TIME(3000) */ @@hostname WHERE NOT SLEEP(10);
+------------+
| @@hostname |
+------------+
| database-f |
+------------+
1 row in set (10.00 sec)

If I flip the comment order, MySQL optimizer hint is obeyed

SELECT /*+ MAX_EXECUTION_TIME(3000) */ /* hostgroup=41 */ @@hostname WHERE NOT SLEEP(10);
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

If we reduce the sleep so that the optimizer hint doesn't kick in, we see hostgroup annotation being ignored

SELECT /*+ MAX_EXECUTION_TIME(3000) */ /* hostgroup=41 */ @@hostname WHERE NOT SLEEP(1);
+------------+
| @@hostname |
+------------+
| database-d |
+------------+
1 row in set (1.00 sec)

So I think I'm still at the same place where to get both MySQL optimizer hints and ProxySQL annotations functioning, we get the warning?

SELECT /*+ MAX_EXECUTION_TIME(3000); hostgroup=41 */ @@hostname WHERE NOT SLEEP(1);
+------------+
| @@hostname |
+------------+
| database-f |
+------------+
1 row in set, 1 warning (1.00 sec)

Warning (Code 1064): Optimizer hint syntax error near '; hostgroup=41 */ @@hostname WHERE NOT SLEEP(1)' at line 1