You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/07/30 07:52:56 UTC
[GitHub] [shardingsphere] Simbafa opened a new issue #6527: Mysql limit does not support variant
Simbafa opened a new issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527
## 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).
```java
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();
}
}
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666334740
Can you update the Connect-Java from 5.1.42 to 5.1.47?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666332038
```
|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)
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666340545
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_isolat
ion 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)
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666290741
@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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666317507
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"
![image](https://user-images.githubusercontent.com/24643893/88918998-fa0b5800-d29c-11ea-97d3-d14c3f32d32e.png)
Can you use wireshark to capture what happened?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa edited a comment on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa edited a comment on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666294162
I have tried connecting to MySql directly, It's OK.
Is "useServerPrepStmts=true" parameter used?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666346445
I tested MySql:5.7, "transaction_isolation" issue disappear.
But "Statement parameter 1 not set" error still exist.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666343130
What is your MySQL server version?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666280812
![bug](https://user-images.githubusercontent.com/37646009/88912002-3769e880-d291-11ea-8306-1e3bcdd9e7f0.jpg)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666354946
I downloaded the tar file, and tested.
The issue also exists.
And I tested MySql 5.7, it also has the issue.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666294162
I have tried connecting to MySql directly, It's OK.
Is "useServerPrepStmts=true" parameter is used?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666880324
Can you please try 5.0.0-SNAPSHOT of master branch?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666874481
I can't reproduce it with sharding strategy.
What is your strategy and config-*.yaml?
What's the server.yaml?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dongzl closed issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
dongzl closed issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666336077
Have you modified any codes of ShardingSphere-Proxy?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666250269
did you try:
```
select * from sys_migrate limit ?,?
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666889847
I have reproduced this problem with you configuration.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666343935
Can you change to MySQL server and have a test?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666340961
5.1.47 is used both in client and sharding-proxy.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666258771
I tried it.
It has same issue.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666887334
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666274307
@Simbafa Please set sql.show: true and show me the log of ShardingSphere-Proxy.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Simbafa commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
Simbafa commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666343480
mariadb:10.5
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6527: Mysql limit does not support variant
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6527:
URL: https://github.com/apache/shardingsphere/issues/6527#issuecomment-666346011
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org