search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
5.96k stars 972 forks source link

Unable to parse unknown SET query #4281

Open bootc opened 1 year ago

bootc commented 1 year ago

MediaWiki 1.39.3 with the DynamicPageList extension produces SET queries which cause the connection to be locked to a particular hostgroup:

2023-07-05 22:28:51 MySQL_Session.cpp:7861:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.195.5.20:49444. Setting lock_hostgroup. Please report a bug for future enhancements:SET /* MediaWiki\Extension\DynamicPageList\Hooks::processQuery - Library:MySQL  */ STATEMENT max_statement_time=10 FOR SELECT  page_namespace,page_title  FROM `page` JOIN `categorylinks` `c1` ON ((page_id = c1.cl_from) AND (c1.cl_to='Databases'))   WHERE page_namespace = 3002  ORDER BY c1.cl_type ASC, c1.cl_sortkey ASC LIMIT 200

This is using ProxySQL 2.5.3 using the proxysql/proxysql:2.5.3 Docker container image.

renecannao commented 1 year ago

Hi @bootc . Thank you for the report.

Indeed, SET STATEMENT is not supported at the moment, and its implementation isn't trivial because indeed it is not even a SET statement, but a SELECT statement with an attribute set by SET . At the moment my only suggestion is a workaround. Using a mysql_query_rules to rewrite :

SET /* MediaWiki\Extension\DynamicPageList\Hooks::processQuery - Library:MySQL  */ STATEMENT max_statement_time=10 FOR SELECT`

into simply SELECT. I know, this will lose the query timeout ...

Because you have max_statement_time I assume you are using MariaDB , that has a completely different syntax than MySQL. If you were using MySQL you could rewrite the query as:

SELECT /*+ max_execution_time=10000 */

(and optionally also copy the rest of the comment).

I hope this helps.

bootc commented 1 year ago

Yes, this is using MariaDB indeed. If this is a known issue, could ProxySQL perhaps do its own rewriting of the query internally into the MySQL syntax which it understands, and then back out again on the way back to MariaDB? Perhaps this could even be a ProxySQL selling point helping with MySQL/MariaDB cross compatibility?

Yes for now I can rewrite the query or maybe change the SubPageList configuration to work around this.