MySQL Bugs: #25333: ERROR 1032 (HY000): Can't find record in '<table>'

Bug #25333	ERROR 1032 (HY000): Can't find record in '<table>'
Submitted:	29 Dec 2006 17:08	Modified:	31 Aug 2007 16:43
Reporter:	Matthew Boehm	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Linux (RHEL4)
Assigned to:	Hartmut Holzgraefe	CPU Architecture:	Any
Tags:	can't, cluster, error, join, record

Description:
Just converted our tables to NDB and now some of our simple queries are failing. Once such is a join involving 2 tables:

CREATE TABLE `subscriptions` (
  `companyID` int(11) NOT NULL default '0',
  `product_id` int(11) NOT NULL default '0',
  `quantity` float(9,2) NOT NULL default '0.00',
  `discountRate` float(5,4) NOT NULL default '0.0000',
  `custom` varchar(30) collate utf8_bin NOT NULL default '',
  `privateComments` varchar(200) collate utf8_bin NOT NULL default '',
  `publicComments` varchar(200) collate utf8_bin NOT NULL default '',
  KEY `productIDFK` (`product_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `products` (
  `product_id` int(11) NOT NULL auto_increment,
  `product_code` varchar(5) collate utf8_bin NOT NULL default '',
  `product_name` varchar(35) collate utf8_bin NOT NULL default '',
  `product_description` varchar(100) collate utf8_bin NOT NULL default '',
  `product_def_price` float(7,2) NOT NULL default '0.00',
  `product_taxable` tinyint(1) NOT NULL default '0',
  `product_create_date` int(11) NOT NULL default '0',
  `product_parentID` int(11) NOT NULL default '0',
  `product_has_price_break` tinyint(1) NOT NULL default '0',
  `product_is_discountable` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`product_id`),
  UNIQUE KEY `product_code` (`product_code`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Tables are populated with data.

SELECT s.product_id, s.quantity, s.custom, s.privateComments, s.publicComments, p.product_code, p.product_name FROM subscriptions s LEFT JOIN products p USING(product_id) WHERE s.companyID = 1;

<returns results>

SELECT s.product_id, s.quantity, s.custom, s.privateComments, s.publicComments, p.product_code, p.product_name FROM subscriptions s LEFT JOIN products p USING(product_id) WHERE s.companyID = 1 ORDER BY s.custom

ERROR 1032 (HY000): Can't find record in 's'

Uh? Adding an ORDER BY and you suddenly can't find anything?

Now this one blows my mind (simply remove s.publicComments):

SELECT s.product_id, s.quantity, s.custom, s.privateComments, p.product_code, p.product_name FROM subscriptions s LEFT JOIN products p USING(product_id) WHERE s.companyID = 1 ORDER BY s.custom;

<returns results>

What? That returned data? There's nothing special about that publicComments field. Let keep blowing minds here:

SELECT * FROM subscriptions WHERE companyID = 1;  <works>

SELECT * FROM subscriptions WHERE companyID = 1 ORDER BY custom; <failes>

SELECT s.product_id, s.quantity, s.custom, s.privateComments FROM subscriptions s WHERE companyID = 1 ORDER BY s.custom;  <works>

SELECT s.product_id, s.quantity, s.custom, s.publicComments FROM subscriptions s WHERE companyID = 1 ORDER BY s.custom; <works>

SELECT s.product_id, s.quantity, s.custom, s.privateComments, s.publicComments FROM subscriptions s WHERE companyID = 1 ORDER BY s.custom; <failes>

SELECT s.privateComments, s.publicComments FROM subscriptions s WHERE companyID = 1 ORDER BY s.custom; <failes>

SELECT s.privateComments, s.publicComments FROM subscriptions s WHERE companyID = 1; <works>

How to repeat:
Not sure how this happened in the first place.

Suggested fix:
No workaround at this time via SQL. Probably will have to convert table back to InnoDB so that we can use it.

Changed 'custom' column to CHAR() makes no difference.
Adding INDEX to 'custom' makes no difference.

Say what? This works?

SELECT s.product_id, s.quantity, s.publicComments, s.privateComments, s.custom, p.product_name, p.product_parentid FROM subscriptions s LEFT JOIN products p USING(product_id) WHERE s.companyID = 1 ORDER BY custom, p.product_parentid ASC;

Yep. Sure does. What the heck is going on? If I remove that 2nd ORDER BY column the query fails.

Hello Matthew, 

i was not able to reproduce this yet, are you  able to provide table data showing the wrong behavior for reproduction on our side?

I'll provide anything you want. Tell me exactly what you need and I'll provide.

Hi Matthew, 

we'd need a mysqldump of your "products" and "subscriptions" tables. 
You can upload the dump files as private files only visible to MySQL
staff using the "Files" tab in the bug form:

  http://bugs.mysql.com/bug.php?id=25333&files=1

or upload them to our FTP server at 

  ftp://ftp.mysql.com/pub/mysql/upload/

if their size exceeds 200KB

Hello Hartmut,
We are having the same thing with a very simple select with an ORDER BY from a table. Can I help in any way towards the solution for this matter?
Cheers
Cass

I am also seeing this issue.  We are running mysql 5.0.24a.
Our table is an InnoDB table where multiple processes are simultaneously doing update order by queries.

The problem comes and goes, some connections get the error while others don't.

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

Just reopening the bug per the automated system. I gave everything asked for. Waiting on developer response.

Since it takes this long for the problem to be dealt with, we are dropping the clustering and using drbd. Maybe our next attempt will turn out better.

Hi Matthew

I retested your sql-statements with the data you provieded.
I tested on 5.0.38
I tested with a 1-node cluster.

They all worked fine.

/Jonas

Same here, tested with both 5.0.27 and a current 5.0 version,
with both engine_condition_pushdown enabled and disabled, 
on a 1, 2 and 4 data nodes configurations but was never able
to reproduce the problems with the provided dumps and queries ...