Description:
In mysql cluster, Any query occurr loss of result, often.
in example,
in table has data 1,2,3,4 of status=1.
but result of select * from t1 where status=1) is
only 2,4 searched.
all date 1,2,3,4 not searched.
How to repeat:
Table t1 desc is below.
mysql> desc t1;
+------------+---------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| rev | int(11) | NO | | 0 | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
| date2 | timestamp | YES | | NULL | |
| id2 | smallint(6) | NO | MUL | NULL | |
| id3 | int(11) | NO | MUL | NULL | |
| id4 | int(11) | NO | MUL | NULL | |
| status | tinyint(4) | NO | MUL | 0 | |
| command | varchar(15) | NO | | NULL | |
| parameters | text | YES | | NULL | |
| response | varchar(1000) | YES | | NULL | |
+------------+---------------+------+-----+-------------------+----------------+
Currently t1 table have 8000000 record.
This record increasing continously.
At time '2011-04-26 16:08:12' and '2011-04-26 16:08:13' record inserted.
+---------+---------------------+---------------------+------+--------+---------+------------+
| id | date | date2 | id2 | status | command | parameters |
+---------+---------------------+---------------------+------+--------+---------+------------+
| 8632094 | 2011-04-26 16:08:12 | 2011-04-26 16:08:15 | 1101 | 0 | a | a=param1 |
| 8632095 | 2011-04-26 16:08:12 | 2011-04-26 16:08:13 | 1101 | 0 | b | b=param1 |
| 8632096 | 2011-04-26 16:08:13 | 2011-04-26 16:08:15 | 1101 | 0 | c | c=param1 |
| 8632097 | 2011-04-26 16:08:13 | 2011-04-26 16:08:13 | 1101 | 0 | d | d=param1 |
+---------+---------------------+---------------------+------+--------+---------+------------+
※
date is record insert time.
date2 is reccord update time.
And thus, simultanesously in other session at '2011-04-26 16:08:13'
this query executed.
mysql> SELECT id, id2, command, parameters FROM t1 WHERE status = 0 AND id2 IN (1101) ORDER BY id LIMIT 30;
Query result was below. Amasinsing shock.
+---------+------+---------+------------+
| id | id2 | command | parameters |
+---------+------+---------+------------+
| 8632095 | 1101 | b | b=param1 |
| 8632097 | 1101 | d | d=param1 |
+---------+------+---------+------------+
and my script is this record update at '2011-04-26 16:08:13'
view upper date2 filed.
right result is below.
+---------+------+---------+------------+
| id | id2 | command | parameters |
+---------+------+---------+------------+
| 8632094 | 1101 | a | a=param1 |
| 8632095 | 1101 | b | b=param1 |
| 8632096 | 1101 | c | c=param1 |
| 8632097 | 1101 | d | d=param1 |
+---------+------+---------+------------+
this problem occurring often.
i think that this is mysql cluster bug.
※SELECT id, id2, command, parameters FROM t1 WHERE status = 0 AND id2 IN (1101) ORDER BY id LIMIT 30;
this query use index idx_id2 of id2 filed.
id2's record count is below.
mysql> select id2,count(*) from t1 group by id2;
+--------------+-----------+
| brand_obj_id | count(*) |
+--------------+-----------+
| 1001 | 1341142 |
| 1002 | 5799715 |
| 1003 | 177890 |
| 1005 | 238468 |
| 1006 | 541 |
| 1007 | 2000 |
| 1008 | 22401 |
| 1101 | 196494 |
| 1102 | 16637 |
+--------------+-----------+
