Mysql limit does not support variant #6527

Simbafa · 2020-07-30T07:40:38Z

Bug Report

Which version of ShardingSphere did you use?

The latest version: Shardingsphere-ui-4.1.1

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

ShardingSphere-Proxy

Expected behavior

limit/offset can use variants.

Actual behavior

ERROR: Statement parameter 1 not set., SqlState: S1009, code:0

Reason analyze (If you can)

I searched internet, someone says that Mysql does not support limit variant before 5.5 version.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

    static void testLimit() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url ="jdbc:mysql://192.168.1.46:3307/test?user=root&password=test&serverTimezone=UTC&useSSL=false&useServerPrepStmts=true";
        //url = "jdbc:mysql://192.168.0.108:3306/test?user=root&password=test&zeroDateTimeBehavior=convertToNull&useServerPrepStmts=true";                                                                                                                                    
        //String user= java.util.Base64.getEncoder().encodeToString(url.getBytes("UTF-8"));
        //url ="jdbc:mysql://192.168.1.46:3307/test?user="+user+"&password=test&useServerPrepStmts=true";
        Connection conn = DriverManager.getConnection(url);
        List<Object> params = new ArrayList<Object>();
        params.add(20); params.add(0); 
        selectSql(conn, "select * from sys_migrate ", params, 0); 
        selectSql(conn, "select * from sys_migrate limit ? offset ?", params, 2); 
    }   


    static void selectSql(Connection conn, String sql, List<Object> paramValues, int paramValuesCount) throws SQLException {
        PreparedStatement stmt = null;
        try {
            System.out.println("selectSql PREPARE: " + sql);
            stmt = conn.prepareStatement(sql);
            paramValuesCount = paramValues == null ? 0 : paramValuesCount;
            for (int i = 0; i < paramValuesCount; i++) {
                stmt.setObject(i + 1, paramValues.get(i));
            }
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= count; i++) {
                    String key = rsmd.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    System.out.println("key:" + key + ", value:" + (value==null?"null":value));
                }
            }
            rs.close();
        } catch (SQLException e) {
            System.out.println("===Error:" + e.getMessage()+", SqlState: " + e.getSQLState() + ", code:" + e.getErrorCode());
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

kimmking · 2020-07-30T09:14:22Z

did you try:

select * from sys_migrate limit ?,?

Simbafa · 2020-07-30T09:31:38Z

I tried it.
It has same issue.

tuohai666 · 2020-07-30T10:04:36Z

@Simbafa Please set sql.show: true and show me the log of ShardingSphere-Proxy.

Simbafa · 2020-07-30T10:19:48Z

tuohai666 · 2020-07-30T10:42:59Z

@Simbafa I can't reproduce this problem. Can you use the existing codes to connect directly to a MySQL server, and find whether there's the same problem?

Simbafa · 2020-07-30T10:50:26Z

I have tried connecting to MySql directly, It's OK.
Is "useServerPrepStmts=true" parameter used?

tuohai666 · 2020-07-30T11:47:22Z

Yes. I have tried:
"jdbc:mysql://127.0.0.1:3307/sharding_db?serverTimezone=UTC&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true" and
"jdbc:mysql://127.0.0.1:3307/sharding_db?serverTimezone=UTC&useSSL=false&useServerPrepStmts=true"

Can you use wireshark to capture what happened?

Simbafa · 2020-07-30T12:23:02Z

|000000f0| 20 4b 45 59 20 28 66 69 64 29 0a 29 3b 0a 0a 0a | KEY (fid).);...|
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.942 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 90 02 00 06 00 00 15 6d 61 69 6e 2f 44 62 74 61 |.......main/Dbta|
|00000010| 62 6c 65 3a 6d 65 74 61 49 6e 66 6f fc 75 02 7b |ble:metaInfo.u.{|
|00000020| 22 63 6f 6c 75 6d 6e 73 22 3a 7b 22 43 38 46 38 |"columns":{"C8F8|
|00000030| 41 45 35 34 43 46 44 30 30 30 30 31 37 41 31 43 |AE54CFD000017A1C|
|00000040| 46 44 46 30 31 35 30 30 31 46 31 34 22 3a 7b 22 |FDF015001F14":{"|
|00000050| 63 6f 6c 75 6d 6e 4e 61 6d 65 22 3a 22 66 69 64 |columnName":"fid|
|00000060| 22 2c 22 64 61 74 61 54 79 70 65 22 3a 22 53 74 |","dataType":"St|
|00000070| 72 69 6e 67 22 7d 2c 22 43 38 46 38 42 36 46 32 |ring"},"C8F8B6F2|
|00000080| 38 45 39 30 30 30 30 31 45 45 42 31 43 32 43 42 |8E900001EEB1C2CB|
|00000090| 42 46 30 30 32 43 46 30 22 3a 7b 22 63 6f 6c 75 |BF002CF0":{"colu|
|000000a0| 6d 6e 4e 61 6d 65 22 3a 22 66 62 6c 6f 62 22 2c |mnName":"fblob",|
|000000b0| 22 64 61 74 61 54 79 70 65 22 3a 22 52 69 63 68 |"dataType":"Rich|
|000000c0| 54 65 78 74 22 2c 22 65 78 74 54 79 70 65 22 3a |Text","extType":|
|000000d0| 22 52 69 63 68 54 65 78 74 22 7d 2c 22 43 38 46 |"RichText"},"C8F|
|000000e0| 38 42 37 30 37 38 41 32 30 30 30 30 31 45 32 36 |8B7078A200001E26|
|000000f0| 39 38 32 44 43 34 41 41 30 41 37 30 30 22 3a 7b |982DC4AA0A700":{|
|00000100| 22 63 6f 6c 75 6d 6e 4e 61 6d 65 22 3a 22 66 62 |"columnName":"fb|
|00000110| 6c 6f 62 6b 36 36 65 38 31 39 22 2c 22 64 61 74 |lobk66e819","dat|
|00000120| 61 54 79 70 65 22 3a 22 52 69 63 68 54 65 78 74 |aType":"RichText|
|00000130| 22 2c 22 65 78 74 54 79 70 65 22 3a 22 52 69 63 |","extType":"Ric|
|00000140| 68 54 65 78 74 22 7d 2c 22 43 38 46 38 41 45 35 |hText"},"C8F8AE5|
|00000150| 35 38 34 35 30 30 30 30 31 45 32 38 42 44 37 41 |584500001E28BD7A|
|00000160| 32 31 44 41 37 31 43 34 32 22 3a 7b 22 63 6f 6c |21DA71C42":{"col|
|00000170| 75 6d 6e 4e 61 6d 65 22 3a 22 66 61 22 2c 22 64 |umnName":"fa","d|
|00000180| 61 74 61 54 79 70 65 22 3a 22 53 74 72 69 6e 67 |ataType":"String|
|00000190| 22 2c 22 65 78 74 54 79 70 65 22 3a 22 53 74 72 |","extType":"Str|
|000001a0| 69 6e 67 22 7d 2c 22 43 38 46 38 41 45 35 35 43 |ing"},"C8F8AE55C|
|000001b0| 46 39 30 30 30 30 31 37 46 41 33 43 44 43 41 32 |F9000017FA3CDCA2|
|000001c0| 37 36 33 31 31 46 46 22 3a 7b 22 63 6f 6c 75 6d |76311FF":{"colum|
|000001d0| 6e 4e 61 6d 65 22 3a 22 66 62 22 2c 22 64 61 74 |nName":"fb","dat|
|000001e0| 61 54 79 70 65 22 3a 22 53 74 72 69 6e 67 22 2c |aType":"String",|
|000001f0| 22 65 78 74 54 79 70 65 22 3a 22 53 74 72 69 6e |"extType":"Strin|
|00000200| 67 22 7d 2c 22 43 38 46 38 41 45 35 36 33 30 45 |g"},"C8F8AE5630E|
|00000210| 30 30 30 30 31 35 30 38 43 31 37 32 30 44 42 37 |00001508C1720DB7|
|00000220| 30 31 30 42 42 22 3a 7b 22 63 6f 6c 75 6d 6e 4e |010BB":{"columnN|
|00000230| 61 6d 65 22 3a 22 66 63 22 2c 22 64 61 74 61 54 |ame":"fc","dataT|
|00000240| 79 70 65 22 3a 22 53 74 72 69 6e 67 22 2c 22 65 |ype":"String","e|
|00000250| 78 74 54 79 70 65 22 3a 22 53 74 72 69 6e 67 22 |xtType":"String"|
|00000260| 7d 7d 2c 22 6d 6f 64 65 6c 4e 61 6d 65 22 3a 22 |}},"modelName":"|
|00000270| 44 62 74 61 62 6c 65 22 2c 22 74 61 62 6c 65 4e |Dbtable","tableN|
|00000280| 61 6d 65 22 3a 22 6d 61 69 6e 5f 64 62 74 61 62 |ame":"main_dbtab|
|00000290| 6c 65 22 7d                                     |le"}            |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.944 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 05 00 00 07 fe 00 00 02 00                      |.........       |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.945 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Read from client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 05 00 00 00 19 01 00 00 00                      |.........       |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.946 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Read from client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 25 00 00 00 16 73 65 6c 65 63 74 20 2a 20 66 72 |%....select * fr|
|00000010| 6f 6d 20 73 79 73 5f 6d 69 67 72 61 74 65 20 6c |om sys_migrate l|
|00000020| 69 6d 69 74 20 3f 20 2c 3f                      |imit ? ,?       |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.946 [ShardingSphere-Command-0] o.a.s.p.f.m.c.MySQLCommandExecutorFactory - Execute packet type: COM_STMT_CLOSE, value: MySQLComStmtClosePacket(statementId=1)
[DEBUG] 20:21:16.949 [ShardingSphere-Command-0] o.a.s.p.f.m.c.MySQLCommandExecutorFactory - Execute packet type: COM_STMT_PREPARE, value: MySQLComStmtPreparePacket(sql=select * from sys_migrate limit ? ,?)
[DEBUG] 20:21:16.951 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 0c 00 00 01 00 02 00 00 00 01 00 02 00 00 00 00 |................|
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.952 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 17 00 00 02 03 64 65 66 00 00 00 01 3f 00 0c 21 |.....def....?..!|
|00000010| 00 00 00 00 00 fd 00 00 00 00 00                |...........     |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.952 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 17 00 00 03 03 64 65 66 00 00 00 01 3f 00 0c 21 |.....def....?..!|
|00000010| 00 00 00 00 00 fd 00 00 00 00 00                |...........     |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.953 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 05 00 00 04 fe 00 00 02 00                      |.........       |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.953 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 16 00 00 05 03 64 65 66 00 00 00 00 00 0c 21 00 |.....def......!.|
|00000010| 00 00 00 00 fd 00 00 00 00 00                   |..........      |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.953 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 05 00 00 06 fe 00 00 02 00                      |.........       |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.955 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Read from client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 18 00 00 00 17 02 00 00 00 00 01 00 00 00 00 01 |................|
|00000010| 03 00 03 00 00 00 00 00 14 00 00 00             |............    |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.957 [ShardingSphere-Command-0] o.a.s.p.f.m.c.MySQLCommandExecutorFactory - Execute packet type: COM_STMT_EXECUTE, value: MySQLComStmtExecutePacket(sql=select * from sys_migrate limit ? ,?, parameters=[0, 20])
[INFO ] 20:21:16.957 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from sys_migrate limit ? ,?
[INFO ] 20:21:16.957 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@67b183a1, tablesContext=TablesContext(tables=[]))
[INFO ] 20:21:16.957 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from sys_migrate limit ? ,?
[DEBUG] 20:21:16.966 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 27 00 00 01 ff 00 00 23 53 31 30 30 39 53 74 61 |'......#S1009Sta|
|00000010| 74 65 6d 65 6e 74 20 70 61 72 61 6d 65 74 65 72 |tement parameter|
|00000020| 20 31 20 6e 6f 74 20 73 65 74 2e                | 1 not set.     |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.974 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Read from client 107f857a : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 05 00 00 00 19 02 00 00 00                      |.........       |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:21:16.974 [ShardingSphere-Command-0] o.a.s.p.f.m.c.MySQLCommandExecutorFactory - Execute packet type: COM_STMT_CLOSE, value: MySQLComStmtClosePacket(statementId=2)

