MySQL Bugs: #67926: Can't find record in 'items'

Bug #67926	Can't find record in 'items'
Submitted:	17 Dec 2012 16:21	Modified:	21 Jan 2013 6:43
Reporter:	Alexander Spitzer	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	mysql-cluster-gpl-7.2.9-linux2.6-x86_64	OS:	Linux (Centos 6.1: Linux 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT)
Assigned to:		CPU Architecture:	Any
Tags:	CentOS, cluster, linux

Description:
under (high?) load, the data becomes corrupt and we get the error:

mysql (root@localhost)> select * from items limit 1;
ERROR 1032 (HY000): Can't find record in 'items'

The ONLY error I see anywhere is in the mysql.err log, where I see:
theNoOfSentTransactions = 0 theListState = 1 theTransArrayIndex = 0
theNoOfSentTransactions = 0 theListState = 1 theTransArrayIndex = 0
theNoOfSentTransactions = 0 theListState = 1 theTransArrayIndex = 0

I am using the BARE minimum configs possible to get the cluster up and running. There is nothing else.

I was also able to repeat this using the "suggested" configs from the mysql page:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-config-starting.html

How to repeat:
insert/update/delete data at a rate of 100,000 questions/min with auto-commit=1.

ndb_error_report output

Attachment: ndb_error_report_20121217091014.tar.bz2 (application/octet-stream, text), 24.12 KiB.

Hello Alexander,

I can not repeat described behavior on 7.2.9

Could you please provide repeatable testcase(table structure, steps to recreate etc) and "show global variables"?

Would it be possible for you to check if it's repeatable on the 7.2.8?

Looks like the reported issue is result of http://bugs.mysql.com/bug.php?id=67928

Yes! We did see this: http://bugs.mysql.com/bug.php?id=67928

Not only did we have table issues, but the data would be corrupt and the cluster would not restart after a shutdown.

Last night I went to download 7.1.X for testing and noticed that 7.2.9 is no longer available!!!! so I downloaded and installed the now latest 7.2.8.

Over night we inserted around 20m rows. This morning we shutdown and restarted without any issues. All the selects are working fine too.

Since 7.2.9 is no longer installed, I cannot do a "show global variables", but you should have my configs in the ndb_report that I attached. It was completely vanilla without anything but the bare minimum to get the cluster running.

Here is the schema of one of the tables that would corrupt:

CREATE TABLE aspects (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
value VARCHAR(255) NOT NULL,
hash CHAR(32) NOT NULL,
UNIQUE KEY (hash),
KEY (name(20))
) engine=ndb;

Another problem we saw was that we could:

select * from aspects limit 1;

and it would return a single row... but if we then did:

select * from aspects where id=xxxxx (the output from the LIMIT 1 query above)

it would say "0 rows found".

Hello Alexander,

I'm not able to repeat the issue at my end and as confirmed by you - this issue was specific to 7.2.9 and you didn't notice any issues and selects were also working fine with 7.2.8

>>Another problem we saw was that we could:
>>select * from aspects limit 1;
>>and it would return a single row... but if we then did:
>>select * from aspects where id=xxxxx (the output from the LIMIT 1 query above)
>>it would say "0 rows found".

This is related to below defect and is fixed in the next Cluster releases (7.1.26, 7.2.10).

http://bugs.mysql.com/bug.php?id=67928

Also, my observation on table

CREATE TABLE aspects (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
value VARCHAR(255) NOT NULL,
hash CHAR(32) NOT NULL,
UNIQUE KEY (hash),
KEY (name(20))
) engine=ndb;

Prefixes on indexes are not supported for NDBCLUSTER tables. If a prefix is used as part of an index specification in a statement such as CREATE TABLE, ALTER TABLE, or CREATE INDEX, the prefix is ignored... 

KEY (name(20)) is ignored full name is indexed

More details here -
http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-limitations-unsupported.html

Please test with 7.2.10, once it is available and please notify if you continue to get same issue please re-open this bug report. Thanks.

Hello Alexander,

Now MySQL Cluster 7.2.10 is GA - http://dev.mysql.com/downloads/cluster/

Could you please check if it is still repeatable?

Thanks,
Umesh

we just upgraded to 7.2.10 (it is still starting up as a matter of fact).

I'll report back once we have done tests.

I tried to replicate but again couldn't reproduce the reported issue on GA version. Duplicate of Bug #67928 Please reopen if needed