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