Spider for MySQL

data is lost when updating the partition key of a record with a value that will force the move of the record to another server

Bug #645780 reported by Frederic Descamps on 2010-09-23

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	Spider for MySQL	Fix Released	High	Kentoku SHIBA

Bug Description

This is what I have :

mysql [localhost] {msandbox} (asp) > select * from trace_ejb6 where id =15;
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| id | timestamp | timestamp2 | latitude | longitude | speed | heading | terminalID | mileage | creationtime | tracetype | customerID |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| 15 | 1155292399058 | 2006-08-11 12:33:19 | 5085231 | 473206 | NULL | NULL | 0 | NULL | 0 | 0 | 0 |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
1 row in set (0.01 sec)

details of the table :

CREATE TABLE `trace_ejb6` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` bigint(20) NOT NULL,
  `timestamp2` datetime DEFAULT NULL,
  `latitude` int(11) DEFAULT NULL,
  `longitude` int(11) DEFAULT NULL,
  `speed` smallint(6) DEFAULT NULL,
  `heading` smallint(6) DEFAULT NULL,
  `terminalID` int(11) NOT NULL,
  `mileage` int(11) DEFAULT NULL,
  `creationtime` bigint(20) NOT NULL,
  `tracetype` int(11) DEFAULT NULL,
  `customerID` int(11) NOT NULL DEFAULT '0',
  KEY `id` (`id`,`customerID`),
  KEY `idx_trace_tracetype` (`tracetype`),
  KEY `idx_terminalID_timestamp` (`terminalID`,`timestamp`),
  KEY `idx_terminalID_timestamp2` (`terminalID`,`timestamp2`),
  KEY `idx_creationtime` (`creationtime`)
) ENGINE=SPIDER AUTO_INCREMENT=5309812 DEFAULT CHARSET=latin1 CONNECTION=' table "trace_ejb6", user "msandbox",\npassword "msandbox" '
/*!50100 PARTITION BY RANGE (timestamp)
(PARTITION pt1 VALUES LESS THAN (1199142000000) COMMENT = 'host "127.0.0.1", port "16850"' ENGINE = SPIDER,
PARTITION pt2 VALUES LESS THAN (1270072800000) COMMENT = 'host "127.0.0.1", port "16849"' ENGINE = SPIDER,
PARTITION pt3 VALUES LESS THAN MAXVALUE COMMENT = 'host "127.0.0.1", port "16848"' ENGINE = SPIDER)

mysql [localhost] {msandbox} (asp) > update trace_ejb6 set timestamp = unix_timestamp(now())*1000 where id=15;
Query OK, 1 row affected (0.24 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql [localhost] {msandbox} (asp) > select * from trace_ejb6 where id =15;Empty set (0.00 sec)

in the binlog of the spider server :

# at 181
#100922 10:17:22 server id 5144 end_log_pos 323 Query thread_id=4 exec_time=0 error_code=0
use asp/*!*/;
SET TIMESTAMP=1285143442/*!*/;
update trace_ejb6 set timestamp = unix_timestamp(now())*1000 where id=15
/*!*/;

in the binlog of the shard where the new record should be moved (pt3):

# at 1535
#100922 10:17:22 server id 101 end_log_pos 1672 Query thread_id=43 exec_time=0 error_code=0
SET TIMESTAMP=1285143442/*!*/;
insert high_priority into `asp`.`trace_ejb6`(`timestamp`)values(1285143442000)
/*!*/;

in the binlog of the shard where the data was (pt1):

# at 3512
#100922 10:17:22 server id 103 end_log_pos 3897 Query thread_id=44 exec_time=0 error_code=0
SET TIMESTAMP=1285143442/*!*/;
delete from `asp`.`trace_ejb6` where `id` = 15 and `timestamp` = 1155292399058 and `timestamp2` = '2006-08-11 12:33:19' and `latitude` = 5085231 and `longitude` = 473206 and `speed` is null and `heading` is null and `terminalID` = 0 and `mileage` is null and `creationtime` = 0 and `tracetype` = 0 and `customerID` = 0 limit 1
/*!*/;

What I expect is that on the shard where the record should be moved instead of having :
insert high_priority into `asp`.`trace_ejb6`(`timestamp`)values(1285143442000)
I expect something like :

insert high_priority into `asp`.`trace_ejb6`(`id,`timestamp`,`timestamp2`,`latitude`,`longitude`,`speed`,`heading`,`terminalID`,`mileage`,`creationtime`,`tracetype`) values (15,1285143442000,'2006-08-11 12:33:19',5085231,473206,null,null,0,null,0,0,0)

