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 2022/04/08 06:17:09 UTC
[GitHub] [shardingsphere] zorohu opened a new issue, #16656: insert statement error : Route table does not exist, available actual table
zorohu opened a new issue, #16656:
URL: https://github.com/apache/shardingsphere/issues/16656
## Question
The table created by the DDL statement was successfully created on shardingsphere-proxy. But an error occurs when using the inset statement to insert, can you help explain what the problem is?
### this is my config file
```
schemaName: sharding_db
dataSources:
master_0:
url: jdbc:mysql://192.168.80.172:23306/demo_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: Aa123456.
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
slave_0:
url: jdbc:mysql://192.168.80.172:23307/demo_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: Aa123456.
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
master_1:
url: jdbc:mysql://192.168.80.176:23306/demo_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: Aa123456.
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
slave_1:
url: jdbc:mysql://192.168.80.176:23307/demo_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: Aa123456.
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
defaultTableStrategy:
none:
tables:
biz_ymcz_minclass:
actualDataNodes: master_0.biz_ymcz_minclass_${[36]},master_1.biz_ymcz_minclass_${[41]}
tableStrategy:
standard:
shardingColumn: area_code
shardingAlgorithmName: biz_ymcz_minclass_inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
biz_ymcz:
actualDataNodes: master_0.biz_ymcz_${[36]},master_1.biz_ymcz_${[41]}
tableStrategy:
standard:
shardingColumn: area_code
shardingAlgorithmName: biz_ymcz_inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
shardingAlgorithms:
biz_ymcz_inline:
type: INLINE
props:
algorithm-expression: biz_ymcz_${area_code[0..1]}
biz_ymcz_minclass_inline:
type: INLINE
props:
algorithm-expression: biz_ymcz_minclass_${area_code[0..1]}
keyGenerators:
snowflake:
type: SNOWFLAKE
- !READWRITE_SPLITTING
dataSources:
master_0:
type: Static
props:
write-data-source-name: master_0
read-data-source-names: slave_0
master_1:
type: Static
props:
write-data-source-name: master_1
read-data-source-names: slave_1
```
### The successfully created table is shown in the following figure, on two different master nodes respectively
<img width="324" alt="image" src="https://user-images.githubusercontent.com/24402746/162374751-079bc750-b615-410f-b7f5-139a88bdbd50.png">
<img width="305" alt="image" src="https://user-images.githubusercontent.com/24402746/162374778-636e5935-26e9-4422-b28d-6b093e6b7da1.png">
### This is the error message
> 1997 - Runtime exception: [Route table biz_ymcz_minclass_36 does not exist, available actual table: [biz_ymcz_minclass_41]], Time: 0.049000s
### The versions I have tried are `5.0.0` , `5.1.0`
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] zorohu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
zorohu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1093673240
@strongduanmu Thank you for your help and patience, I may have overlooked this property, I'll add this property test later
--
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] zorohu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
zorohu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1092584133
this is a simplified version of the initialization statement
```
CREATE TABLE `biz_ymcz_minclass` (
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`min_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`min_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0',
`area_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uq_minid` (`min_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;
INSERT INTO `biz_ymcz_minclass` VALUES ('1480067538049560578', '12345678-12345678', '123456789', '3600003');
```
--
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] zorohu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
zorohu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1092559039
Thanks Reply
Here are all my logs using the insert statement,But there is no insert statement in the log
<img width="1076" alt="image" src="https://user-images.githubusercontent.com/24402746/162388810-f5c01997-175d-42b8-97e9-bb9e1785aaa2.png">
And I tried to insert data in two real nodes. Using the select statement in ShardingSphere-proxy can query the data correctly
<img width="1450" alt="image" src="https://user-images.githubusercontent.com/24402746/162390599-7cb1b230-c974-44ba-981b-a105e1d7ca1e.png">
--
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] strongduanmu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1093736690
@zorohu Thank you, I will try again.
--
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] strongduanmu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1092544654
Hi @zorohu, can you open `sql-show`, and provide sql-show log?
--
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] strongduanmu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1092567336
@zorohu Thank you. Can you provide init sql of `biz_ymcz_minclass` and insert statement? I will try to reproduce this problem in my local machine.
--
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] zorohu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
zorohu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1096162004
@strongduanmu Thanks again for your reply, this problem has been solved after I modified the configuration. But the read-write separation configuration thank you for reminding me, otherwise I may still be using the wrong configuration. This issue can be closed, and finally I like your patience and professionalism
--
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] strongduanmu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1096140682
Hi @zorohu, I found some problems from your configuration. First problem is dataSourceName in actualDataNodes. If you want to use read-write splitting feature, you need to configure read-write splitting aggregation data source with a unique name and use it in actualDataNodes. I made the following modifications to your configuration.
The second problem is that according to the sharding strategy you specified, the actual table cannot be found, and you need to adjust the sharding strategy. The current sharding strategy routes insert statements to the `biz_ymcz_minclass_36` table of the `ds_1` data source.
```yaml
schemaName: sharding_db
dataSources:
master_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
slave_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
master_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
slave_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
defaultTableStrategy:
none:
defaultDatabaseStrategy:
none:
tables:
biz_ymcz_minclass:
actualDataNodes: ds_0.biz_ymcz_minclass_${[36]},ds_1.biz_ymcz_minclass_${[41]}
databaseStrategy:
#none:
standard:
shardingColumn: area_code
shardingAlgorithmName: area_code_database_inline
tableStrategy:
standard:
shardingColumn: area_code
shardingAlgorithmName: biz_ymcz_minclass_inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
shardingAlgorithms:
area_code_database_inline:
type: INLINE
props:
algorithm-expression: ds_1
biz_ymcz_minclass_inline:
type: INLINE
props:
algorithm-expression: biz_ymcz_minclass_${area_code[0..1]}
- !READWRITE_SPLITTING
dataSources:
ds_0:
type: Static
props:
write-data-source-name: master_0
read-data-source-names: slave_0
ds_1:
type: Static
props:
write-data-source-name: master_1
read-data-source-names: slave_1
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] zorohu closed issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
zorohu closed issue #16656: insert statement error : Route table does not exist, available actual table
URL: https://github.com/apache/shardingsphere/issues/16656
--
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] strongduanmu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1092697125
@zorohu Why not configure 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1096169066
@zorohu You are welcome. Looking forward to seeing you more in the community.
--
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] zorohu commented on issue #16656: insert statement error : Route table does not exist, available actual table
Posted by GitBox <gi...@apache.org>.
zorohu commented on issue #16656:
URL: https://github.com/apache/shardingsphere/issues/16656#issuecomment-1093706902
Here is my modified config, but still the same problem and the logs are the same
Among them, I directly configure the policy to the master1 node `algorithm-expression: master_1`
```
rules:
- !SHARDING
defaultTableStrategy:
none:
defaultDatabaseStrategy:
none:
tables:
biz_ymcz_minclass:
actualDataNodes: master_0.biz_ymcz_minclass_${[36]},master_1.biz_ymcz_minclass_${[41]}
databaseStrategy:
#none:
standard:
shardingColumn: area_code
shardingAlgorithmName: area_code_database_inline
tableStrategy:
standard:
shardingColumn: area_code
shardingAlgorithmName: biz_ymcz_minclass_inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
shardingAlgorithms:
area_code_database_inline:
type: INLINE
props:
algorithm-expression: master_1
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org