proxysql 2.0.1 jdbc mariadb sql_mode rewrite results in invalid sql query

[benconnito]

when connecting to proxysql 2.0.1 via jdbc mariadb 2.3.0 connector i am getting

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cat(@@sql_mode,',strict_trans_tables')' at line 1

ubuntu 16.04
proxysql 2.0.1

previously in 2.0.0-rc2 the query would look like

set autocommit=1, sql_mode = concat(@@sql_mode,\',STRICT_TRANS_TABLES\')

now in 2.0.1 our server logs

set autocommit=1, sql_mode = c1 cat(@@sql_mode,\',strict_trans_tables\')

note that concat is now c1 cat

[csokafor]

@benconnito Please any update on this issue

[woopstar]

We seem to see this in 2.0.2 too

2019-03-19 14:24:59 MySQL_Session.cpp:3278:handler(): [WARNING] Error during query on (10,192.168.1.45,7033): 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 'cat(@@sql_mode,',strict_trans_tables')' at line 1

[woopstar]

To reproduce the problem, you can use this java tester:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.mariadb.jdbc.Driver;

public class ProxySQLTester {
    public static void main(String[] args) throws Exception {
        Driver driver = new Driver();
        Properties props = new Properties();
        ResultSet rs;
        String variable_value;
        Connection conn = null;
        String JDBC_URL = "jdbc:mysql://address=(protocol=tcp)(host=192.168.1.100)(port=3306)";

        props.put("useSSL", "false");
        props.put("usePipelineAuth", "false");
        props.put("user", "root");
        props.put("password", "root");

        System.out.println("\n------------ MariaDB Connector/J and ProxySQL Testing ------------\n");

        System.out.println("Trying connection...");
        try {
            conn = driver.connect(JDBC_URL, props);
        }
        catch (SQLException e) {
            System.out.println("Connection Failed!");
            System.out.println("Error cause: "+e.getCause());
            System.out.println("Error message: "+e.getMessage());
            return;
        }

        System.out.println("Connection established...");

        for(int i=1; i <= 50; i++) {
            System.out.println("\nQuery "+i+": ");
            // Read write query that can be performed ONLY on master server
            System.out.println("Read Write query...");
            try {
                rs = conn.createStatement().executeQuery("select (select variable_value from information_schema.global_variables where variable_name=\'hostname\') || \' on port \' || (select variable_value from information_schema.global_variables where variable_name=\'port\') variable_value for update");
                while (rs.next()) {
                    variable_value = rs.getString("variable_value");
                    System.out.println("variable_value : " + variable_value);
                }
            }
            catch (SQLException e) {
                System.out.println("Read/write query has failed...");
            }


            // Read Only statement (that can also be done on master server if all slaves are down)
            System.out.println("Read Only query...");

            try {
                rs = conn.createStatement().executeQuery("select (select variable_value from information_schema.global_variables where variable_name=\'hostname\') || \' on port \' || (select variable_value from information_schema.global_variables where variable_name=\'port\') variable_value");
                while (rs.next()) {
                        variable_value = rs.getString("variable_value");
                        System.out.println("variable_value : " + variable_value);
                }
            }
            catch (SQLException e) {
                System.out.println("Read only query has failed...");
            }

            Thread.sleep(1000);
        }
        conn.close();
    }
}

$ javac -cp ".:mariadb-java-client-2.4.1.jar" ProxySQLTester.java
$ java -cp ".:mariadb-java-client-2.4.1.jar" ProxySQLTester

------------ MariaDB Connector/J and ProxySQL Testing ------------

Trying connection...
Connection Failed!
Error cause: java.sql.SQLException: 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 'cat(@@sql_mode,',strict_trans_tables')' at line 1
Error message: 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 'cat(@@sql_mode,',strict_trans_tables')' at line 1

This has been tested with:

ProxySQL 2.0.2
MariaDB Connector 2.4.1

UPDATE:
It seems to work with MariaDB Connector 1.5.9 (We have been testing all versions, and this seems to be the last one working)

[woopstar]

UPDATE 2:

Setting jdbcCompliantTruncation to false mitigates the issue. But setting this to false seems like a bad option.

[marcel-dias]

hey @renecannao
The MariaDB JDBC Driver version 2.4.1 tries to execute the following statement to get the server parameters

SHOW VARIABLES WHERE Variable_name in ('max_allowed_packet','system_time_zone','time_zone','auto_increment_increment');

Could you implement that answer in the ProxySQL Admin ?

[renecannao]

@woopstar : thank you for the reproducible test case. Unfortunately I haven't try it yet.
@marceldiass : this seems unrelated to this issue. Can you please open a new one?
Thanks

[marcel-dias]

hey @renecannao
Sure, done #2009

[renecannao]

Fixed in 2.0.4 .
Thank you for the report

[ujmoser]

We are using proxysql 2.0.8 and get the same error using Connector/J 2.4.3