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/04/13 02:48:55 UTC
[GitHub] [shardingsphere] glon opened a new issue #10063: Proxy broadcastTables select * error
glon opened a new issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063
> ShardingSphere Proxy 4.1.1
We get a problem when executing SQL like `select * from city;` , can't get the results.
Table city configured in broadcastTables.
```sql
> select * from city;
Query OK, 0 rows affected, 25971 warnings (0.00 sec)
> select count(*) from city;
+----------+
| count(*) |
+----------+
| 75331 |
+----------+
1 row in set (0.03 sec)
```
And other client returns with error like :
Our confi-sharding.yaml :
```
schemaName: shardingdb
dataSources:
ds_0:
url: jdbc:mysql://10.13.63.17:3306/goods?useTimezone=true&serverTimezone=Asia/Shanghai&useSSL=false
username: sharding_user
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://10.13.63.97:3306/order?useTimezone=true&serverTimezone=Asia/Shanghai&useSSL=false
username: sharding_user
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_2:
url: jdbc:mysql://10.13.63.14:3306/partner?useTimezone=true&serverTimezone=Asia/Shanghai&useSSL=false
username: sharding_user
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_3:
url: jdbc:mysql://10.13.63.19:3306/common?useTimezone=true&serverTimezone=Asia/Shanghai&useSSL=false
username: sharding_user
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
# ds_0
test_good:
actualDataNodes: ds_0.test_good
# ds_1
test_order:
actualDataNodes: ds_1.test_order
# ds_2
test_partner:
actualDataNodes: ds_2.test_partner
# ds_3
test_common:
actualDataNodes: ds_3.test_common
broadcastTables:
- Area
- City
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
```
--
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] glon closed issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon closed issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063
--
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] glon commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819268082
@strongduanmu
Our backend MySQL DB timeout setting：
```mysql
>show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 120 |
| net_write_timeout | 120 |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 120 |
+------------------------------+----------+
14 rows in set (0.01 sec)
```
And I don't think it should be the reason.
Executing SQLs behind won't take a long time :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
I can execute SQL `select * from not_broadcast_table` ，this problem only appears in broadcast tables, sharding tables do not have this 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] glon commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-826273393
I configured defaultDataSourceName: ds_0 to solved the problem, but during the process I've been tried, not work, oddly...
--
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] glon commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-826273393
I configured defaultDataSourceName: ds_0 to solved the problem, but during the process I've been tried, not work, oddly...
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819180886
1. We've set `sql.show = true`
When executing `select * from city`, could not get the answer, and the selected database will lost, like :
```sql
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
Here's the logs/stdout.log recording when executing the SQL :
```
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c958aed, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@25372307, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@35e56d5a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@55474be9, containsSubquery=false)
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[ERROR] 10:37:36.500 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.NullPointerException: null
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6300)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@46fe6fd1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7660743e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4a1ab3ef, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7fafcd9a, containsSubquery=false)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT DATABASE()
[WARN ] 10:37:36.501 [ShardingSphere-Command-5] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5328c8ea (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
server.yaml :
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max.connections.size.per.query: 9
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 128 # The default value is 128.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
2. SQL like `select id, name from city`, `insert into city (id, name) values (...)[,(...)]` work well.
`insert into city values (...)[,(...)]` does not work well.
3. Other broadcast tables have the same problem, but sharding tables work well.
---
For MySQL driver, we used mysql-connector-java-5.1.49.jar, downloaded from https://mvnrepository.com/artifact/mysql/mysql-connector-java
When we tried 8.0.*, datetime item cannot return the right format, values contains 'T'.
--
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] strongduanmu commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-820878504
> Hi @strongduanmu any progress？
@tuohai666 Can you help me look at this issue? It seems similar to #6606.
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819180886
1. We've set `sql.show = true`
When executing `select * from city`, could not get the answer, and the selected database will lost, like :
```sql
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
Here's the logs/stdout.log recording when executing the SQL :
```
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c958aed, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@25372307, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@35e56d5a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@55474be9, containsSubquery=false)
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[ERROR] 10:37:36.500 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.NullPointerException: null
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6300)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@46fe6fd1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7660743e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4a1ab3ef, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7fafcd9a, containsSubquery=false)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT DATABASE()
[WARN ] 10:37:36.501 [ShardingSphere-Command-5] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5328c8ea (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
server.yaml :
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max.connections.size.per.query: 9
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 128 # The default value is 128.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
2. SQL that specified fields like :
`select id, name from city`,
`insert into city (id, name) values (...)[,(...)]`,
`update city set name = xx where id = N`,
`delete from city where id = N`,
`truncate table city`,
add column， add index work well.
otherwise, `insert into city values (...)[,(...)]` does not work well.
3. Other broadcast tables have the same problem, but sharding tables work well.
---
For MySQL driver, we used `mysql-connector-java-5.1.49.jar`, downloaded from https://mvnrepository.com/artifact/mysql/mysql-connector-java
When we tried 8.0.*, datetime item cannot return the right format, values contains 'T'.
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819180886
1. We've set `sql.show = true`
When executing `select * from city`, could not get the answer, and the selected database will lost, like :
```
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
Here's the logs/stdout.log recording when executing the SQL :
```
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c958aed, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@25372307, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@35e56d5a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@55474be9, containsSubquery=false)
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[ERROR] 10:37:36.500 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.NullPointerException: null
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6300)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@46fe6fd1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7660743e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4a1ab3ef, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7fafcd9a, containsSubquery=false)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT DATABASE()
[WARN ] 10:37:36.501 [ShardingSphere-Command-5] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5328c8ea (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
server.yaml :
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max.connections.size.per.query: 9
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 128 # The default value is 128.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
2. SQL like `select id, name from city`, `insert into city (id, name) values (...)[,(...)]` work well.
`insert into city values (...)[,(...)]` does not work well.
3. Other broadcast tables have the same problem, but sharding tables work well.
---
For MySQL driver, we used mysql-connector-java-5.1.49.jar, downloaded from https://mvnrepository.com/artifact/mysql/mysql-connector-java
When we tried 8.0.*, datetime item cannot return the right format, values contains 'T'.
--
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 #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-818419331
Hi
1. Could you set `sql.show` = true? That way, we can get more info from its log.
2. Did other SQLs on broadcastTable or shardingTable work well?
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819268082
@strongduanmu
Our backend MySQL DB timeout setting：
```mysql
>show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 120 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 120 |
| net_write_timeout | 120 |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 120 |
+------------------------------+----------+
14 rows in set (0.01 sec)
```
And I don't think it should be the reason.
Executing SQLs behind won't take a long time :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
I can execute SQL `select * from not_broadcast_table` and get the right answer.
This problem only appears in broadcast tables, sharding tables do not have this 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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819317629
Executing SQLs :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.02 sec)
sharding_user@localhost:6033 [(none)]>
```
And it's debug level log behind, logs/stdout.log
```
[INFO ] 15:56:46.718 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select USER()
[INFO ] 15:56:46.719 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@79e971a4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a58e36), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a58e36, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=12, distinctRow=false, projections=[ExpressionProjection(expression=USER(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@263a2033, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@772f2786, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@45790c96, containsSubquery=false)
[INFO ] 15:56:46.719 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_1 ::: select USER()
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@a825ed5, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@477123c8), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@477123c8, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@bd0e9f, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@42875337, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@5a6bd944, containsSubquery=false)
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_3 ::: select * from city
[ERROR] 15:56:57.007 [ShardingSphere-Command-5] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5e580a5f, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b9131d2), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b9131d2, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@f52981e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@c1cde8d, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@120837a3, containsSubquery=false)
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_2 ::: SELECT DATABASE()
```
logback.xml :
```yaml
<configuration>
<appender name="file" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>sharding.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>sharding.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>60</maxHistory>
<!--<totalSizeCap>1GB</totalSizeCap>-->
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n</pattern>
</encoder>
</appender>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%-5level] %d{HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.apache.shardingsphere" level="info" additivity="false">
<appender-ref ref="console"/>
<appender-ref ref="file"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="console" />
<appender-ref ref="file" />
</root>
</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] strongduanmu commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819231754
@glon Thank you very much for your feedback. I tested it locally and did not reproduce your exception. 😪
Through the exception information you provided, I noticed that it may be caused by an unreasonable connection timeout. You can refer to this [article](https://lchml.com/technology/hikari-maxlifetime/) to check your connection timeout configuration.
```
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
--
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 #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-820346623
Hi @strongduanmu any progress？
--
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] glon commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-824497081
Maybe I could reproduce the exception.
When I copy my config files to a new test env, and run it, could not see the broadcast table this time.
I packaged my env and uploaded to the cloud, unpack and run the db.sql in config dir, create db user with grants, the test env should be copied.
link：https://pan.baidu.com/s/1_sEMQ8xhLHVizIRKYCRkGg
code：6666
--
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] glon commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819180886
1. We've set `sql.show = true`
When executing `select * from city`, could not get the answer, and the selected database will lost, like :
```
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
Here's the logs/stdout.log recording when executing the SQL :
```
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c958aed, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@25372307, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@35e56d5a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@55474be9, containsSubquery=false)
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[ERROR] 10:37:36.500 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.NullPointerException: null
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6300)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@46fe6fd1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7660743e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4a1ab3ef, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7fafcd9a, containsSubquery=false)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT DATABASE()
[WARN ] 10:37:36.501 [ShardingSphere-Command-5] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5328c8ea (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
server.yaml :
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max.connections.size.per.query: 9
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 128 # The default value is 128.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
2. SQL like `select id, name from city`, `insert into city (id, name) values (...)[,(...)]` work well.
`insert into city values (...)[,(...)]` does not work as well.
3. Other broadcast tables have the same problem, but sharding tables work well.
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819180886
1. We've set `sql.show = true`
When executing `select * from city`, could not get the answer, and the selected database will lost, like :
```sql
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
Here's the logs/stdout.log recording when executing the SQL :
```
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c958aed, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@25372307, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@35e56d5a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@55474be9, containsSubquery=false)
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[ERROR] 10:37:36.500 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.NullPointerException: null
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6300)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@46fe6fd1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7660743e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4a1ab3ef, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7fafcd9a, containsSubquery=false)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT DATABASE()
[WARN ] 10:37:36.501 [ShardingSphere-Command-5] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5328c8ea (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
server.yaml :
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max.connections.size.per.query: 9
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 128 # The default value is 128.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
2. SQL that specified fields like
`select id, name from city`,
`insert into city (id, name) values (...)[,(...)]`,
`update city set name = xx where id = N`,
`delete from city where id = N`,
`truncate table city`,
add column， add index work well.
`insert into city values (...)[,(...)]` does not work well.
3. Other broadcast tables have the same problem, but sharding tables work well.
---
For MySQL driver, we used `mysql-connector-java-5.1.49.jar`, downloaded from https://mvnrepository.com/artifact/mysql/mysql-connector-java
When we tried 8.0.*, datetime item cannot return the right format, values contains 'T'.
--
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] glon closed issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon closed issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063
--
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] glon commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819332567
It seems a little bit like this [issue](https://github.com/apache/shardingsphere/issues/6606) , but not exactly.
--
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] glon commented on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon commented on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819317629
Executing SQLs :
```mysql
root@localhost:6033 [(none)]>use yaochufa
Database changed
sharding_user@localhost:6033 [yaochufa]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.02 sec)
sharding_user@localhost:6033 [(none)]>
```
And it's debug level log behind, logs/stdout.log
```
[INFO ] 15:56:46.718 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select USER()
[INFO ] 15:56:46.719 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@79e971a4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a58e36), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a58e36, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=12, distinctRow=false, projections=[ExpressionProjection(expression=USER(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@263a2033, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@772f2786, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@45790c96, containsSubquery=false)
[INFO ] 15:56:46.719 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_1 ::: select USER()
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@a825ed5, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@477123c8), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@477123c8, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@bd0e9f, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@42875337, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@5a6bd944, containsSubquery=false)
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_3 ::: select * from city
[ERROR] 15:56:57.007 [ShardingSphere-Command-5] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5e580a5f, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b9131d2), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b9131d2, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@f52981e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@c1cde8d, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@120837a3, containsSubquery=false)
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_2 ::: SELECT DATABASE()
```
logback.xml :
```yaml
<configuration>
<appender name="file" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>sharding.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>sharding.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>60</maxHistory>
<!--<totalSizeCap>1GB</totalSizeCap>-->
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n</pattern>
</encoder>
</appender>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%-5level] %d{HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.apache.shardingsphere" level="info" additivity="false">
<appender-ref ref="console"/>
<appender-ref ref="file"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="console" />
<appender-ref ref="file" />
</root>
</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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819317629
Executing SQLs :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.02 sec)
sharding_user@localhost:6033 [(none)]>
```
And it's debug level log, logs/stdout.log
```
[INFO ] 15:56:46.718 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select USER()
[INFO ] 15:56:46.719 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@79e971a4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a58e36), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a58e36, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=12, distinctRow=false, projections=[ExpressionProjection(expression=USER(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@263a2033, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@772f2786, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@45790c96, containsSubquery=false)
[INFO ] 15:56:46.719 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_1 ::: select USER()
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@a825ed5, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@477123c8), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@477123c8, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@bd0e9f, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@42875337, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@5a6bd944, containsSubquery=false)
[INFO ] 15:56:56.947 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_3 ::: select * from city
[ERROR] 15:56:57.007 [ShardingSphere-Command-5] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5e580a5f, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b9131d2), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@b9131d2, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@f52981e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@c1cde8d, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@120837a3, containsSubquery=false)
[INFO ] 15:56:57.009 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_2 ::: SELECT DATABASE()
```
logback.xml :
```yaml
<configuration>
<appender name="file" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>sharding.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>sharding.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>60</maxHistory>
<!--<totalSizeCap>1GB</totalSizeCap>-->
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n</pattern>
</encoder>
</appender>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%-5level] %d{HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.apache.shardingsphere" level="info" additivity="false">
<appender-ref ref="console"/>
<appender-ref ref="file"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="console" />
<appender-ref ref="file" />
</root>
</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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-824497081
Maybe I could reproduce the exception.
When I copy my config files to a new test env, and run it, could not see the broadcast table this time.
I packaged my env and uploaded to the cloud, download and unpack, run the db.sql in config dir and create db user with grants, then the env should be copied.
link：https://pan.baidu.com/s/1_sEMQ8xhLHVizIRKYCRkGg
code：6666
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819317629
@strongduanmu
After I changed the maxLifetimeMilliseconds in conf/config-sharding.yaml for each dateasource from default 1800000 to 60000, the WARN log before is disappeared.
```
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 60000
```
But, the problem all the same.
Executing SQLs :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.02 sec)
sharding_user@localhost:6033 [(none)]>
```
And it's debug level log, logs/stdout.log
```
[INFO ] 16:11:54.461 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ: [id: 0xbe9d76e7, L:/10.13.15.140:6033 - R:/10.13.8.18:14735]
[INFO ] 16:11:54.463 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ COMPLETE
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1b38ed33, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@90251ca, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3c98a0d5, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@60159300, containsSubquery=false)
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[ERROR] 16:11:56.491 [ShardingSphere-Command-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 16:11:56.517 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@371321cf, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@4faf163c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@294a4694, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@779172b, containsSubquery=false)
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_3 ::: SELECT DATABASE()
```
logback.xml :
```yaml
<configuration>
<appender name="file" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>sharding.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>sharding.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>60</maxHistory>
<!--<totalSizeCap>1GB</totalSizeCap>-->
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n</pattern>
</encoder>
</appender>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%-5level] %d{HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.apache.shardingsphere" level="info" additivity="false">
<appender-ref ref="console"/>
<appender-ref ref="file"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="console" />
<appender-ref ref="file" />
</root>
</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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819180886
1. We've set `sql.show = true`
When executing `select * from city`, could not get the answer, and the selected database will lost, like :
```sql
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
Here's the logs/stdout.log recording when executing the SQL :
```
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c958aed, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@25372307, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@35e56d5a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@55474be9, containsSubquery=false)
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[ERROR] 10:37:36.500 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.NullPointerException: null
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6300)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@46fe6fd1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7660743e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4a1ab3ef, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7fafcd9a, containsSubquery=false)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT DATABASE()
[WARN ] 10:37:36.501 [ShardingSphere-Command-5] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5328c8ea (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
server.yaml :
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max.connections.size.per.query: 9
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 128 # The default value is 128.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
2. SQL like `select id, name from city`, `insert into city (id, name) values (...)[,(...)]` work well.
`insert into city values (...)[,(...)]` does not work well.
3. Other broadcast tables have the same problem, but sharding tables work well.
---
For MySQL driver, we used `mysql-connector-java-5.1.49.jar`, downloaded from https://mvnrepository.com/artifact/mysql/mysql-connector-java
When we tried 8.0.*, datetime item cannot return the right format, values contains 'T'.
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819317629
@strongduanmu
After I changed the maxLifetimeMilliseconds in conf/config-sharding.yaml for each dateasource from default 1800000 to 60000, the WARN log before now disappeared.
```
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 60000
```
But, the problem still here.
Executing SQLs :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.02 sec)
sharding_user@localhost:6033 [(none)]>
```
And it's debug level log, logs/stdout.log
```
[INFO ] 16:11:54.461 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ: [id: 0xbe9d76e7, L:/10.13.15.140:6033 - R:/10.13.8.18:14735]
[INFO ] 16:11:54.463 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ COMPLETE
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1b38ed33, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@90251ca, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3c98a0d5, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@60159300, containsSubquery=false)
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[ERROR] 16:11:56.491 [ShardingSphere-Command-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 16:11:56.517 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@371321cf, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@4faf163c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@294a4694, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@779172b, containsSubquery=false)
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_3 ::: SELECT DATABASE()
```
logback.xml :
```yaml
<configuration>
<appender name="file" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>sharding.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>sharding.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>60</maxHistory>
<!--<totalSizeCap>1GB</totalSizeCap>-->
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n</pattern>
</encoder>
</appender>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%-5level] %d{HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.apache.shardingsphere" level="info" additivity="false">
<appender-ref ref="console"/>
<appender-ref ref="file"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="console" />
<appender-ref ref="file" />
</root>
</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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819317629
@strongduanmu
After I changed the maxLifetimeMilliseconds in conf/config-sharding.yaml for each dateasource from default 1800000 to 60000, the WARN log before is disappeared.
```
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 60000
```
But, the problem still here.
Executing SQLs :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.02 sec)
sharding_user@localhost:6033 [(none)]>
```
And it's debug level log, logs/stdout.log
```
[INFO ] 16:11:54.461 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ: [id: 0xbe9d76e7, L:/10.13.15.140:6033 - R:/10.13.8.18:14735]
[INFO ] 16:11:54.463 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ COMPLETE
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1b38ed33, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@90251ca, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3c98a0d5, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@60159300, containsSubquery=false)
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[ERROR] 16:11:56.491 [ShardingSphere-Command-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 16:11:56.517 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@371321cf, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@4faf163c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@294a4694, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@779172b, containsSubquery=false)
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_3 ::: SELECT DATABASE()
```
logback.xml :
```yaml
<configuration>
<appender name="file" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>sharding.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>sharding.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>60</maxHistory>
<!--<totalSizeCap>1GB</totalSizeCap>-->
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n</pattern>
</encoder>
</appender>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%-5level] %d{HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.apache.shardingsphere" level="info" additivity="false">
<appender-ref ref="console"/>
<appender-ref ref="file"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="console" />
<appender-ref ref="file" />
</root>
</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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819180886
1. We've set `sql.show = true`
When executing `select * from city`, could not get the answer, and the selected database will lost, like :
```
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.01 sec)
sharding_user@localhost:6033 [(none)]>
```
Here's the logs/stdout.log recording when executing the SQL :
```
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4c958aed, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4365ad34, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@25372307, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@35e56d5a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@55474be9, containsSubquery=false)
[INFO ] 10:37:36.364 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[WARN ] 10:37:36.364 [ShardingSphere-Command-4] com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@3ba54078 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[ERROR] 10:37:36.500 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.NullPointerException: null
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6300)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@46fe6fd1, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@18a691b4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7660743e, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4a1ab3ef, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@7fafcd9a, containsSubquery=false)
[INFO ] 10:37:36.501 [ShardingSphere-Command-5] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT DATABASE()
[WARN ] 10:37:36.501 [ShardingSphere-Command-5] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@5328c8ea (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
```
server.yaml :
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max.connections.size.per.query: 9
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 128 # The default value is 128.
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
2. SQL like `select id, name from city`, `insert into city (id, name) values (...)[,(...)]` work well.
`insert into city values (...)[,(...)]` does not work well.
3. Other broadcast tables have the same problem, but sharding tables work well.
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-826273393
I configured `defaultDataSourceName: ds_0` to solved the problem, but during the process I've been tried, not work, oddly...
--
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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-819317629
@strongduanmu
After I changed the maxLifetimeMilliseconds in conf/config-sharding.yaml for each dateasource from default 1800000 to 60000, the WARN log before disappeared.
```
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 60000
```
But, the problem still here.
Executing SQLs :
```mysql
root@localhost:6033 [(none)]>use sharding_db
Database changed
sharding_user@localhost:6033 [sharding_db]>select * from city;
Query OK, 0 rows affected, 25971 warnings (0.02 sec)
sharding_user@localhost:6033 [(none)]>
```
And it's debug level log, logs/stdout.log
```
[INFO ] 16:11:54.461 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ: [id: 0xbe9d76e7, L:/10.13.15.140:6033 - R:/10.13.8.18:14735]
[INFO ] 16:11:54.463 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x14d7fc8a, L:/0.0.0.0:6033] READ COMPLETE
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: select * from city
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1b38ed33, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2436f092, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=7, distinctRow=false, projections=[ShorthandProjection(owner=Optional.empty, actualColumns=[])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@90251ca, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3c98a0d5, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@60159300, containsSubquery=false)
[INFO ] 16:11:56.319 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from city
[ERROR] 16:11:56.491 [ShardingSphere-Command-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279)
at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
at org.apache.shardingsphere.sharding.execute.sql.execute.result.StreamQueryResult.next(StreamQueryResult.java:50)
at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.next(JDBCDatabaseCommunicationEngine.java:138)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.next(QueryBackendHandler.java:57)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:135)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:81)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:101)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
[INFO ] 16:11:56.517 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: SELECT DATABASE()
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@371321cf, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@eed01c5, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[ExpressionProjection(expression=DATABASE(), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@4faf163c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@294a4694, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@779172b, containsSubquery=false)
[INFO ] 16:11:56.518 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_3 ::: SELECT DATABASE()
```
logback.xml :
```yaml
<configuration>
<appender name="file" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>sharding.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>sharding.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>60</maxHistory>
<!--<totalSizeCap>1GB</totalSizeCap>-->
</rollingPolicy>
<encoder>
<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{80} - %msg%n</pattern>
</encoder>
</appender>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%-5level] %d{HH:mm:ss.SSS} [%thread] %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.apache.shardingsphere" level="info" additivity="false">
<appender-ref ref="console"/>
<appender-ref ref="file"/>
</logger>
<root>
<level value="debug" />
<appender-ref ref="console" />
<appender-ref ref="file" />
</root>
</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] glon edited a comment on issue #10063: Proxy broadcastTables select * error
Posted by GitBox <gi...@apache.org>.
glon edited a comment on issue #10063:
URL: https://github.com/apache/shardingsphere/issues/10063#issuecomment-826273393
I configured `defaultDataSourceName: ds_0` to solved the problem, but during the process I've been tried, not work, oddly...
--
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