Thank you for your help.

Kentoku SHIBA (kentokushiba) on 2010-09-25

Changed in spiderformysql:
assignee:	nobody → Kentoku SHIBA (kentokushiba)
importance:	Undecided → High

Revision history for this message

Kentoku SHIBA (kentokushiba) wrote on 2010-10-03:

spider-src-2.22b-for-5.1.44.tgz Edit (164.3 KiB, application/x-tar)

Please use this attached file for avoiding this problem.

Changed in spiderformysql:
status:	New → In Progress

Revision history for this message

Frederic Descamps (lefred) wrote on 2010-10-06:

Hi Kentoku,

I tested it today and it works :)

on node3 :
select * from trace_ejb6 limit 1;
----+---------------+---------------------+----------+-----------+-------+---------+------------+----------+--------------+-----------+------------+
| id | timestamp | timestamp2 | latitude | longitude | speed | heading | terminalID | mileage | creationtime | tracetype | customerID |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+----------+--------------+-----------+------------+
| 16 | 1155292399058 | 2006-08-11 12:33:19 | 5085231 | 473206 | NULL | NULL | 0 | NULL | 0 | 0 | 0 |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+----------+--------------+-----------+------------+

then on the spider node :

update trace_ejb6 set timestamp = unix_timestamp(now())*1000 where id=16;

on the same node (spider) :

select * from trace_ejb6 where id =16;+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| id | timestamp | timestamp2 | latitude | longitude | speed | heading | terminalID | mileage | creationtime | tracetype | customerID |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| 16 | 1286372530000 | 2006-08-11 12:33:19 | 5085231 | 473206 | NULL | NULL | 0 | NULL | 0 | 0 | 0 |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
1 row in set (0.00 sec)

the record is not more on node 3 but now it's on node 1 :

select * from trace_ejb6 where id =16;
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| id | timestamp | timestamp2 | latitude | longitude | speed | heading | terminalID | mileage | creationtime | tracetype | customerID |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| 16 | 1286372530000 | 2006-08-11 12:33:19 | 5085231 | 473206 | NULL | NULL | 0 | NULL | 0 | 0 | 0 |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
1 row in set (0.00 sec)

Thank you for having fixed this bug

Fred.

Hi Kentoku,

I tested it today and it works :)

on node3 :
select * from trace_ejb6 limit 1;
----+---------------+---------------------+----------+-----------+-------+---------+------------+----------+--------------+-----------+------------+
| id | timestamp     | timestamp2          | latitude | longitude | speed | heading | terminalID | mileage  | creationtime | tracetype | customerID |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+----------+--------------+-----------+------------+
| 16 | 1155292399058 | 2006-08-11 12:33:19 |  5085231 |    473206 |  NULL |    NULL |          0 |     NULL |            0 |         0 |          0 |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+----------+--------------+-----------+------------+

then on the spider node :

update trace_ejb6 set timestamp = unix_timestamp(now())*1000 where id=16;

on the same node (spider) :

select * from trace_ejb6 where id =16;+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| id | timestamp     | timestamp2          | latitude | longitude | speed | heading | terminalID | mileage | creationtime | tracetype | customerID |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| 16 | 1286372530000 | 2006-08-11 12:33:19 |  5085231 |    473206 |  NULL |    NULL |          0 |    NULL |            0 |         0 |          0 |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
1 row in set (0.00 sec)

the record is not more on node 3 but now it's on node 1 :

select * from trace_ejb6 where id =16;
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| id | timestamp     | timestamp2          | latitude | longitude | speed | heading | terminalID | mileage | creationtime | tracetype | customerID |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
| 16 | 1286372530000 | 2006-08-11 12:33:19 |  5085231 |    473206 |  NULL |    NULL |          0 |    NULL |            0 |         0 |          0 |
+----+---------------+---------------------+----------+-----------+-------+---------+------------+---------+--------------+-----------+------------+
1 row in set (0.00 sec)

Thank you for having fixed this bug

Fred.