Error - Unable to parse query - SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'

[joetpoole]

OS: Ubuntu 20.04
ProxySQL: ProxySQL version 2.5.5-10-g195bd70
DB: MYSQL Community 5.7.41

We recently upgraded our ProxySQL version to 2.5.5 and are now seeing a constant flow of this error message which just seems to fill up the proxysql.log file.

2023-09-04 13:49:48 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-04 13:49:53 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-04 13:49:58 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-04 13:50:03 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-04 13:50:03 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-04 13:50:03 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

Our MySQL database does indeed use NO_ENGINE_SUBSTITUTION for the SQL_MODE parameter which has never changed in between versions and previous versions such as 2.2.x do not display the error.

If i try and query the SQL_MODE via proxysql user it returns this result:-

Any help on how we can stop this would be greatly appreciated.

[renecannao]

Hi @joetpoole .
Is your application sending the semicolon ( ; ) as part of the query?
If yes, please remove it: it is not required.

I verified this with the following.

NO error:

mysql> SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.02 sec)

Error:

mysql> DELIMITER |
mysql> SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';|
Query OK, 0 rows affected (0.03 sec)

If my guess is correct (your application sending the semicolon) , this is a bug from our end (we should strip the final semicolon if an application sends it), but should also easy to workaround on your end.

Thanks,
René

[joetpoole]

Hi @renecannao

Thanks for the prompt response, so I updated the application in question from (c#):-

var command = connection.CreateCommand();
command.CommandText = "SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';";
command.ExecuteNonQuery();

to below without the semi-colon.

var command = connection.CreateCommand();
command.CommandText = "SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'";
command.ExecuteNonQuery();

and were still seeing the same error..

2023-09-05 13:48:22 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-05 13:48:22 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-05 13:48:22 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
2023-09-05 13:48:24 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

[renecannao]

Hi @joetpoole ,
According to the error log the semicolon is still there.

[renecannao]

Closed by #4385