mycat2 分片表，用雪花算法生成自增Id，为什么被分到错误的库和表里了？

[zhaozw1984]

mycat2_1.21和mysql 8.0.23

用的是自动mod_hash分片 DBPARTITION BY mod_hash(id) DBPARTITIONS TBPARTITION BY mod_hash(id) TBPARTITIONS 2

id=698914537197277270 这条记录本应该在zzwtest_1.travelrecord_fenpian_2里，但是却被分到zzwtest_0.travelrecord_fenpian_0里。

[junwen12221]

CREATE TABLE db1.test (
	`id` BIGINT(22) NOT NULL AUTO_INCREMENT,
	`sql` LONGTEXT,
	`fix_plan_id` BIGINT(22) DEFAULT NULL,
	KEY `id` (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci   DBPARTITION BY mod_hash(id) DBPARTITIONS 2 TBPARTITION BY mod_hash(id) TBPARTITIONS 2;

INSERT INTO `db1`.`test` (`id`) VALUES (698914537197277270); 
SELECT * FROM db1.test WHERE id = 698914537197277270;
 
INSERT INTO `db1`.`test` (`id`) VALUES ('698914537197277270'); 
SELECT * FROM db1.test WHERE id = '698914537197277270';

我用这个测试没有复现，要检查一下本地文件的配置对不对（根据SQL生成的分片表配置）当分库分表键相同的时候是id%（2*2）

CREATE TABLE db1.test (
	`id` BIGINT(22) NOT NULL AUTO_INCREMENT,
	`sql` LONGTEXT,
	`fix_plan_id` BIGINT(22) DEFAULT NULL,
	KEY `id` (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci   DBPARTITION BY mod_hash(id) DBPARTITIONS 2 TBPARTITION BY mod_hash(id) TBPARTITIONS 2;

EXPLAIN  INSERT INTO `db1`.`test` (`id`) VALUES (698914537197277270); //698914537197277270%4 = 2

另外这是最新版本 http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies-2022-4-20.jar

[zhaozw1984]

带id固定值方式插入是正常的，会分到正确的库表，我是不带id插入的。大概插入100条就出现2条有问题的数据。

[junwen12221]

  public void test672() throws Exception {
        try (Connection mySQLConnection = getMySQLConnection(DB_MYCAT)) {
            execute(mySQLConnection, "CREATE TABLE db1.test (\n" +
                    "\t`id` BIGINT(22) NOT NULL AUTO_INCREMENT,\n" +
                    "\t`sql` LONGTEXT,\n" +
                    "\t`fix_plan_id` BIGINT(22) DEFAULT NULL,\n" +
                    "\tKEY `id` (`id`)\n" +
                    ") ENGINE = INNODB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci   DBPARTITION BY mod_hash(id) DBPARTITIONS 2 TBPARTITION BY mod_hash(id) TBPARTITIONS 2;\n");
        }
        NameableExecutor executor = ExecutorUtil.create("text", 2);
        CountDownLatch countDownLatch = new CountDownLatch(2);
        for (int i = 0; i < executor.getCorePoolSize(); i++) {
            executor.execute(new Runnable() {
                @Override
                @SneakyThrows
                public void run() {
                    try {
                        while (true) {
                            try (Connection mySQLConnection = getMySQLConnection(DB_MYCAT)) {


                                Statement statement = mySQLConnection.createStatement();
                                statement.execute("INSERT INTO `db1`.`test` (`fix_plan_id`) VALUES (1); ",Statement.RETURN_GENERATED_KEYS);
                                ResultSet generatedKeys = statement.getGeneratedKeys();
                                generatedKeys.next();
                                BigInteger id = (BigInteger) generatedKeys.getObject(1);
                                long l = id.longValue() % 4;
                                List<Map<String, Object>> maps = executeQuery(mySQLConnection, "select * from `db1`.`test` where id = " + id);
                                Assert.assertEquals(1,maps.size());

                                List<Map<String, Object>> explain = executeQuery(mySQLConnection, "explain select * from `db1`.`test` where id = " + id);
                                Assert.assertTrue(explain.toString().contains("`test_"+l+"`"));
                                System.out.println();
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    } finally {
                        countDownLatch.countDown();
                    }
                }
            });
        }
        countDownLatch.await();
        executor.shutdown();
    }

这是我的测试程序,没有复现

[zhaozw1984]

你测试是什么日期的版本？

[junwen12221]

mycat2_1.21_2022-4-20 你看一下你jar的文件日期

[zhaozw1984]

我的是4.1的日期。

[junwen12221]

换新的看看,如果有这个问题还是会有的