xiaoti2 commented on issue #37264:
URL:
https://github.com/apache/shardingsphere/issues/37264#issuecomment-3604815193
用ShardingSphere 5.5.1，5.5.2 分 32个 分片，做 全量 和 增量 迁移 CREATE SHARDING TABLE
RULE device_ota_upgrade_record(
STORAGE_UNITS(ds_1), SHARDING_COLUMN=product_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="32"))
);
product_id 是bigint 。
增量数据插入：
device_ota_upgrade_record_3 插入的数据， 部分跑到了 device_ota_upgrade_record_29
device_ota_upgrade_record_4 插入的数据， 部分跑到了 device_ota_upgrade_record_28，
device_ota_upgrade_record_5插入的数据， 部分跑到了 device_ota_upgrade_record_27
好像挺有规律的，3+29=32,4+28=32,5+27=32。不知道是配置哪方面出了问题。 数据校验是没问题的。全量迁移算法和增量算法不一致？
问了DeepSeek ： (product_id.hashCode() & Integer.MAX_VALUE) % 32 MAX_VALUE =
0x7fffffff; Math.abs((long) shardingValue.hashCode()) % shardingCount 算法 的差异
有点像 全量使用的 Math.abs((long) shardingValue.hashCode()) % shardingCount 算法， 增量使用的
(product_id.hashCode() & Integer.MAX_VALUE) % 32 MAX_VALUE = 0x7fffffff;
算法，再hash_mod 为负数的时候， 增量数据 写入到了 “互补余数”的分片数。
select count(*) cnt,product_id from device_ota_upgrade_record_3 group by
product_id;
+-----+---------------------+
| cnt | product_id |
+-----+---------------------+
| 123 | 1594642343748886528 |
| 105 | 1914264300839890944 |
+-----+---------------------+
2 rows in set (0.00 sec)
增量插入： insert into device_ota_upgrade_record(id,product_id)
values(88888888881,1914264300839890944);
select * from device_ota_upgrade_record_29 where id =88888888881;
落到29个分片。
select count(*) cnt,product_id from device_ota_upgrade_record_4 group by
product_id; （全量数据分布）
+------+---------------------+
| cnt | product_id |
+------+---------------------+
| 3034 | 28 |
| 124 | 92 |
| 1110 | 1714832026754129920 |
| 128 | 1812805249227071488 |
+------+---------------------+
4 rows in set (0.01 sec)
增量插入：
insert into device_ota_upgrade_record(id,product_id) values(555552
,1714832026754129920);
insert into device_ota_upgrade_record(id,product_id) values(555553
,1812805249227071488);
预期落在device_ota_upgrade_record_4 分片，实际device_ota_upgrade_record_28 。
select * from device_ota_upgrade_record_28 where id =555552;
select * from device_ota_upgrade_record_28 where id =555553;
8个分片也是一样：全量迁移，product_id数据 分布
mysql> select count(*) cnt,product_id from device_ota_upgrade_record_5 group
by product_id;
+-------+---------------------+
| cnt | product_id |
+-------+---------------------+
| 131 | 35 |
| 1 | 91 |
| 6797 | 155 |
| 2 | 1537616184546033664 |
| 96 | 1539892061970046976 |
| 5 | 1542415541089280000 |
| 34787 | 1729402509999788032 |
| 10837 | 1812812533583880192 |
+-------+---------------------+
8 rows in set (0.01 sec)
增量插入：
insert into device_ota_upgrade_record(id,product_id) values(7888880 , 91);
insert into device_ota_upgrade_record(id,product_id) values(7888881 , 155);
insert into device_ota_upgrade_record(id,product_id) values(7888882 ,
1537616184546033664);
insert into device_ota_upgrade_record(id,product_id) values(7888883 ,
1729402509999788032);
查询增量迁移结果：
mysql> select * from device_ota_upgrade_record_5 where id >= 7888880 and id
<= 7888883;
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| id | device_id | device_name | ota_task_id |
device_curr_firmware_version | state | describe | product_id |
upgrade_firmware_version | created_time | last_modified_time | start_time |
end_time | error_code | detail_code |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| 7888883 | 0 | | 0 | NULL
| 0 | NULL | 1729402509999788032 | NULL |
0 | 0 | NULL | NULL | NULL | NULL |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
1 row in set (0.00 sec)
mysql> select * from device_ota_upgrade_record_3 where id >= 7888880 and id
<= 7888883;
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| id | device_id | device_name | ota_task_id |
device_curr_firmware_version | state | describe | product_id |
upgrade_firmware_version | created_time | last_modified_time | start_time |
end_time | error_code | detail_code |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
| 7888880 | 0 | | 0 | NULL
| 0 | NULL | 91 | NULL |
0 | 0 | NULL | NULL | NULL | NULL |
| 7888881 | 0 | | 0 | NULL
| 0 | NULL | 155 | NULL |
0 | 0 | NULL | NULL | NULL | NULL |
| 7888882 | 0 | | 0 | NULL
| 0 | NULL | 1537616184546033664 | NULL |
0 | 0 | NULL | NULL | NULL | NULL |
+---------+-----------+-------------+-------------+------------------------------+-------+----------+---------------------+--------------------------+--------------+--------------------+------------+----------+------------+-------------+
3 rows in set (0.00 sec)
<img width="761" height="560" alt="Image"
src="https://github.com/user-attachments/assets/3ea1ee3f-64f6-4ae7-97e3-2605fea45dd6";
/>
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
