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 2021/01/22 06:45:19 UTC
[GitHub] [shardingsphere] tristaZero opened a new issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
tristaZero opened a new issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127
After #9114, the IT engine fails to run well with the `SELECT DISTINCT` SQL. It is necessary to find the cause and fix it.
----------------------------------------------------------------
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 closed issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
tuohai666 closed issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127
----------------------------------------------------------------
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] JiekerTime commented on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
JiekerTime commented on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-765178904
Hi~ I will check it !
----------------------------------------------------------------
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 #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-768289981
Got it. Thanks for the details.
----------------------------------------------------------------
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] JiekerTime commented on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
JiekerTime commented on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-768223047
When you use Proxy and add the useServerPrepStmts=true parameter to the URL, as long as the form of SUM() or COUNT(A+B) is used, it will cause a forced transfer exception at the bottom layer.
For specific proxy configuration, please refer to shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/env/db/rules.yaml
SQL statement reference "SELECT SUM(DISTINCT order_id+user_id) FROM t_order WHERE order_id <1100"
For database structure, please refer to: shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/env/db/init-sql/mysql/init.sql
----
Or you can try to run the integration test locally.
----------------------------------------------------------------
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] tristaZero commented on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-765172664
@JiekerTime Are you interested in this one?
----------------------------------------------------------------
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] JiekerTime commented on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
JiekerTime commented on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-768220150
When you use Proxy and add the useServerPrepStmts=true parameter to the URL, as long as the form of SUM() or COUNT(A+B) is used, it will cause a forced transfer exception at the bottom layer.
----------------------------------------------------------------
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] sandynz edited a comment on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
sandynz edited a comment on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-768224083
I found a similar problem when PR integration test, e.g. `Integration Test / MySQL-Proxy with DB`, part of error output:
```
Error: assertExecute[proxy: db -> MySQL -> Placeholder -> SELECT SUM(DISTINCT order_id) FROM t_order WHERE order_id < 1100](org.apache.shardingsphere.test.integration.engine.it.dql.GeneralDQLIT) Time elapsed: 0.007 s <<< FAILURE!
java.lang.AssertionError:
Size of actual result set is different with size of expected dat set rows.
Expected: is <1>
but: was <0>
```
Steps to reproduce on local environment:
1, Prepare configurations for proxy, copy config files from `shardingsphere-integration-test-suite/src/test/resrouces/docker/db/proxy/conf/` into `shardingsphere-proxy-bootstrap/src/main/resources/`
- copy `config-db.yaml` into `config-sharding.yaml`
- copy `server.yaml` into `server.yaml`
2, Update `shardingsphere-integration-test-suite/src/test/resources/env/engine-env.properties`
```
it.adapters=proxy
#it.scenarios=db,tbl,dbtbl_with_replica_query,replica_query,shadow
it.scenarios=db
#it.databases=H2,MySQL,Oracle,SQLServer,PostgreSQL
it.databases=MySQL
```
Update `shardingsphere-integration-test-suite/src/test/resources/env/db/scenario-env.properties`, `mysql` and `proxy` related configuration, e.g.
```
it.db.mysql.host=127.0.0.1
it.db.mysql.port=3306
it.db.mysql.username=root
it.db.mysql.password=
it.db.proxy.host=127.0.0.1
it.db.proxy.port=3307
```
3, Copy `org.apache.shardingsphere.proxy.Bootstrap.java` to `shardingsphere-integration-test-suite` as `ProxyBootstrapTest.java`, since `STANDARD_TEST` in `config-db.yaml` is a customized algorithm that is implemented in unit test
4, Run `ProxyBootstrapTest.java`
5, Prepare an test client and start it, e.g. `TempTest.java`
```
public class TempTest {
public static void main(String[] args) throws Exception {
String sql;
sql = "SELECT SUM(DISTINCT order_id) s FROM t_order WHERE order_id < 1100"; // result missed in response
// sql = "SELECT SUM(order_id) s FROM t_order WHERE order_id < 1100"; //ok
// sql = "SELECT COUNT(DISTINCT order_id) s FROM t_order WHERE order_id < 1100"; //ok
String jdbcUrl = "jdbc:mysql://localhost:3307/db?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&useLocalSessionState=true&characterEncoding=utf-8";
try (Connection connection = DriverManager.getConnection(jdbcUrl, "root", "root");
PreparedStatement statement = connection.prepareStatement(sql)) {
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
System.out.println("c1=" + resultSet.getString(1));
}
}
}
}
}
```
6, Run `GeneralDQLIT.java` to verify all test cases
----------------------------------------------------------------
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] JiekerTime removed a comment on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
JiekerTime removed a comment on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-768220150
When you use Proxy and add the useServerPrepStmts=true parameter to the URL, as long as the form of SUM() or COUNT(A+B) is used, it will cause a forced transfer exception at the bottom layer.
----------------------------------------------------------------
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] sandynz commented on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-768224083
I found a similar problem when PR integration test, e.g. `Integration Test / MySQL-Proxy with DB`, part of error output:
```
Error: assertExecute[proxy: db -> MySQL -> Placeholder -> SELECT SUM(DISTINCT order_id) FROM t_order WHERE order_id < 1100](org.apache.shardingsphere.test.integration.engine.it.dql.GeneralDQLIT) Time elapsed: 0.007 s <<< FAILURE!
java.lang.AssertionError:
Size of actual result set is different with size of expected dat set rows.
Expected: is <1>
but: was <0>
```
Steps to reproduce on local environment:
1, Prepare configurations for proxy, copy config files from `shardingsphere-integration-test-suite/src/test/resrouces/docker/db/proxy/conf/` into `shardingsphere-proxy-bootstrap/src/main/resources/`
- copy `config-db.yaml` into `config-sharding.yaml`
- copy `server.yaml` into `server.yaml`
2, Update `shardingsphere-integration-test-suite/src/test/resources/env/engine-env.properties`
```
it.adapters=proxy
#it.scenarios=db,tbl,dbtbl_with_replica_query,replica_query,shadow
it.scenarios=db
#it.databases=H2,MySQL,Oracle,SQLServer,PostgreSQL
it.databases=MySQL
```
Update `shardingsphere-integration-test-suite/src/test/resources/env/db/scenario-env.properties`, `mysql` and `proxy` related configuration, e.g.
```
it.db.mysql.host=127.0.0.1
it.db.mysql.port=3306
it.db.mysql.username=root
it.db.mysql.password=
it.db.proxy.host=127.0.0.1
it.db.proxy.port=3307
```
3, Copy `org.apache.shardingsphere.proxy.Bootstrap.java` to `shardingsphere-integration-test-suite` as `ProxyBootstrapTest.java`, since `STANDARD_TEST` in `config-db.yaml` is a customized algorithm that is implemented in unit test
4, Run `ProxyBootstrapTest.java`
5, Prepare an test client and start it, e.g. `TempTest.java`
```
public class TempTest {
public static void main(String[] args) throws Exception {
String sql;
sql = "SELECT SUM(DISTINCT order_id) s FROM t_order WHERE order_id < 1100"; // result missed in response
// sql = "SELECT SUM(order_id) s FROM t_order WHERE order_id < 1100"; //ok
// sql = "SELECT COUNT(DISTINCT order_id) s FROM t_order WHERE order_id < 1100"; //ok
String jdbcUrl = "jdbc:mysql://localhost:3307/db?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&useLocalSessionState=true&characterEncoding=utf-8";
try (Connection connection = DriverManager.getConnection(jdbcUrl, "root", "root");
PreparedStatement statement = connection.prepareStatement(sql)) {
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
System.out.println("c1=" + resultSet.getString(1));
}
}
}
}
}
```
----------------------------------------------------------------
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