You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "young138120 (via GitHub)" <gi...@apache.org> on 2023/04/21 07:46:28 UTC
[GitHub] [shardingsphere] young138120 opened a new issue, #25260: sharding table join fail
young138120 opened a new issue, #25260:
URL: https://github.com/apache/shardingsphere/issues/25260
version: 5.3.2
backgroud:
i have big many tables with more than 100 million rows in database_a，i want split those tables by year ，and move it to anther databases like database_2020,databse_2021,database_2022。
i use shardingsphere proxy server to sharding select , i don't execute any insert or update dml
now， i execute the sql that two sharding table join select
order_id is or_order_state_record table sharding key , but id is not sharding key in or_rescue_order table
cs_customer is broadcast table
but it throw Unkonw exception
i debug it ，i found the custom_id field is long type, but the code only support Integer and String
--
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] young138120 commented on issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517437321
> Hi, can you provide a demo to help reproduce this exception?
ok ，can you add my wx chat (yang513515163) ？
i will provider demo you need
--
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] tuichenchuxin commented on issue #25260: sharding table join fail
Posted by "tuichenchuxin (via GitHub)" <gi...@apache.org>.
tuichenchuxin commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1518925033
Ok, seems worked.
--
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 #25260: sharding table join fail
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517432578
Hi, can you provide a demo to help reproduce this exception?
--
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 #25260: sharding table join fail
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1519301621
This exception is caused by wrong sharding configuration. Since it has beed solved, I will close this issue.
--
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] young138120 commented on issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517533146
> It seems that your sharding configuration is not correct, why some sharding tables do not have a sharding strategy
i already define the defaultDatabaseStrategy , it is can be word
--
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] tuichenchuxin commented on issue #25260: sharding table join fail
Posted by "tuichenchuxin (via GitHub)" <gi...@apache.org>.
tuichenchuxin commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517503595
It seems that your sharding configuration is not correct, why some sharding tables do not have a sharding strategy
--
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 #25260: sharding table join fail
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517441889
You can copy your sharding config and table init sql here.
--
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] young138120 commented on issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517462270
all of exception info
``java
[ERROR] 2023-04-21 15:55:15.774 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.lang.IllegalStateException: null
at java.base/java.util.Optional.orElseThrow(Optional.java:403)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.impl.BinaryOperationExpressionConverter.convertSqlNodes(BinaryOperationExpressionConverter.java:88)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.impl.BinaryOperationExpressionConverter.convert(BinaryOperationExpressionConverter.java:82)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.ExpressionConverter.convert(ExpressionConverter.java:75)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.where.WhereConverter.convert(WhereConverter.java:34)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.select.SelectStatementConverter.lambda$convertSelect$3(SelectStatementConverter.java:67)
at java.base/java.util.Optional.flatMap(Optional.java:289)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.select.SelectStatementConverter.convertSelect(SelectStatementConverter.java:67)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.select.SelectStatementConverter.convert(SelectStatementConverter.java:51)
at org.apache.shardingsphere.sqlfederation.optimizer.converter.SQLNodeConverterEngine.convert(SQLNodeConverterEngine.java:42)
at org.apache.shardingsphere.sqlfederation.optimizer.SQLOptimizeEngine.optimize(SQLOptimizeEngine.java:49)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.execute(AdvancedSQLFederationExecutor.java:146)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.executeQuery(AdvancedSQLFederationExecutor.java:114)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecuteFederation(DatabaseConnector.java:288)
at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.execute(DatabaseConnector.java:205)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:92)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:114)
```
--
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 closed issue #25260: sharding table join fail
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu closed issue #25260: sharding table join fail
URL: https://github.com/apache/shardingsphere/issues/25260
--
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] young138120 commented on issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517456269
server.yaml
```yaml
######################################################################################################
#
# If you want to configure governance, authorization and proxy properties, please refer to this file.
#
######################################################################################################
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: shardingsphere_proxy_jn
server-lists: 1.1.1.1:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
# overwrite: true
#这里配置的是客户端链接proxy的授权信息，可以针对用户控制访问后面的数据源信息
authority:
users:
- user: dw_admin@%
password: Jn@123!@#
privilege:
type: ALL_PERMITTED
# 暂时用xa试下情况
transaction:
defaultType: XA
providerType: Atomikos
sqlParser:
# 是否解析 SQL 注释
sqlCommentParseEnabled: false
# SQL 语句本地缓存配置项
sqlStatementCache:
# 本地缓存初始容量
initialCapacity: 2000
# 本地缓存最大容量
maximumSize: 65535
# 解析树本地缓存配置项
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
#
logging:
loggers:
- loggerName: ShardingSphere-SQL
additivity: true
level: DEBUG
props:
enable: true
#
props:
system-log-level: DEBUG
#一次查询请求在每个数据库实例中所能使用的最大连接数。
max-connections-size-per-query: 1
# 用于设置任务处理线程池的大小。每个 ShardingSphereDataSource 使用一个独立的线程池，同一个 JVM 的不同数据源不共享线程池。
kernel-executor-size: 16
#在 ShardingSphere-Proxy 中设置传输数据条数的 IO 刷新阈值
proxy-frontend-flush-threshold: 128
proxy-hint-enabled: false
# sql-show is the same as props in logger ShardingSphere-SQL, and its priority is lower than logging rule
sql-show: true
# 在程序启动和更新时，是否检查分片元数据的结构一致性。
check-table-metadata-enabled: false
# Proxy 后端与数据库交互的每次获取数据行数（使用游标的情况下）。数值增大可能会增加 ShardingSphere Proxy 的内存使用。默认值为 -1，代表设置为 JDBC 驱动的最小值。
proxy-backend-query-fetch-size: -1
# Proxy 前端 Netty 线程池线程数量，默认值 0 代表使用 Netty 默认值。netty默认值为核数的2倍
proxy-frontend-executor-size: 0
# 可选选项：OLAP、OLTP。OLTP 选项可能会减少向客户端写入数据包的时间开销，但如果客户端连接数超过 proxy-frontend-executor-size，尤其是执行慢 SQL 时，它可能会增加 SQL 执行的延迟甚至阻塞其他客户端的连接。
proxy-backend-executor-suitable: OLAP
# 允许连接 Proxy 的最大客户端数量，默认值 0 代表不限制。
proxy-frontend-max-connections: 0
# 联邦查询执行器类型，包括：NONE，ORIGINAL，ADVANCED。
sql-federation-type: ADVANCED
# Proxy 通过配置文件指定 MySQL 的版本号，默认版本：5.7.22。
proxy-mysql-default-version: 5.7.22
proxy-default-port: 3307
proxy-netty-backlog: 1024
cdc-server-port: 33071
```
config-sharding.yaml
```yaml
######################################################################################################
#
# Here you can configure the rules for the proxy.
# This example is configuration of sharding rule.
#
######################################################################################################
databaseName: jn_proxy_db
dataSources:
ds_2019:
url: jdbc:mysql://1.1.1.1:3306/prod_2019?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2022:
url: jdbc:mysql://1.1.1.1:3306/prod_2022?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2020:
url: jdbc:mysql://2.2.2.2:3306/prod_2020?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2021:
url: jdbc:mysql://2.2.2.2:3306/prod_2021?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_now:
url: jdbc:mysql://3.3.3.3:3306/prod?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
or_order_state_record:
actualDataNodes: ds_${2019..2022}.or_order_state_record,ds_now.or_order_state_record
or_order_task_state :
actualDataNodes: ds_${2019..2022}.or_order_task_state,ds_now.or_order_task_state
or_task_node:
actualDataNodes: ds_${2019..2022}.or_task_node,ds_now.or_task_node
or_order_task:
actualDataNodes: ds_${2019..2022}.or_order_task,ds_now.or_order_task
or_order_other:
actualDataNodes: ds_${2019..2022}.or_order_other,ds_now.or_order_other
databaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: order_id_fetch
or_handle_user:
actualDataNodes: ds_${2019..2022}.or_handle_user,ds_now.or_handle_user
databaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: order_id_fetch
or_rescue_order:
actualDataNodes: ds_${2019..2022}.or_rescue_order,ds_now.or_rescue_order
databaseStrategy:
standard:
shardingColumn: book_time
shardingAlgorithmName: time_range
# tableStrategy:
# standard:
# shardingColumn: order_id
# shardingAlgorithmName: t_order_inline
# keyGenerateStrategy:
# column: order_id
# keyGeneratorName: snowflake
# auditStrategy:
# auditorNames:
# - sharding_key_required_auditor
# allowHintDisable: true
bindingTables:
- or_rescue_order,or_order_state_record,or_order_task_state,or_task_node,or_order_task,or_order_other,or_handle_user
broadcastTables:
- cs_customer
defaultDatabaseStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: order_id_fetch
defaultTableStrategy:
none:
# defaultAuditStrategy:
# auditorNames:
# - sharding_key_required_auditor
# allowHintDisable: true
shardingAlgorithms:
# database_inline:
# type: INLINE
# props:
# algorithm-expression: ds_${user_id % 2}
time_range:
type: INTERVAL
props:
datetime-pattern: "yyyy-MM-dd HH:mm:ss" # 分片字段格式
datetime-lower: "2019-01-31 00:00:00" # 范围下限
datetime-upper: "2022-12-31 23:59:59" # 范围上限
sharding-suffix-pattern: "yyyy" # 分片名后缀，可以是MM，yyyyMMdd等。
datetime-interval-amount: 1 # 分片间隔，这里指一个月
datetime-interval-unit: "Years" # 分片间隔单位
order_id_fetch:
type: JN_ORDERID_SHARDING
# keyGenerators:
# snowflake:
# type: SNOWFLAKE
# auditors:
# sharding_key_required_auditor:
# type: DML_SHARDING_CONDITIONS
```
--
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] young138120 commented on issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517457662
> You can copy your sharding config and table init sql here.
ok i copy all of config and init table sql
--
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] young138120 commented on issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517455216
init sql:
```sql
CREATE TABLE `cs_customer` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '客户表id',
`customer_name` varchar(255) NOT NULL COMMENT '客户名称 ',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户管理信息主表'
```
```sql
CREATE TABLE `or_order_state_record` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单状态表id',
`order_id` bigint(20) DEFAULT NULL COMMENT '订单id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单状态记录表'
```
```sql
CREATE TABLE `or_rescue_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单id',
`customer_id` int(11) DEFAULT NULL COMMENT '客户id',
`book_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预定时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表'
```
--
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] young138120 commented on issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 commented on issue #25260:
URL: https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517453767
> You can copy your sharding config and table init sql here.
[demo.zip](https://github.com/apache/shardingsphere/files/11293728/demo.zip)
--
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] young138120 closed issue #25260: sharding table join fail
Posted by "young138120 (via GitHub)" <gi...@apache.org>.
young138120 closed issue #25260: sharding table join fail
URL: https://github.com/apache/shardingsphere/issues/25260
--
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