Bug #60989 critical query result loss. It's a shock.
Submitted: 28 Apr 2011 4:41 Modified: 28 May 2011 6:44
Reporter: ws lee Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:mysql5.1.32-ndb6.3.24 OS:Solaris (SunOS 5.10)
Assigned to: CPU Architecture:Any

[28 Apr 2011 4:41] ws lee
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 | 
+--------------+-----------+
[28 Apr 2011 4:46] ws lee
my mysql cluster cofig is 2 data node, 2 sql node.
[28 Apr 2011 6:44] Martin Skold
Nothing reported on anything similar to this and some quick tests
on a recent lease shows nothing. You are running a fairly old release,
please try a GA release from:
http://dev.mysql.com/downloads/cluster/
[28 May 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".