Unable to parse query

[y-trudeau]

I can figure why this fails, many other inserts on other table but with similar construct works just fine.

2018-10-04 23:06:44 MySQL_Session.cpp:3884:handler_status_WAITING_CLIENTDATASTATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode='ANSI';INSERT INTO cachelog( CacheLog_Time, CacheLog_Type, CacheLog_URL, CacheLog_Status) VALUES ( '2018-10-04 23:06:44.594063', 'Catalog', '/travel-pack-tool-rods', 2); SELECT CacheLog_ID FROM cachelog WHERE row_count() > 0 AND CacheLog_ID=last_insert_id()

[renecannao]

ProxySQL is not able to parse SET SESSION sql_mode together another statement if sent in a multi-statements command. You can disable multi-statements setting mysql-client_multi_statements=false, and then hope that the client doesn't ignore that setting: I have seen libraries ignoring the value of CLIENT_MULTI_STATEMENTS and always assuming it is enabled.

[angrygreenfrogs]

We've been running into this same issue ourselves.

This is particularly a problem for .NET code using EntityFramework, where they very obviously ignore the client multi statement capability, and they also ignore their own AllowBatch .NET connection option which is supposed to enable/disable multi-statements. I hope this saves someone else some grief to know this.

You can see their code here: https://github.com/mysql/mysql-connector-net/blob/68c54371821c87ff40a773acc127ce357b46a5ae/Source/MySql.Data.EntityFramework5/Statements/InsertStatement.cs

Which just blindly plugs a SET SESSION sql_mode to the beginning of the statement and sends it out.

Rene, I noticed that despite this appearing as an "ERROR" in the proxysql logs... the query itself seems to succeed and work.... is this more like a warning that can be ignored? It doesn't actually appear to be a hard error where proxysql is rejecting the query?

I'm at a loss to decide if we have to fork the .NET connector code, or is there any chance proxysql will support set session in multi-statements in the future?

[angrygreenfrogs]

I've found a work-around that may help others until this is supported in proxysql.

You have to be pretty sure that your application is using only one sql_mode, but otherwise this should be fine.

Definitely a bit hacky, but it does basically work fine.

I found you can add a query rule to simply replace SET SESSION sql_mode='ANSI' with a no-op call like "DO 0" (which does nothing).

Combined with setting the default sql mode to ANSI, this effectively works around the issue and allows .NET clients to work just fine through the proxy.

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, log, apply) VALUES (3, 1, "SET SESSION sql_mode='ANSI'", "DO 0", 1, 0); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

UPDATE global_variables SET variable_value='ANSI' WHERE variable_name='mysql-default_sql_mode'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; PROXYSQL RESTART;

If possible, be great to hear from @renecannao if he sees any issue with this.

[NataliyaLev]

Here is the rule we added as a work-around: { rule_id=3 active=1 match_pattern="SET TRANSACTION ISOLATION(.*)" replace_pattern="SELECT 1 LIMIT 0" apply=1 }