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/03/11 09:17:31 UTC
[GitHub] [shardingsphere] wavelet123 opened a new issue #9634: sharding tables error when insert
wavelet123 opened a new issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634
Hi Team
We are using sharding proxy to sharding 1000 tables in one database,but when we are running insert statement,we got the following errors,could you please advise?
[ERROR] 11:09:27.161 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.IllegalStateException: no database route info
schemaName: test_friends
dataSources:
ds_0:
url: jdbc:mysql://xx.xx.xx.xx:5306/test_friends?serverTimezone=UTC&useSSL=false
username: shardingadmin
password: xxx
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
t_accountfriend:
actualDataNodes: ds_${0}.t_accountfriend_00${0..9},ds_${0}.t_accountfriend_0${10..99},ds_${0}.t_accountfriend_${100..999}
tableStrategy:
inline:
shardingColumn: FFirstAccountID
algorithmExpression: t_accountfriend_${FFirstAccountID % 1000}
bindingTables:
- t_accountfriend
defaultDatabaseStrategy:
inline:
shardingColumn: FFirstAccountID
algorithmExpression: ds_${FFirstAccountID % 1000}
defaultTableStrategy:
none:
defaultDataSourceName: ds_0
Thanks
----------------------------------------------------------------
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] terrymanu commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-796592957
Could you provide log of `sql-show`?
----------------------------------------------------------------
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] wavelet123 commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-796593852
> Could you provide log of `sql-show`?
This is the only error:
[ERROR] 11:09:27.161 [ShardingSphere-Command-4] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.IllegalStateException: no database route info
at com.google.common.base.Preconditions.checkState(Preconditions.java:173)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeDataSources(ShardingStandardRoutingEngine.java:205)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route0(ShardingStandardRoutingEngine.java:192)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditionsWithCondition(ShardingStandardRoutingEngine.java:121)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditions(ShardingStandardRoutingEngine.java:115)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.getDataNodes(ShardingStandardRoutingEngine.java:96)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:74)
at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:69)
at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53)
at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91)
at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
at org.apache.shardingsphere.underlying.pluggble.prepare.SimpleQueryPrepareEngine.route(SimpleQueryPrepareEngine.java:54)
at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.StatementExecutorWrapper.doShardingRoute(StatementExecutorWrapper.java:80)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.wrapper.StatementExecutorWrapper.route(StatementExecutorWrapper.java:63)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:74)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.execute(QueryBackendHandler.java:52)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:78)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:93)
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)
----------------------------------------------------------------
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] wavelet123 commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-804679251
> Hi,
>
> ```
> bindingTables:
> - t_accountfriend
> defaultDatabaseStrategy:
> inline:
> shardingColumn: FFirstAccountID
> algorithmExpression: ds_${FFirstAccountID % 1000}
> ```
>
> Firstly, `bindingTables:` is incorrect since at least two tables are needed.
> Second, like @yanyzy said, `defaultDatabaseStrategy` will be applied to `t_accountfriend` in your case, as you ignore the specific `database strategy` for this table. I am curious my analysis is what you want?
It's ok now after removing those items.
--
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] wavelet123 edited a comment on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 edited a comment on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-799187291
> Hi @wavelet123
>
> 1. Which version is running in your case?
> 2. It looks like SS did not locate the database for this table. Which SQL triggered this exception?
> 3. Could you set `SQL-show` = true in the configuration file? Therefore we can get more details about your issue.
>
> Thanks
Hi tristaZero ,
1.We are using 4.1.1.
2.The following sql triggered this exception
insert into t_accountfriend(FFirstAccountID,FSecondAccountID,FGroupType,FIsDeleted,FCreateTime,FModifyTime)values(1013421,1013422,2,0,now(),now());
3. sql.show: true has been set in server.yaml,do we need to set SQL-show = true in config-sharding.yaml?
4. the "select count(*) from t_accountfriend" works correct and does not trigger any errors.
Thanks
----------------------------------------------------------------
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 #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-799171009
Hi @wavelet123
1. Which version is running in your case?
2. It looks like SS did not locate the database for this table. Which SQL triggered this exception?
3. Could you set `SQL-show` = true in the configuration file? Therefore we can get more details about your issue.
Thanks
----------------------------------------------------------------
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] wavelet123 closed issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 closed issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634
--
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] wavelet123 commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-796594125
mysql> insert into t_accountfriend(FFirstAccountID,FSecondAccountID,FGroupType,FIsDeleted,FCreateTime,FModifyTime)values(1013421,1013422,2,0,now(),now());
ERROR 10002 (C1000): 2Unknown exception: [no database route info]
----------------------------------------------------------------
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] wavelet123 commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-796595374
> mysql> insert into t_accountfriend(FFirstAccountID,FSecondAccountID,FGroupType,FIsDeleted,FCreateTime,FModifyTime)values(1013421,1013422,2,0,now(),now());
> ERROR 10002 (C1000): 2Unknown exception: [no database route info]
select count(*) from t_accountfriend;
+----------+
| count(*) |
+----------+
| 83103 |
+----------+
select is ok
----------------------------------------------------------------
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] yanyzy commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
yanyzy commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-803982371
Because t_accountfriend requires databaseStrategy?
--
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] wavelet123 commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-799187291
> Hi @wavelet123
>
> 1. Which version is running in your case?
> 2. It looks like SS did not locate the database for this table. Which SQL triggered this exception?
> 3. Could you set `SQL-show` = true in the configuration file? Therefore we can get more details about your issue.
>
> Thanks
Hi tristaZero ,
1.We are using 4.1.1.
2.The following sql triggered this exception
insert into t_accountfriend(FFirstAccountID,FSecondAccountID,FGroupType,FIsDeleted,FCreateTime,FModifyTime)values(1013421,1013422,2,0,now(),now());
3. sql.show: true has been set in server.yaml,do we need to set SQL-show = true in config-sharding.yaml?
Thanks
----------------------------------------------------------------
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 #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-804641477
Hi,
```xml
bindingTables:
- t_accountfriend
defaultDatabaseStrategy:
inline:
shardingColumn: FFirstAccountID
algorithmExpression: ds_${FFirstAccountID % 1000}
```
Firstly, `bindingTables:` is incorrect since at least two tables are needed.
Second, like @yanyzy said, `defaultDatabaseStrategy` will be applied to `t_accountfriend` in your case, as you ignore the specific `database strategy` for this table. I am curious my analysis is what you want?
--
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] wavelet123 commented on issue #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
wavelet123 commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-806298918
issue resolved
--
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 #9634: sharding tables error when insert
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9634:
URL: https://github.com/apache/shardingsphere/issues/9634#issuecomment-805500688
If this issues was solved, please close it. Thx.
--
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