CREATE TABLE `order_0` (
  `order_id` int NOT NULL,
  `user_id` int NOT NULL,
  `address_id` int NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `order_item_0` (
  `order_item_id` bigint NOT NULL,
  `order_id` int NOT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user_0` (
  `user_id` bigint NOT NULL,
  `user_name` varchar(45) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `address` (
  `address_id` int NOT NULL,
  `address_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

spring-boot-starter使用2.7.5
shardingsphere-jdbc-core-spring-boot-starter使用5.2.1

测试的时候最好直接跑,不要用单测,会被自动回滚掉.
可以定义多个ApplicationRunner来测试.

@Component
public class MyApplicationRunner implements ApplicationRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(final ApplicationArguments args) throws Exception {

用JdbcTemplate方便点也省去了依赖更多的东西.
返回自增key的代码样例:

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
    PreparedStatement ps = connection.prepareStatement("insert into user(`user_name`) values (?)",
            Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, "cc2");
    return ps;
}, keyHolder);
System.out.println("key:" + keyHolder.getKey());

配置

本来使用yaml的配置,但看了一下有点太乱,先用properties的代替.
配的时候有比较多的问题,几个配置错误会导致没法启动或者测试时报错,但配完之后感觉整体逻辑还是比较清晰的.

spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.props.sql-show=true

# logic datasource
spring.shardingsphere.datasource.names=shard00,shard01

# real datasource
spring.shardingsphere.datasource.shard00.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.shard00.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.shard00.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_0
spring.shardingsphere.datasource.shard00.username=root
spring.shardingsphere.datasource.shard00.password=

spring.shardingsphere.datasource.shard01.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.shard01.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.shard01.jdbc-url=jdbc:mysql://localhost:3306/sharding_test_1
spring.shardingsphere.datasource.shard01.username=root
spring.shardingsphere.datasource.shard01.password=

spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=\
  shard0$->{0..1}.user_$->{0..1}
spring.shardingsphere.rules.sharding.tables.order_item.actual-data-nodes=\
  shard0$->{0..1}.order_item_$->{0..1}
spring.shardingsphere.rules.sharding.tables.order.actual-data-nodes=\
  shard0$->{0..1}.order_$->{0..1}
spring.shardingsphere.rules.sharding.tables.address.actual-data-nodes=\
  shard0$->{0..1}.address


# database strategy and table strategy
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.database-strategy.standard.sharding-algorithm-name=alg_db_user
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.type=MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_user.props.sharding-count=2

spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=alg_table_user
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_user.props.sharding-count=2

spring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order.database-strategy.standard.sharding-algorithm-name=alg_db_order
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.type=MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_db_order.props.sharding-count=2

spring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order.table-strategy.standard.sharding-algorithm-name=alg_table_order
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_table_order.props.sharding-count=2
# order_item and order use the same strategy
spring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order_item.database-strategy.standard.sharding-algorithm-name=alg_db_order

spring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.order_item.table-strategy.standard.sharding-algorithm-name=alg_table_order

# key generator
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=user_id
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=alg_snowflake

spring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.order.key-generate-strategy.key-generator-name=alg_snowflake

spring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.column=order_item_id
spring.shardingsphere.rules.sharding.tables.order_item.key-generate-strategy.key-generator-name=alg_snowflake

# key generator algorithm
spring.shardingsphere.rules.sharding.key-generators.alg_snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.alg_uuid.type=UUID

# binding table and broadcast table
spring.shardingsphere.rules.sharding.binding-tables[0]=order,order_item
spring.shardingsphere.rules.sharding.broadcast-tables=address

也就分为逻辑datasource定义, 真实的datasource定义.
对于每个逻辑表,定义分库分表规则,如果需要生成分布式key,定义key的生成算法.
分别对应spring.shardingsphere.datasource.前缀和spring.shardingsphere.rules.sharding前缀.

对于SNOWFLAKE要注意数据库的字段类型要bigint,int不够放.

启动报错

***************************
APPLICATION FAILED TO START
***************************

Description:

An attempt was made to call a method that does not exist. The attempt was made from the following location:

    org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.<init>(ShardingSphereYamlConstructor.java:44)

The following method did not exist:

    'void org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.setCodePointLimit(int)'

The calling methods class, org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1, was loaded from the following location:

    jar:file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar!/org/apache/shardingsphere/infra/util/yaml/constructor/ShardingSphereYamlConstructor$1.class

The called methods class, org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1, is available from the following locations:

    jar:file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar!/org/apache/shardingsphere/infra/util/yaml/constructor/ShardingSphereYamlConstructor$1.class

The called methods class hierarchy was loaded from the following locations:

    null: file:/.m2/repository/org/apache/shardingsphere/shardingsphere-infra-util/5.2.1/shardingsphere-infra-util-5.2.1.jar
    org.yaml.snakeyaml.LoaderOptions: file:/.m2/repository/org/yaml/snakeyaml/1.30/snakeyaml-1.30.jar


Action:

Correct the classpath of your application so that it contains a single, compatible version of org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1

很明显的一个以来冲突问题, 主要是这行代码:

public ShardingSphereYamlConstructor(final Class<?> rootClass) {
    super(rootClass, new LoaderOptions() {
        
        {
            setCodePointLimit(Integer.MAX_VALUE);
        }
    });
    ShardingSphereYamlConstructFactory.getInstances().forEach(each -> typeConstructs.put(each.getType(), each));
    ShardingSphereYamlShortcutsFactory.getAllYamlShortcuts().forEach((key, value) -> addTypeDescription(new TypeDescription(value, key)));
    this.rootClass = rootClass;
}

snakeyaml的版本冲突,使用的版本中LoaderOptions没有setCodePointLimit这个方法.
使用的springboot的依赖的是1.30.0,显式依赖1.33.0即可.

        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>
```  

# 配置错误导致的报错  
这类报错种类比较多  
比如  
* `DataNodesMissedWithShardingTableException`
* `ShardingRuleNotFoundException`  
* `InconsistentShardingTableMetaDataException`  

等等, 启动就会失败, 因为是读取了配置解析异常.  

这种就要看看对应的错误和配置.  

不过有点奇怪的是一些错误没有打出详细的报错信息.比如:

Caused by: org.apache.shardingsphere.sharding.exception.metadata.DataNodesMissedWithShardingTableException: null
at org.apache.shardingsphere.sharding.rule.TableRule.lambda$checkRule$4(TableRule.java:246) ~[shardingsphere-sharding-core-5.2.1.jar:5.2.1]
at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) ~[shardingsphere-infra-util-5.2.1.jar:5.2.1]
at org.apache.shardingsphere.sharding.rule.TableRule.checkRule(TableRule.java:245) ~[shardingsphere-sharding-core-5.2.1.jar:5.2.1]


看了下是基类没调用super,导致message没有值.看了下这个已经在master分支修好了:
```java
    public ShardingSphereSQLException(final SQLState sqlState, final int typeOffset, final int errorCode, final String reason, final Object... messageArguments) {
        this(sqlState.getValue(), typeOffset, errorCode, reason, messageArguments);
    }
    
    public ShardingSphereSQLException(final String sqlState, final int typeOffset, final int errorCode, final String reason, final Object... messageArguments) {
        this.sqlState = sqlState;
        vendorCode = typeOffset * 10000 + errorCode;
        this.reason = null == reason ? null : String.format(reason, messageArguments);
        // missing super(resaon) here
    }

数据库自动生成的key不能作为route key

但是分布式生成的key可以, 这个在FAQ里有, 有这个错误是刚开始配分布式key的时候配错了.

原文:

[分片] ShardingSphere 除了支持自带的分布式自增主键之外，还能否支持原生的自增主键？
回答：

是的，可以支持。但原生自增主键有使用限制，即不能将原生自增主键同时作为分片键使用。由于 ShardingSphere 并不知晓数据库的表结构，而原生自增主键是不包含在原始 SQL 中内的，因此 ShardingSphere 无法将该字段解析为分片字段。如自增主键非分片键，则无需关注，可正常返回；若自增主键同时作为分片键使用，ShardingSphere 无法解析其分片值，导致 SQL 路由至多张表，从而影响应用的正确性。而原生自增主键返回的前提条件是 INSERT SQL 必须最终路由至一张表，因此，面对返回多表的 INSERT SQL，自增主键则会返回零。

分表分库的规则思考

最开始的时候对于分库分表无脑两个都用了MOD, 但因为分区数和分表数是一样的(都是2).
所以mod 2数据的分布也是一样的,这就导致了sharding_test_0的user_1是没有数据的,sharding_test_1的user_0也是没有数据的.
分表了个寂寞.

不只是一样,其实只要分库和分表数最大公约数不为1如果无脑MOD都会有倾斜的问题.
可以代码验证下:

int dbShard = 6;
int tableShard = 32;

Map<Tuple2<Integer, Integer>, Integer> count = new TreeMap<>();

for (int i = 0; i < dbShard; i++) {
    for (int j = 0; j < tableShard; j++) {
        count.put(Tuple.tuple(i, j), 0);
    }
}

for (int i = 0; i < 100000; i++) {
    count.computeIfPresent(Tuple.tuple(i % dbShard, i % tableShard), (k, v) -> v + 1);
}
count.forEach((k,v) -> {
    System.out.println(k + ":" + v);
});

因为前司我经手的项目用的都是分表,还没有到分库,没有意识到这个问题,也算是一点点小经验吧,要考虑下分库分表的规则组合会不会导致数据倾斜.

其他还有些实践中的问题,当时没有记录把配置整对之后也不知道怎么复现了.
不得不说shardingsphere-jdbc的易用性是非常高了,通俗易懂.

参考

shardingsphere官网: https://shardingsphere.apache.org
shardingsphere-jdbc配置: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/
shardingsphere FAQ: https://shardingsphere.apache.org/document/current/cn/faq/
How to get generated ID after I inserted into a new data record in database using Spring JDBCTemplate?

2022-10-26

[翻译]单线程共享可变性的问题

原文链接: https://manishearth.github.io/blog/2015/05/17/the-problem-with-shared-mutability/
作者: Manish Goregaokar
翻译 by abaabaqua

注: 这篇文章写的比较早,早期的Rust编译器有很多不友善的地方,代码示例中部分当时无法编译的现在已经可以了,译者会增改一些代码导致和原文不同.

Edit(2017年1月):我重新发现了Niko的文章,文章也提到了这一点并达到了同样的认识.我怀疑我下意识地从这篇文章中得到了这个想法,至少是部分的想法.

这是我一直想写的一篇文章;Rust 1.0的发布给了我继续接下去的完美动力.

虽然这篇文章是讨论Rust设计中的一个选择,并且例子都是用Rust写的,但这里讨论的原则在很大程度上适用于其它语言.我还将尝试让那些没有Rust背景的人更容易理解这篇文章;如果需要解释一些代码或术语,请告诉我.

我将在这里讨论的是Rust做的一个选择,不允许同一数据有多个可变别名(或者当有active的不可变别名时只能有一个可变别名) ，即使是在同一个线程.本质上讲,它不允许做这样的事情:

let mut x = Vec::new();
{
    let ptr = &mut x; // 获取队`x`的可变引用
    ptr.push(1); // 允许
    let y = x[0]; // 不允许 (无法编译): 只要`ptr`是active的,
                  // x不能读取 ...
    x.push(1);    // .. 也不能写入
    println!("{:?}", ptr); // 这段是译者新加 现在的编译器限制判断是否active更加智能 不是看整个作用域了
}

// 另外一种,

let mut x = Vec::new();
x.push(1); // 允许
{
    let ptr = &x; // 创建一个不可变引用
    let y = ptr[0]; // 允许, 不改变值
    let y = x[0]; // 同样允许
    x.push(1); // 不允许 (无法编译): 只要`ptr`是active,
               // `x`被冻结了可变性
    println!("{:?}", ptr); // 译者新加 原因一样
}

2022-05-11

flink kafka source的avro反序列化问题

对接了一个kafka,里面的topic使用avro编码,且每个topic的schema都不相同使用schema registry管理。
现在要反序列化key和message,查看了现有的:

1 2	<groupId>org.apache.flink</groupId> <artifactId>flink-avro-confluent-registry</artifactId>

内的ConfluentRegistryAvroDeserializationSchema.
发现无论是forSpecific返回一个特定的类,还是forGeneric返回GenericRecord都需要指定read schame,而registry仅作为write schema使用, 也就是虽然返回的是GenericRecord, 但实际上都是同一个Schema反序列化的, 每个record的格式是一致的.

2022-04-17

windwos下运行hadoop相关代码(hdfs,mr)

最近在学习一些hadoop相关的知识,需要在本地运行hdfs和mr相关的代码.
因为开发机使用的是windows,所以会有一些不太方便.

可以在windows下解压hadoop,然后在应用的env中指定HADOOP_HOME为解压路径.
在windows中需要winutils.exe工具,当前网上好心人预编译好的只到3.3.0.
见: https://kontext.tech/article/446/hadoop-330-winutils

2022-03-20

spring boot应用从mysql迁移h2

此前有个应用用了mysql做数据存储，但实际存储的数据很少就几张表加起来一共几千行，并且一直没有修改的需求，应用内也只是进行查询。
实际上是静态的数据。

因为使用的是云服务商的产品，想着之后就不续费了需要将数据迁移。

有几个选项：

改用文件存储，读取到内存后处理；
换成嵌入式数据库。
Read More

2022-02-19

prestosql(trino) hive时间戳显示不正确排查

TLDR: 服务器的时区设置有问题。

小麦的杂货铺

日常随笔~

ChatGPT Plugin开发setup - Java(Spring Boot) Python(fastapi)

leptos手工集成tailwindcss

[翻译]没有线程

高性能MySQL(第4版) 第一章 MySQL架构读书笔记

MySQL的逻辑架构

shardingsphere-jdbc 水平分表学习记录

环境

配置