Receiving errors after executing SET SQL_MODE command

[markosutic]

Hello,

OS: Ubuntu 18.04 ProxySQL: ProxySQL version 2.0.8-67-g877cab1e, codename Truls DB: MariaDB Galera Cluster 10.2.26

We are having ERROR 1064 after successfully running command: set autocommit=1, session_track_schema=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES'),net_write_timeout=1800;

Test:

MariaDB [(none)]> set autocommit=1, session_track_schema=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES'),net_write_timeout=1800;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select count(*) from information_schema.tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

ProxySQL log:

019-12-11 19:58:33 MySQL_Session.cpp:2477:handler_again___status_SETTING_GENERIC_VARIABLE(): [WARNING] Error while setting SQL_MODE to "concat(@@sql_mode,',STRICT_TRANS_TABLES'),net_write_timeout": 1064, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
2019-12-11 19:58:33 MySQL_Session.cpp:128:kill_query_thread(): [WARNING] KILL CONNECTION 4510 on 10.1.0.4:3306

Application log:

2019-12-11T19:14:01,294][ERROR][logstash.inputs.jdbc     ] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaSql::SQLNonTransientConnectionException: Could not connect to address=(host=10.9.0.6)(port=3305)(type=master) : could not load system variables"}
{ 2008 rufus-scheduler intercepted an error:
  2008   job:
  2008     Rufus::Scheduler::CronJob "* * * * *" {}
  2008   error:
  2008     2008
  2008     Sequel::DatabaseConnectionError
  2008     Java::JavaSql::SQLNonTransientConnectionException: Could not connect to address=(host=10.9.0.6)(port=3305)(type=master) : could not load system variables

When running query excluding SQL_MODE everything works fine.

MariaDB [(none)]> set autocommit=1, session_track_schema=1,net_write_timeout=1800;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|      327 |
+----------+
1 row in set (0.01 sec)

We don't have any problems when running queries directly on the database.

Can you please explain how can we workaround this problem?

Thanks

Best regards, Marko

[renecannao]

I confirm the issue. There are actually a series of issues here, 2 related to the parser and 1 related to session tracking. To make it work, you need to create this rule:

INSERT INTO mysql_query_rules (active,match_pattern,replace_pattern) VALUES (1,"session_track_schema=1, sql_mode = concat\(@@sql_mode,',STRICT_TRANS_TABLES'\),net_write_timeout=1800","net_write_timeout=1800, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')");

Please make attention to whitespaces! Also, the above query rewrite disables session_track_schema : ProxySQL doesn't support it.

[markosutic]

Hello Rene, Thank you for a quick response. Workaround you noted solves our issue.

Best regards, Marko

[renecannao]

Hi Marko,

Glad to hear the workaround worked. I am reopening the issue, because this is still a bug even if a workaround exists. Thanks

[markuman]

I just wanted to add a note that we run into the same error when we're trying to use proxysql 2.1.1, while 2.0.16 works fine (yeah, that's strange).

MySQL_Session.cpp:7054:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.127.38.185:64693. Setting lock_hostgroup. Please report a bug for future enhancements:set autocommit=1, session_track_schema=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')

[renecannao]

Hi @markuman . Please consider the behavior in 2.1.1 to be the correct one. 2.0.16 wasn't working fine, it was just not detecting the real issue. Please use the query rule suggested above. Thanks

[markuman]

here is an ansible task from the above suggestion for our error msg.

    - name: eleminate session_track_schema=1 from mariadb jdbc connections
      proxysql_query_rules:
        match_pattern: session_track_schema=1, sql_mode = concat\(@@sql_mode,',STRICT_TRANS_TABLES'\)
        replace_pattern: sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')
        active: 1
        retries: 3
        state: present
        load_to_runtime: yes
        save_to_disk: yes