tuohai666 · 2020-07-30T12:29:10Z

Can you update the Connect-Java from 5.1.42 to 5.1.47?

tuohai666 · 2020-07-30T12:32:06Z

Have you modified any codes of ShardingSphere-Proxy?

Simbafa · 2020-07-30T12:41:47Z

MySQLPacketCodecEngine.java is modified, because sometimes resetReaderIndex() is called.
But using the test code, it dosen't occur.
I just add "System.out.println()" in decode method

    @Override
    public void decode(final ChannelHandlerContext context, final ByteBuf in, final List<Object> out, final int readableBytes) {
        int payloadLength = in.markReaderIndex().readMediumLE();
        int realPacketLength = payloadLength + MySQLPacket.PAYLOAD_LENGTH + MySQLPacket.SEQUENCE_LENGTH;
        if (readableBytes < realPacketLength) {
            System.out.println("====================resetReaderIndex==============");
            in.resetReaderIndex();
            return;
        }
        out.add(in.readRetainedSlice(payloadLength + MySQLPacket.SEQUENCE_LENGTH));
    }

When using 5.1.47, "java.sql.SQLException: Unknown system variable 'transaction_isolation'" is thrown.

|00000340| 61 63 74 69 6f 6e 5f 69 73 6f 6c 61 74 69 6f 6e |action_isolation|
|00000350| 20 41 53 20 74 72 61 6e 73 61 63 74 69 6f 6e 5f | AS transaction_|
|00000360| 69 73 6f 6c 61 74 69 6f 6e 2c 20 40 40 77 61 69 |isolation, @@wai|
|00000370| 74 5f 74 69 6d 65 6f 75 74 20 41 53 20 77 61 69 |t_timeout AS wai|
|00000380| 74 5f 74 69 6d 65 6f 75 74                      |t_timeout       |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:33:23.980 [ShardingSphere-Command-0] o.a.s.p.f.m.c.MySQLCommandExecutorFactory - Execute packet type: COM_QUERY, value: MySQLComQueryPacket(sql=/* mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout)
[INFO ] 20:33:24.611 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: /* mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
[INFO ] 20:33:24.611 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@a76834c, tablesContext=TablesContext(tables=[]))
[INFO ] 20:33:24.612 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_0 ::: /* mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
[DEBUG] 20:33:24.636 [epollEventLoopGroup-3-1] o.a.s.db.protocol.codec.PacketCodec - Write to client 3e8de303 : 
          +-------------------------------------------------+
         |  0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f |
+--------+-------------------------------------------------+----------------+
|00000000| 38 00 00 01 ff a9 04 23 48 59 30 30 30 55 6e 6b |8......#HY000Unk|
|00000010| 6e 6f 77 6e 20 73 79 73 74 65 6d 20 76 61 72 69 |nown system vari|
|00000020| 61 62 6c 65 20 27 74 72 61 6e 73 61 63 74 69 6f |able 'transactio|
|00000030| 6e 5f 69 73 6f 6c 61 74 69 6f 6e 27             |n_isolation'    |
+--------+-------------------------------------------------+----------------+
[DEBUG] 20:33:47.941 [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Before cleanup stats (total=1, active=0, idle=1, waiting=0)
[DEBUG] 20:33:47.943 [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After cleanup  stats (total=1, active=0, idle=1, waiting=0)
[DEBUG] 20:34:17.944 [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Before cleanup stats (total=1, active=0, idle=1, waiting=0)

Simbafa · 2020-07-30T12:42:43Z

5.1.47 is used both in client and sharding-proxy.

tuohai666 · 2020-07-30T12:47:24Z

What is your MySQL server version?

Simbafa · 2020-07-30T12:48:13Z

mariadb:10.5

tuohai666 · 2020-07-30T12:49:09Z

Can you change to MySQL server and have a test?

tuohai666 · 2020-07-30T12:53:29Z

I suggest you to download a binary version of Proxy to do this test. The download page as follows:

https://www.apache.org/dyn/closer.cgi/shardingsphere/4.1.1/apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz

Simbafa · 2020-07-30T12:54:24Z

I tested MySql:5.7, "transaction_isolation" issue disappear.
But "Statement parameter 1 not set" error still exist.

Simbafa · 2020-07-30T13:10:36Z

I downloaded the tar file, and tested.
The issue also exists.
And I tested MySql 5.7, it also has the issue.

tuohai666 · 2020-07-31T02:16:57Z

I can't reproduce it with sharding strategy.
What is your strategy and config-*.yaml?
What's the server.yaml?

tuohai666 · 2020-07-31T02:38:11Z

Can you please try 5.0.0-SNAPSHOT of master branch?

Simbafa · 2020-07-31T03:04:33Z

server.yaml

 22 ######################################################################################################                                                                                                                                                                    
 23 #                          
 24 #orchestration:
 25 #  orchestration_ds:       
 26 #    orchestrationType: registry_center,config_center,metadata_center                                                                                                                                                                                                     
 27 #    instanceType: zookeeper
 28 #    serverLists: localhost:2181
 29 #    namespace: orchestration
 30 #    props:
 31 #      overwrite: false
 32 #      retryIntervalMilliseconds: 500
 33 #      timeToLiveSeconds: 60
 34 #      maxRetries: 3
 35 #      operationTimeoutMilliseconds: 500                                                                                                                                                                                                                                  
 36 #
 37 authentication:
 38   users:
 39     root:
 40       password: test
 41 #    sharding:
 42 #      password: sharding 
 43 #      authorizedSchemas: sharding_db                                                                                                                                                                                                                                     
 44 #
 45 props:
 46   max.connections.size.per.query: 1
 47   acceptor.size: 1  # The default value is available processors count * 2.                                                                                                                                                                                                
 48   executor.size: 1  # Infinite by default.
 49   proxy.frontend.flush.threshold: 128 # The default value is 128.
 50 #    # LOCAL: Proxy will run with LOCAL transaction.
 51 #    # XA: Proxy will run with XA transaction.
 52 #    # BASE: Proxy will run with B.A.S.E transaction.
 53   proxy.transaction.type: LOCAL
 54   proxy.opentracing.enabled: false
 55 #  proxy.hint.enabled: false
 56 #  query.with.cipher.column: true 
 57   sql.show: true
 58 #  allow.range.query.with.inline.sharding: false
 59 #  check.table.metadata.enabled: false

config-sharding.yaml

 23 # If you want to use sharding, please refer to this file;                                                                                                                                                                                                                 
 24 # if you want to use master-slave, please refer to the config-master_slave.yaml.
 25 #                          
 26 ######################################################################################################
 27 #
 28 schemaName: test
 29 
 30 dataSources:
 31   ds_0:
 32     url: jdbc:mysql://192.168.1.46:3306/test?useServerPrepStmts=true&zeroDateTimeBehavior=convertToNull
 33     username: root
 34     password: test
 35     connectionTimeoutMilliseconds: 30000
 36     idleTimeoutMilliseconds: 60000  
 37     maxLifetimeMilliseconds: 1800000
 38     maxPoolSize: 50
 39 #
 40 #schemaName: sharding_db
 41 #
 42 #dataSourceCommon:
 43 #  username: postgres
 44 #  password: postgres
 45 #  connectionTimeoutMilliseconds: 30000
 46 #  idleTimeoutMilliseconds: 60000

I will try 5.0.0-SNAPSHOT

tuohai666 · 2020-07-31T03:14:37Z

I have reproduced this problem with you configuration.

* #6527, parameters for transparent mode * #6527, refine

* apache#6527, parameters for transparent mode * apache#6527, refine

* Visit CreateDataSourcesStatement * add setter * Modify interface for DatabaseTypes * add insert select and replace select statement doc (#6521) * add insert select statement doc * add replace select statement doc * modify doc description * Fix #3954 : Sharding-JDBC querying support PostgreSQL array type (#6524) * Sharding-JDBC support PostgreSQL array type * Sharding-JDBC support PostgreSQL array type * Sharding-JDBC support PostgreSQL array type, try to fix ci error * check style * Refactor sqlStatement context converter * Rename ProxyConfigurationLoader (#6540) * refactor OrchestrationShardingSphereDataSource * refactor OrchestrationShardingSphereDataSource * refactor OrchestrationShardingSphereDataSource * Rename ProxyConfigurationLoader * Refactor ProxyConfigurationLoader * Refactor ProxyConfigurationConverter * Fix javadoc * Fix javadoc * Refactor OrchestrationShardingSphereDataSource * Refactor OrchestrationShardingSphereDataSource * #6294 oracle support select for update (#6538) * #6527, parameters for transparent mode (#6545) * #6527, parameters for transparent mode * #6527, refine * update release guide of elasticjob (#6549) * support select '' as a from order (#6542) * Adjust executor and engine package directory (#6552) * Create ShardingSphereSQLParserEngine and factory (#6553) * Adjust sql engine package dir (#6554) * Add BootstrapArguments (#6555) * Refactor ProxyConfigurationConverterFactory * Refactor ProxyConfigurationLoader * Adjust private method order of Bootstrap * Add BootstrapArguments * use ShardingSphereSQLParserEngine instead of SQLParserEngine (#6557) * Refactor Bootstrap's log (#6559) * Refactor Bootstrap * Refactor Bootstrap's log * fix docs (#6560) * Remove ProxyConfigurationConverter (#6564) * rename Bootstrap.init * Refactor Bootstrap.log * Remove useless javadoc * Add final * Revise ProxyConfiguration * Revise DataSourceParameter * Revise DataSourceParameter * Refactor OrchestrationConfigurationConverter * Add OrchestrationBootstrap * Add YamlProxyConfigurationSwapper * Remove ProxyConfigurationConverter * Refactor Bootstrap * Refactor Bootstrap * Refactor Bootstrap * Remove Serializable for ProxyConfiguration * refactor OrchestrationBootstrap * For checkstyle * Add orchestrationEnabled for Bootstrap (#6565) * Refactor Bootstrap * Add orchestrationEnabled for Bootstrap * update release guide about docker (#6566) * docker release guide for elasticjob (#6567) * docker release for elasticjob * docker release guide for elasticjob ui * Create maven.yml (#6563) * Remove ShardingSphereSchema.dataSourceParameters (#6569) * Decouple ShardingSphereSchema.dataSourceParameters * Remove ShardingSphereSchema.dataSourceParameters * Add ProxyDataSourceContext * Refactor SchemaContextsBuilder * Visit CreateDataSourcesStatement * Adjust executor and engine package directory (#6552) * Adjust sql engine package dir (#6554) * Adjust executor and engine package directory (#6552) * Adjust sql engine package dir (#6554) * Adjust executor and engine package directory (#6552) * Adjust sql engine package dir (#6554) * Rename "unfinish" to "unfinished"(#6571) * Rename "unfinish" to "unfinished"(#6571) Co-authored-by: tristaZero <[email protected]> Co-authored-by: DuanZhengqiang <[email protected]> Co-authored-by: sandynz <[email protected]> Co-authored-by: Liang Zhang <[email protected]> Co-authored-by: zangyk <[email protected]> Co-authored-by: Zhang Yonglun <[email protected]> Co-authored-by: cmonkey <[email protected]> Co-authored-by: Juan Pan(Trista) <[email protected]> Co-authored-by: kimmking <[email protected]> Co-authored-by: qiulu3 <Lucas209910>

kimmking added in: proxy db: MySQL labels Jul 30, 2020

kimmking added this to the 5.0.0-alpha milestone Jul 30, 2020

kimmking assigned tuohai666 Jul 30, 2020

tuohai666 added a commit to tuohai666/shardingsphere that referenced this issue Jul 31, 2020

apache#6527, parameters for transparent mode

41cc38e

tuohai666 mentioned this issue Jul 31, 2020

#6527, parameters for transparent mode #6545

Merged

tuohai666 added the type: bug label Jul 31, 2020

tuohai666 added a commit to tuohai666/shardingsphere that referenced this issue Jul 31, 2020

apache#6527, refine

466adf5

dongzl closed this as completed in #6545 Jul 31, 2020

dongzl pushed a commit that referenced this issue Jul 31, 2020

#6527, parameters for transparent mode (#6545)

ca2e33a

* #6527, parameters for transparent mode * #6527, refine

Lucas-307 pushed a commit to Lucas-307/shardingsphere that referenced this issue Aug 3, 2020

apache#6527, parameters for transparent mode (apache#6545)

faa669e

* apache#6527, parameters for transparent mode * apache#6527, refine

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Mysql limit does not support variant #6527

Mysql limit does not support variant #6527

Simbafa commented Jul 30, 2020

kimmking commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020 •

edited

Loading

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 31, 2020

tuohai666 commented Jul 31, 2020

Simbafa commented Jul 31, 2020

tuohai666 commented Jul 31, 2020

Mysql limit does not support variant #6527

Mysql limit does not support variant #6527

Comments

Simbafa commented Jul 30, 2020

Bug Report

Which version of ShardingSphere did you use?

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

Expected behavior

Actual behavior

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

kimmking commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020 • edited Loading

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

tuohai666 commented Jul 30, 2020

Simbafa commented Jul 30, 2020

Simbafa commented Jul 30, 2020

tuohai666 commented Jul 31, 2020

tuohai666 commented Jul 31, 2020

Simbafa commented Jul 31, 2020

tuohai666 commented Jul 31, 2020

Simbafa commented Jul 30, 2020 •

edited

Loading