How to Repeat
1. Create tables on Spider and Remote backends and Insert some rows. (The table is with float fields or double fields.)
mysql> CREATE TABLE t (
|
-> id INT PRIMARY KEY -- shard key
|
-> a INT,
|
-> b BLOB,
|
-> f FLOAT
|
-> )ENGINE=SPIDER COMMENT='wrapper "mysql", table "t"'
|
-> PARTITION BY LIST (id%2)
|
-> (
|
-> PARTITION pt0 VALUES IN (0) COMMENT = 'database "test1_0", table "t", server "SPT0"',
|
-> PARTITION pt1 VALUES IN (1) COMMENT = 'database "test1_1", table "t", server "SPT1"'
|
-> );
|
Query OK, 0 rows affected (0.02 sec)
|
|
mysql> INSERT INTO t VALUES(61,76,'b1',4.86947), (60,33,'b2',0), (24,75,'b3',4.61755), (63,76,'b4',4.86947);
|
Query OK, 4 rows affected (0.00 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
mysql> select * from t;
|
+----+------+------+---------+
|
| id | a | b | f |
|
+----+------+------+---------+
|
| 24 | 75 | b3 | 4.61755 |
|
| 60 | 33 | b2 | 0 |
|
| 61 | 76 | b1 | 4.86947 |
|
| 63 | 76 | b4 | 4.86947 |
|
+----+------+------+---------+
|
4 rows in set (0.00 sec)
|
There are four rows in the table, and we do a UPDATE to let one row change its shard. (Update sharding key)
mysql> UPDATE t SET id = 25 WHERE id = 24;
|
Query OK, 1 row affected (0.11 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
mysql> select * from t;
|
+----+------+------+---------+
|
| id | a | b | f |
|
+----+------+------+---------+
|
| 24 | 75 | b3 | 4.61755 |
|
| 60 | 33 | b2 | 0 |
|
| 25 | 75 | b3 | 4.61755 |
|
| 61 | 76 | b1 | 4.86947 |
|
| 63 | 76 | b4 | 4.86947 |
|
+----+------+------+---------+
|
5 rows in set (0.00 sec)
|
Now, we can see there are five rows in the table. This is a serious BUG.
Analysis of the Bug
If we open general log (general_log and spider_general_log), we would see:
1. ....; select `id`,`a`,`b`,`f` from `test1_0`.`t` where `id` = 24 and (`id` = 24) for update
|
2. ...; start transaction;
|
3. insert high_priority into `test1_1`.`t`(`id`,`a`,`b`,`f`)values(25,75,'b3',4.61755);
|
4. delete from `test1_0`.`t` where `id` = 24 and `a` = 75 and `b` = 'b3' and `f` = 4.61755 limit 1;
|
5. commit
|
The logic here is.
First, make a selection to see whether the target sharding key exists.
Second, insert the updated row to the new shard.
Third, delete the outdated row in the previous shard.
When deleting, we must specify the target row, which means we must append some conditions in the WHERE. The solution here is to append every column value from the previous selection and append all of them using =.
This is problematic since we cannot use equivalent signs to compare two floating values.
How to Solve
1. If there is a primary key or a unique key when doing
delete from `update_test`.`t` where ...`
|
we only append the information of the primary key or the unique key in the where condition. (FLOAT | DOUBLE cannot be the primary key / unique key)
2. If no primary key or unique key is found, and if float or double fields are related, we using like in comparison in these fields to substitute '=';
select `id`,`a`,`b`,`f` from `update_test`.`t` where (`id` = 24) for update
|
insert high_priority into `update_test`.`t`(`id`,`a`,`b`,`f`)values(25,75,'b3',4.61755)
|
delete from `update_test`.`t` where `id` = 24 and `a` = 75 and `b` = 'b3' and `f` like 4.61755 limit 1
|
In the previous selection, Spider got the value of column f and String *Item_float::val_str() for the value 4.61755 would be called. Afterward, when Spider uses the LIKE comparator, String *Item_decimal::val_str() for the value 4.61755 would be called. And finally, compare them as two strings.
|
