JDBC with ShardingSphere-Proxy not support MultiQueries

[xuup]

Bug Report

Which version of ShardingSphere did you use?

master

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

when request contains more than one sql, should execute as batch execute.

Actual behavior

In my test , when request has more sql, shardingsphere-proxy cannot execute, exception as bellow:

code :

        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3307/sharding_db?useSSL=false&allowMultiQueries=true";
        String user = "****";
        String pwd = "****";
        Connection connection = DriverManager.getConnection(url, user, pwd);
        PreparedStatement preparedStatement = connection.prepareStatement("select count(1) from t_order;select * from t_order where user_id=10");
        ResultSet resultSet = preparedStatement.executeQuery();

exception log:

Exception in thread "main" java.sql.SQLSyntaxErrorException: 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 'select * from t_order where user_id=10 UNION ALL select count(1) from t_order_1;' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019)
	at com.demo.jdbc.ShardingSphereJDBC.main(ShardingSphereJDBC.java:15)

but I communicate mysql rather than shardingsphere-proxy, the sql could be executed ,

        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/demo_ds_0?useSSL=false&allowMultiQueries=true";
        String user = "***";
        String pwd = "***";
        Connection connection = DriverManager.getConnection(url, user, pwd);
        PreparedStatement preparedStatement = connection.prepareStatement("select 1 from dual;select count(1) from t_order_0");
        ResultSet resultSet = preparedStatement.executeQuery();

I think ShardingSphere should be support allowMultiQueries feature . FYI.

I am interested this issue , and I hope I will fix it if this issue be confirmed.

[TeslaCN]

You may try setting useServerPrepStmts=false in JDBC URL of ShardingSphere-Proxy or useServerPrepStmts=true when connecting to MySQL directly.

MySQL's server prepared statement doesn't support multi queries neither. But MySQL JDBC Driver may catch exception and fallback to COM_QUERY, so you don't know there is an error occur inside the driver.

https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

[xuup]

Got it . I mean ShardingSphere-Proxy support allowMultiQueries setting like JDBC ?

I hope I can execute more than one sql in a request.

This case can be work and update tow records in JDBC with MySQL but execute error in ShardingSphere-Proxy.

        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/demo_ds_0?useSSL=false&allowMultiQueries=true";
        String user = "";
        String pwd = "";
        Connection connection = DriverManager.getConnection(url, user, pwd);
        Statement statement = connection.createStatement();
        statement.execute("update t_order_0 set content_plain = 'con22' where user_id=10;update t_order_0 set content_plain = 'con24' where user_id=12");

[TeslaCN]

You may execute batched statements by standard JDBC API. https://github.com/apache/shardingsphere/issues/16124

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.