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/06/18 12:11:50 UTC
[GitHub] [shardingsphere] tinyfos opened a new issue, #18419: proxy 5.1.1 数据库分片相关的问题
tinyfos opened a new issue, #18419:
URL: https://github.com/apache/shardingsphere/issues/18419
我想请问下数据库分片的事情，我的环境是 ShardingSphere-proxy 5.1.1 可执行包，目前有三个问题
1、多键分片，
比如：
CREATE TABLE `role_0` (
`uid` bigint NOT NULL,
`profileId` bigint DEFAULT NULL,
`serverId` int DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`sceneId` int DEFAULT NULL,
`loginTime` datetime DEFAULT NULL,
`logoutTime` datetime DEFAULT NULL,
`deleteFlag` int DEFAULT NULL,
PRIMARY KEY (`uid`),
KEY `idx_profile_server_role_0` (`profileId`,`serverId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT COMMENT='角色基本数据';
在执行的时候报错
2、分片的时候 algorithm-expression 是否可以使用 java 代码，比如，是否可以左移右移
3、是否可以不配置表，让它走默认的路由分片
: [131, 0, 131, 0]
[INFO ] 2022-06-18 20:01:41.981 [ShardingSphere-Command-6] ShardingSphere-SQL - Logic SQL: select * from role where profileId = ?
[INFO ] 2022-06-18 20:01:41.983 [ShardingSphere-Command-6] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-06-18 20:01:41.984 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from role_0 where profileId = ? UNION ALL select * from role_1 where profileId = ? ::: [131, 131]
[INFO ] 2022-06-18 20:01:41.986 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from role_0 where profileId = ? UNION ALL select * from role_1 where profileId = ? ::: [131, 131]
[INFO ] 2022-06-18 20:01:42.193 [ShardingSphere-Command-6] ShardingSphere-SQL - Logic SQL: select * from role where profileId = ?
[INFO ] 2022-06-18 20:01:42.194 [ShardingSphere-Command-6] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-06-18 20:01:42.195 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from role_0 where profileId = ? UNION ALL select * from role_1 where profileId = ? ::: [131, 131]
[INFO ] 2022-06-18 20:01:42.197 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from role_0 where profileId = ? UNION ALL select * from role_1 where profileId = ? ::: [131, 131]
[ERROR] 2022-06-18 20:01:42.286 [ShardingSphere-Command-6] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.apache.shardingsphere.proxy.frontend.exception.UnsupportedCommandException: null
at org.apache.shardingsphere.proxy.frontend.mysql.command.generic.MySQLUnsupportedCommandExecutor.execute(MySQLUnsupportedCommandExecutor.java:38)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:100)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)
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)
[ERROR] 2022-06-18 20:01:42.289 [ShardingSphere-Command-7] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.lang.IndexOutOfBoundsException: readerIndex(78) + length(1) exceeds writerIndex(78): PooledSlicedByteBuf(ridx: 78, widx: 78, cap: 78/78, unwrapped: PooledUnsafeDirectByteBuf(ridx: 81, widx: 81, cap: 256))
at io.netty.buffer.AbstractByteBuf.checkReadableBytes0(AbstractByteBuf.java:1442)
at io.netty.buffer.AbstractByteBuf.readByte(AbstractByteBuf.java:730)
at io.netty.buffer.AbstractByteBuf.readUnsignedByte(AbstractByteBuf.java:744)
at org.apache.shardingsphere.db.protocol.mysql.payload.MySQLPacketPayload.readInt1(MySQLPacketPayload.java:49)
at org.apache.shardingsphere.db.protocol.mysql.payload.MySQLPacketPayload.readIntLenenc(MySQLPacketPayload.java:185)
at org.apache.shardingsphere.db.protocol.mysql.payload.MySQLPacketPayload.readStringLenenc(MySQLPacketPayload.java:250)
at org.apache.shardingsphere.db.protocol.mysql.packet.command.query.binary.execute.protocol.MySQLStringLenencBinaryProtocolValue.read(MySQLStringLenencBinaryProtocolValue.java:29)
at org.apache.shardingsphere.db.protocol.mysql.packet.command.query.binary.execute.MySQLComStmtExecutePacket.getParameters(MySQLComStmtExecutePacket.java:106)
at org.apache.shardingsphere.db.protocol.mysql.packet.command.query.binary.execute.MySQLComStmtExecutePacket.<init>(MySQLComStmtExecutePacket.java:84)
at org.apache.shardingsphere.db.protocol.mysql.packet.command.MySQLCommandPacketFactory.newInstance(MySQLCommandPacketFactory.java:65)
at org.apache.shardingsphere.proxy.frontend.mysql.command.MySQLCommandExecuteEngine.getCommandPacket(MySQLCommandExecuteEngine.java:57)
at org.apache.shardingsphere.proxy.frontend.mysql.command.MySQLCommandExecuteEngine.getCommandPacket(MySQLCommandExecuteEngine.java:48)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:97)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)
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)
我的分片配置：
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
equip:
actualDataNodes: ds_${0..1}.equip_${0..1}
tableStrategy:
standard:
shardingColumn: ownerId
shardingAlgorithmName: t_equip_inline
databaseStrategy:
standard:
shardingColumn: ownerId
shardingAlgorithmName: database_inline
item:
actualDataNodes: ds_${0..1}.item_${0..1}
tableStrategy:
standard:
shardingColumn: ownerId
shardingAlgorithmName: t_item_inline
databaseStrategy:
standard:
shardingColumn: ownerId
shardingAlgorithmName: database_inline
role_detail:
actualDataNodes: ds_${0..1}.role_detail_${0..1}
tableStrategy:
standard:
shardingColumn: uid
shardingAlgorithmName: t_role_detail_inline
databaseStrategy:
standard:
shardingColumn: uid
shardingAlgorithmName: database_uid_inline
role:
actualDataNodes: ds_${0..1}.role_${0..1}
tableStrategy:
complex:
shardingColumns: profileId,uid
shardingAlgorithmName: t_role_inline
databaseStrategy:
complex:
shardingColumns: profileId,uid
shardingAlgorithmName: database_profiles_inline
wallet:
actualDataNodes: ds_${0..1}.wallet_${0..1}
tableStrategy:
standard:
shardingColumn: uid
shardingAlgorithmName: t_wallet_inline
databaseStrategy:
standard:
shardingColumn: uid
shardingAlgorithmName: database_uid_inline
defaultDatabaseStrategy:
standard:
shardingColumn: ownerId
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${ownerId % 1}
database_profile_inline:
type: INLINE
props:
algorithm-expression: ds_${profileId % 1}
database_profiles_inline:
type: INLINE
props:
algorithm-expression: ds_${profileId % 1}
database_uid_inline:
type: INLINE
props:
algorithm-expression: ds_${uid % 1}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 1}
t_equip_inline:
type: INLINE
props:
algorithm-expression: equip_${ownerId % 1}
t_item_inline:
type: INLINE
props:
algorithm-expression: item_${ownerId % 1}
t_role_inline:
type: INLINE
props:
algorithm-expression: role_${uid % 1}
t_profile_inline:
type: INLINE
props:
algorithm-expression: role_${uid % 1}
t_role_detail_inline:
type: INLINE
props:
algorithm-expression: role_detail_${uid % 1}
t_wallet_inline:
type: INLINE
props:
algorithm-expression: wallet_${uid % 1}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
--
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] tinyfos closed issue #18419: proxy 5.1.1 Problems related to database fragmentation
Posted by GitBox <gi...@apache.org>.
tinyfos closed issue #18419: proxy 5.1.1 Problems related to database fragmentation
URL: https://github.com/apache/shardingsphere/issues/18419
--
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