You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/06/04 09:08:24 UTC
[GitHub] [shardingsphere] tony1991 opened a new issue #5919: how to set broadcastTables with multi-datasources
tony1991 opened a new issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919
There are five datasources in my application. One datasource is A，Four datasource is B1 B2 B3 B4, I want to set a broadcastTables like tb_config in B1 B2 B3 B4, how to config?
----------------------------------------------------------------
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] tony1991 commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
tony1991 commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-640995665
> what's your sharding rule for the application？
A is a outer db, no relation with B.
B1 B2 B3 B4 store biz data which is hashed by user_no.
now I just want to config a broadcastTables tb_config ,which need storeed same in each B(1,2,3,4)
my situation like this:
https://shardingsphere.apache.org/document/legacy/4.x/document/en/faq/#6-if-sharding-database-is-partial-should-tables-without-sharding-database--table-be-configured-in-sharding-rules
----------------------------------------------------------------
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] jacksparrow414 edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
jacksparrow414 edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-644145240
you could config A as defaultDataSource,and config tb_config as the broadcast table for the other four databases.like this:
```yaml
dataSources:
names: ds0,ds1,ds2,ds3,de4
ds0:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/A
username: root
password:
ds1:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:13306/B1
username: root
password:
ds2:
url: jdbc:mysql://localhost:23306/B2
ds3:
url: jdbc:mysql://localhost:33306/B3
ds4:
url: jdbc:mysql://localhost:43306/B4
sharding:
default-database-strategy:
inline:
sharding-column: id
algorithm-expression: ds$->{id % 2}
default-table-strategy:
inline:
sharding-column: age
algorithm-expression: user_$->{age % 2}
tables:
user:
# Configuration rules are not included database A
actual-data-nodes: ds$->{1..4}.user_$->{1..4}
default-data-source-name: ds0
broadcast-tables: t_config
```
----------------------------------------------------------------
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] chrissata edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646406106
Met same issue here( **version 4.1.0**)
configure the table's actual data node in sharding rule ->tables section, and also put it into broadcast config like below: (3 datasources, but broadcast table is on ds0&1)
```
ds0:
xxx
ds1:
xxx
ds2:
xxx
shardingRule:
tables:
sys_role:
actualDataNodes: ds${0..1}.sys_role
...
broadcastTables:
- sys_role
```
When I do update like :
`UPDATE sys_role set modified_time=1592355995 WHERE sys_role.id = 550700;`
, it will report error:
```
org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@7163e1c0'.
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:72)
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:73)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
```
----------------------------------------------------------------
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] menghaoranss commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-639321335
what's your sharding rule of the application？
----------------------------------------------------------------
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] chrissata edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646462297
I tried above config on 4.1.0 version, but using sctl:explain update xxx sql I found that the sql was still being routed to all datasources.
Below is the code that controls the routing target:
```
public final class ShardingDatabaseBroadcastRoutingEngine implements ShardingRouteEngine {
@Override
public RouteResult route(final ShardingRule shardingRule) {
RouteResult result = new RouteResult();
for (String each : shardingRule.getShardingDataSourceNames().getDataSourceNames()) {
result.getRouteUnits().add(new RouteUnit(new RouteMapper(each, each), Collections.emptyList()));
}
return result;
}
}
```
----------------------------------------------------------------
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] chrissata commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646485623
Verified again and found that only DAL sql could be routed to specified data sources, other update、select SQLs will use ShardingDatabaseBroadcastRoutingEngine, but actually it should use ShardingDataSourceGroupBroadcastRoutingEngine.
in ShardingRouteEngineFactory newInstance:
```
if (sqlStatement instanceof DALStatement) {
return getDALRoutingEngine(shardingRule, sqlStatement, tableNames);
}
...
if (shardingRule.isAllBroadcastTables(tableNames)) {
return sqlStatement instanceof SelectStatement ? new ShardingUnicastRoutingEngine(tableNames) : new ShardingDatabaseBroadcastRoutingEngine();
}
```
```
private static ShardingRouteEngine getDALRoutingEngine(final ShardingRule shardingRule, final SQLStatement sqlStatement, final Collection<String> tableNames) {
if (sqlStatement instanceof UseStatement) {
return new ShardingIgnoreRoutingEngine();
}
if (sqlStatement instanceof SetStatement || sqlStatement instanceof ResetParameterStatement || sqlStatement instanceof ShowDatabasesStatement) {
return new ShardingDatabaseBroadcastRoutingEngine();
}
if (!tableNames.isEmpty() && !shardingRule.tableRuleExists(tableNames) && shardingRule.hasDefaultDataSourceName()) {
return new ShardingDefaultDatabaseRoutingEngine(tableNames);
}
if (!tableNames.isEmpty()) {
return new ShardingUnicastRoutingEngine(tableNames);
}
return new ShardingDataSourceGroupBroadcastRoutingEngine();
}
```
----------------------------------------------------------------
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] chrissata removed a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata removed a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646406106
Met same issue here( **version 4.1.0**)
configure the table's actual data node in sharding rule ->tables section, and also put it into broadcast config like below: (3 datasources, but broadcast table is on ds0&1)
```
ds0:
xxx
ds1:
xxx
ds2:
xxx
shardingRule:
tables:
sys_role:
actualDataNodes: ds${0..1}.sys_role
...
broadcastTables:
- sys_role
defaultDataSourceName: ds2
```
When I do update like :
`UPDATE sys_role set modified_time=1592355995 WHERE sys_role.id = 550700;`
, it will report error:
```
org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@7163e1c0'.
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:72)
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:73)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
```
----------------------------------------------------------------
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] chrissata commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646462297
I tried above config, but using sctl:explain command I found that the sql was still being routed to all datasources.
Below is the code that controls the routing target:
public final class ShardingDatabaseBroadcastRoutingEngine implements ShardingRouteEngine {
@Override
public RouteResult route(final ShardingRule shardingRule) {
RouteResult result = new RouteResult();
for (String each : shardingRule.getShardingDataSourceNames().getDataSourceNames()) {
result.getRouteUnits().add(new RouteUnit(new RouteMapper(each, each), Collections.emptyList()));
}
return result;
}
}
----------------------------------------------------------------
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 #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-641714038
> > what's your sharding rule for the application？
>
> A is a outer db, no relation with B.
> B1 B2 B3 B4 store biz data which is hashed by user_no.
> now I just want to config a broadcastTables tb_config ,which need storeed same in each B(1,2,3,4)
>
> my situation like this:
> https://shardingsphere.apache.org/document/legacy/4.x/document/en/faq/#6-if-sharding-database-is-partial-should-tables-without-sharding-database--table-be-configured-in-sharding-rules
Yes, it is the correct solution.
----------------------------------------------------------------
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] chrissata edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646462297
I tried above config on 4.1.0 version, but using sctl:explain command I found that the sql was still being routed to all datasources.
Below is the code that controls the routing target:
public final class ShardingDatabaseBroadcastRoutingEngine implements ShardingRouteEngine {
@Override
public RouteResult route(final ShardingRule shardingRule) {
RouteResult result = new RouteResult();
for (String each : shardingRule.getShardingDataSourceNames().getDataSourceNames()) {
result.getRouteUnits().add(new RouteUnit(new RouteMapper(each, each), Collections.emptyList()));
}
return result;
}
}
----------------------------------------------------------------
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] RaigorJiang closed issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
RaigorJiang closed issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] menghaoranss removed a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
menghaoranss removed a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-639321335
what's your sharding rule of the application？
----------------------------------------------------------------
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] tony1991 edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
tony1991 edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-640995665
> what's your sharding rule for the application？
A is a outer db, no relation with B.
B1 B2 B3 B4 store biz data which is hashed by user_no.
now I just want to config a broadcastTables tb_config ,which need storeed same in each B(1,2,3,4)
my situation like this:
https://shardingsphere.apache.org/document/legacy/4.x/document/en/faq/#6-if-sharding-database-is-partial-should-tables-without-sharding-database--table-be-configured-in-sharding-rules
----------------------------------------------------------------
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] jacksparrow414 commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
jacksparrow414 commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-644145240
you could config A as defaultDataSource,and config tb_config as the broadcast table for the other four databases.like this:
```yaml
dataSources:
names: ds0,ds1,ds2,ds3,de4,ds5
ds0:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/A
username: root
password:
ds1:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:13306/B1
username: root
password:
ds2:
url: jdbc:mysql://localhost:23306/B2
ds3:
url: jdbc:mysql://localhost:33306/B3
ds4:
url: jdbc:mysql://localhost:43306/B4
sharding:
default-database-strategy:
inline:
sharding-column: id
algorithm-expression: ds$->{id % 2}
default-table-strategy:
inline:
sharding-column: age
algorithm-expression: user_$->{age % 2}
tables:
user:
# Configuration rules are not included database A
actual-data-nodes: ds$->{1..5}.user_$->{1..5}
default-data-source-name: ds0
broadcast-tables: t_config
```
----------------------------------------------------------------
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] tony1991 commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
tony1991 commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-642472280
> solution
> > > what's your sharding rule for the application？
> >
> >
> > A is a outer db, no relation with B.
> > B1 B2 B3 B4 store biz data which is hashed by user_no.
> > now I just want to config a broadcastTables tb_config ,which need storeed same in each B(1,2,3,4)
> > my situation like this:
> > https://shardingsphere.apache.org/document/legacy/4.x/document/en/faq/#6-if-sharding-database-is-partial-should-tables-without-sharding-database--table-be-configured-in-sharding-rules
>
> Yes, it is the correct solution.
No, i don't know how to do it. May you give me a example or demo?
----------------------------------------------------------------
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] jacksparrow414 edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
jacksparrow414 edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-644145240
you could config A as defaultDataSource,and config tb_config as the broadcast table for the other four databases.like this:
```yaml
dataSources:
names: ds0,ds1,ds2,ds3,de4
ds0:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/A
username: root
password:
ds1:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:13306/B1
username: root
password:
ds2:
url: jdbc:mysql://localhost:23306/B2
ds3:
url: jdbc:mysql://localhost:33306/B3
ds4:
url: jdbc:mysql://localhost:43306/B4
sharding:
default-database-strategy:
inline:
sharding-column: id
algorithm-expression: ds$->{id % 2}
default-table-strategy:
inline:
sharding-column: age
algorithm-expression: user_$->{age % 2}
tables:
user:
# Configuration rules are not included database A
actual-data-nodes: ds$->{1..4}.user_$->{1..4}
default-data-source-name: ds0
broadcast-tables: t_config
```
----------------------------------------------------------------
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] menghaoranss commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-639321955
what's your sharding rule for the application？
----------------------------------------------------------------
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] RaigorJiang commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-926756508
Since this issue has been inactive for more than 1 year, we will close it.
If you still have related questions, please submit a new issue, thank you.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] chrissata commented on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata commented on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646406106
Met same issue here( version 4.1.0)
configure the table's actual data node in sharding rule ->tables section, and also put it into broadcast config like below: (3 datasources, but broadcast table is on ds0&1)
ds0:
xxx
ds1:
xxx
ds2:
xxx
shardingRule:
tables:
sys_role:
actualDataNodes: ds${0..1}.sys_role
...
broadcastTables:
- sys_role
When I do update like :
UPDATE sys_role set modified_time=1592355995 WHERE sys_role.id = 550700;
, it will report error:
org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@7163e1c0'.
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:72)
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:73)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
----------------------------------------------------------------
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] chrissata edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646406106
Met same issue here( **version 4.1.0**)
configure the table's actual data node in sharding rule ->tables section, and also put it into broadcast config like below: (3 datasources, but broadcast table is on ds0&1)
```
ds0:
xxx
ds1:
xxx
ds2:
xxx
shardingRule:
tables:
sys_role:
actualDataNodes: ds${0..1}.sys_role
...
broadcastTables:
- sys_role
defaultDataSourceName: ds2
```
When I do update like :
`UPDATE sys_role set modified_time=1592355995 WHERE sys_role.id = 550700;`
, it will report error:
```
org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@7163e1c0'.
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:72)
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:73)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
```
----------------------------------------------------------------
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] chrissata edited a comment on issue #5919: how to set broadcastTables with multi-datasources
Posted by GitBox <gi...@apache.org>.
chrissata edited a comment on issue #5919:
URL: https://github.com/apache/shardingsphere/issues/5919#issuecomment-646462297
I tried above config on 4.1.0 version, but using sctl:explain command I found that the sql was still being routed to all datasources.
Below is the code that controls the routing target:
```
public final class ShardingDatabaseBroadcastRoutingEngine implements ShardingRouteEngine {
@Override
public RouteResult route(final ShardingRule shardingRule) {
RouteResult result = new RouteResult();
for (String each : shardingRule.getShardingDataSourceNames().getDataSourceNames()) {
result.getRouteUnits().add(new RouteUnit(new RouteMapper(each, each), Collections.emptyList()));
}
return result;
}
}
```
----------------------------------------------------------------
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