MySQL Bugs: #67928: select cannot find records by primary key

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

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.

Hello Maciej Piecko,

Thank you for the report.

Verified as described on reported version.

Thanks,
Umesh

This issue is fixed in the next Cluster releases (7.1.26, 7.2.10).

Docs info in progress.

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

Fixed in 7.2.10.

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).

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).

See the changelog entry for some additional commentary regarding upgrades/downgrades.