Bug #67928 select cannot find records by primary key
Submitted: 17 Dec 2012 17:04 Modified: 9 Jan 2015 16:18
Reporter: NOT_FOUND NOT_FOUND Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:7.2.9, 7.1.25 OS:Linux (Linux Centos 6.3 64-bit)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[17 Dec 2012 17:04] NOT_FOUND NOT_FOUND
Description:
For a table with over 1-2 million records a query like:

select * from table where primary_key=X 

doesn't return any records.

How to repeat:
The case was run on 2 data node cluster (2-core x86 intel, 24GB), both 7.2.9 and 7.1.25 versions, see cluster configuration report in private comment.

How to repeate: 

Setup a clear 2 data node cluster like from config and load the test table from private comment.

mysql> create database db1;

shell$> mysql -u root -p db1 < bc1.sql

mysql> show create table bc1 \G;
*************************** 1. row ***************************
       Table: bc1
Create Table: CREATE TABLE `bc1` (
  `cookieid` int(10) unsigned NOT NULL,
  `cookie` varchar(4000) CHARACTER SET latin2 DEFAULT NULL,
  `cookie_sek` varchar(4000) CHARACTER SET latin2 DEFAULT NULL,
  `cookie_retarget` varchar(4000) CHARACTER SET latin2 DEFAULT NULL,
  `data` date DEFAULT NULL,
  PRIMARY KEY (`cookieid`),
  KEY `Index_data` (`data`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select cookieid from bc1 order by cookieid limit 10;
+----------+
| cookieid |
+----------+
|        1 |
|        5 |
|       15 |
|       25 |
|       35 |
|       45 |
|       55 |
|       65 |
|       75 |
|       85 |
+----------+
10 rows in set (0.01 sec)

mysql> select cookieid from bc1 where cookieid=45;
Empty set (0.00 sec)

but should return record with 45 value.
[19 Dec 2012 11:32] Umesh Shastry
Hello Maciej Piecko,

Thank you for the report.

Verified as described on reported version.

Thanks,
Umesh
[21 Dec 2012 10:25] Jon Stephens
This issue is fixed in the next Cluster releases (7.1.26, 7.2.10).

Docs info in progress.
[4 Jan 2013 12:22] Umesh Shastry
Can't repeat on 7.2.10.

[ushastry@cluster-repo mysql-cluster-com-7_2_10]$ bin/mysql -u root -p db1 < bc1.sql
Enter password:

mysql> select version();
+-----------------------------------------------+
| version()                                     |
+-----------------------------------------------+
| 5.5.29-ndb-7.2.10-cluster-commercial-advanced |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> use db1
Database changed
mysql> select cookieid from bc1 order by cookieid limit 10;
+----------+
| cookieid |
+----------+
|        1 |
|        5 |
|       15 |
|       25 |
|       35 |
|       45 |
|       55 |
|       65 |
|       75 |
|       85 |
+----------+
10 rows in set (0.09 sec)

mysql> select cookieid from bc1 where cookieid=45;
+----------+
| cookieid |
+----------+
|       45 |
+----------+
1 row in set (0.02 sec)

mysql> select cookieid from bc1 where cookieid=15;
+----------+
| cookieid |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

mysql>

Regards,
Umesh
[7 Jan 2013 9:06] NOT_FOUND NOT_FOUND
Fixed in 7.2.10.
[9 Jan 2013 10:45] Jon Stephens
This bug should not have been closed by the submitter; doing so prematurely can short-circuit our documentation processes. (We don't normally close a bug until after the fix for it has been documented).
[9 Jan 2013 10:50] Jon Stephens
Fixed in MySQL Cluster NDB 7.0+. Documented in the NDB 7.0.37, 7.1.27, and 7.2.10 changelogs as follows:

      When NDB tables grew to contain approximately one million rows 
      or more per partition, it became possible to insert rows having 
      duplicate primary keys into them. In addition, primary key lookups 
      began to fail, even if a matching row existed in the table.

Status unchanged (since was already closed).
[9 Jan 2013 12:12] Jon Stephens
See the changelog entry for some additional commentary regarding upgrades/downgrades.