ss-proxy如何创建复杂的自定义算法规则？

xieyc · 2022 年2 月 22 日 06:48

刚开始学这个语法，帮忙看下这个DistSQL语法有什么问题？

创建自定义算法：
CREATE SHARDING ALGORITHM ftms_flow_algorithm (
TYPE(NAME=CLASS_BASED,PROPERTIES(“strategy”="COMPLEX ",“algorithm-class-name”=“com.xieyc.sharding.config.FtmsFlowShardingAlgorithm”))
);

创建表分片规则：
CREATE SHARDING TABLE RULE ftms_flow (
DATANODES(“ds_${0…1}.ftms_flow_$->{[201602,201603,201604,201605,201606,201607,201608,201609,201610,201611,201612,201701,201702,201703,201704,201705,201706,201707,201708,201709,201710,201711,201712,201801,201802,201803,201804,201805,201806,201807,201808,201809,201810,201811,201812,201901,201902,201903,201904,201905,201906,201907,201908,201909,201910,201911,201912,202001,202002,202003,202004,202005,202006,202007,202008,202009,202010,202011,202012,202101,202102,202103,202104,202105,202106,202107,202108,202109,202110,202111,202112,202201,202202,202203,202204,202205,202206,202207,202208,202209,202210,202211,202212]}”),
TABLE_STRATEGY(TYPE=complex,SHARDING_COLUMN=uid,create_date, ALGORITHM_CLASS_NAME=com.sxmaps.ftms.common.sharding.FtmsFlowShardingAlgorithm)
);

RaigorJiang · 2022 年2 月 22 日 08:54

CREATE SHARDING ALGORITHM 是正确的，
CREATE SHARDING TABLE RULE 中指定已创建的算法：

CREATE SHARDING TABLE RULE ftms_flow (
DATANODES("xxx"),
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM= ftms_flow_algorithm)
);

xieyc · 2022 年2 月 22 日 08:56

@RaigorJiang 这样还是不行

CREATE SHARDING TABLE RULE ftms_flow (
DATANODES("ds_${0..1}.ftms_flow_$->{[201602,201603,201604,201605,201606,201607,201608,201609,201610,201611,201612,201701,201702,201703,201704,201705,201706,201707,201708,201709,201710,201711,201712,201801,201802,201803,201804,201805,201806,201807,201808,201809,201810,201811,201812,201901,201902,201903,201904,201905,201906,201907,201908,201909,201910,201911,201912,202001,202002,202003,202004,202005,202006,202007,202008,202009,202010,202011,202012,202101,202102,202103,202104,202105,202106,202107,202108,202109,202110,202111,202112,202201,202202,202203,202204,202205,202206,202207,202208,202209,202210,202211,202212]}"),
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=uid,create_date, SHARDING_ALGORITHM=ftms_flow_algorithm)
);

xieyc · 2022 年2 月 22 日 08:57

@RaigorJiang
是不是指定多列的问题？

RaigorJiang · 2022 年2 月 22 日 09:34

多列时将 SHARDING_COLUMN 改为 SHARDING_COLUMNS

SHARDING_COLUMNS=uid,create_date

xieyc · 2022 年2 月 22 日 09:51

@RaigorJiang 麻烦再看下这个问题

我弄了个自定义算法。打成jar，放到了lib目录，启动Proxy的时候，会报这个类找不到的异常，是为啥？

RaigorJiang · 2022 年2 月 22 日 10:11

这样看不出来，提供 jar 包的结构看看
例如

xieyc · 2022 年2 月 22 日 10:14

@RaigorJiang jar包解压后，是这样的

RaigorJiang · 2022 年2 月 22 日 10:19

jar 包应该有 meta-inf 文件，你打包的方式是否正确呢，class 截图不能说明问题

xieyc · 2022 年2 月 22 日 10:20

@RaigorJiang 有的

RaigorJiang · 2022 年2 月 22 日 10:21

custom_algorithm 中类的路径呢

xieyc · 2022 年2 月 22 日 10:22

@RaigorJiang

路径是能对上的

RaigorJiang · 2022 年2 月 22 日 10:24

附件是我测试自定义算法（CLASS_BASED）的包，可以对比看看
class-based-sharding-algorithm.zip (4.9 KB)

RaigorJiang · 2022 年2 月 22 日 10:25

CLASS_BASED 形式的配置只关心 java 类的路径，可以不用 SPI 的配置信息

xieyc · 2022 年2 月 23 日 01:35

@RaigorJiang 好的，谢谢，我先看下

xieyc · 2022 年2 月 23 日 02:08

@RaigorJiang 这个问题解决了，现在又有如下问题

RaigorJiang · 2022 年2 月 23 日 02:11

提供下 Proxy 端的日志吧

xieyc · 2022 年2 月 23 日 02:12

@RaigorJiang

[ERROR] 2022-02-23 10:11:50.701 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.apache.shardingsphere.infra.distsql.exception.rule.InvalidAlgorithmConfigurationException: Invalid sharding_db algorithms [ftms_flow_algorithm].
        at org.apache.shardingsphere.sharding.distsql.handler.checker.ShardingTableRuleStatementChecker.checkStrategy(ShardingTableRuleStatementChecker.java:215)
        at org.apache.shardingsphere.sharding.distsql.handler.checker.ShardingTableRuleStatementChecker.checkTableRule(ShardingTableRuleStatementChecker.java:206)
        at org.apache.shardingsphere.sharding.distsql.handler.checker.ShardingTableRuleStatementChecker.check(ShardingTableRuleStatementChecker.java:98)
        at org.apache.shardingsphere.sharding.distsql.handler.checker.ShardingTableRuleStatementChecker.checkCreation(ShardingTableRuleStatementChecker.java:74)
        at org.apache.shardingsphere.sharding.distsql.handler.update.CreateShardingTableRuleStatementUpdater.checkSQLStatement(CreateShardingTableRuleStatementUpdater.java:45)
        at org.apache.shardingsphere.sharding.distsql.handler.update.CreateShardingTableRuleStatementUpdater.checkSQLStatement(CreateShardingTableRuleStatementUpdater.java:34)
        at org.apache.shardingsphere.proxy.backend.text.distsql.rdl.rule.RuleDefinitionBackendHandler.execute(RuleDefinitionBackendHandler.java:68)
        at org.apache.shardingsphere.proxy.backend.text.distsql.rdl.rule.RuleDefinitionBackendHandler.execute(RuleDefinitionBackendHandler.java:49)
        at org.apache.shardingsphere.proxy.backend.text.SchemaRequiredBackendHandler.execute(SchemaRequiredBackendHandler.java:51)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:62)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:96)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:69)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)

xieyc · 2022 年2 月 23 日 02:26

@RaigorJiang

应该是这里，但是我不知道是哪个条件出了问题

RaigorJiang · 2022 年2 月 23 日 02:29

好的，我们请 @lanchengx 帮忙看看