Bug #15115 TEXT Column only selectable singularly
Submitted: 21 Nov 2005 22:40 Modified: 29 Jul 2006 16:47
Reporter: Adam Dixon (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:Cluster 5.0.15 OS:Linux (RHEL4)
Assigned to: CPU Architecture:Any

[21 Nov 2005 22:40] Adam Dixon
Description:
Having a working cluster setup for some time now, I have some simple tables of which I cannot use mysql dump or even run a select * from table. I have nailed this down to involving the TEXT columns within the tables and if I run a query which selects all columns but the text, it works, including the text causes failure.
Config information;
8datanodes.
2sql api.

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=4900M
IndexMemory=225M
MaxNoOfConcurrentOperations=200000
MaxNoOfConcurrentTransactions=175000
MaxNoOfLocalOperations=175000 
MaxNoOfAttributes = 500000
MaxNoOfOrderedIndexes=500
MaxNoOfUniqueHashIndexes=500
##MaxNoOfFiredTriggers Default is 4000
MaxNoOfFiredTriggers=2000
MaxNoOfOpenFiles=60
MaxNoOfTables=350 

A simple example below, a table with only 5 rows, and not even excessivly large text data;
mysql> desc AdminMessage;
+-------------+------------+------+-----+---------------------+----------------+
| Field       | Type       | Null | Key | Default             | Extra          |
+-------------+------------+------+-----+---------------------+----------------+
| MessageID   | int(11)    | NO   | PRI | NULL                | auto_increment |
| Body        | text       | NO   |     |                     |                |
| Status      | tinyint(4) | NO   |     | 0                   |                |
| MessageDate | datetime   | NO   |     | 0000-00-00 00:00:00 |                |
+-------------+------------+------+-----+---------------------+----------------+
4 rows in set (0.01 sec)

Running a working query, which does not include the text field;
mysql> select MessageID, Status, MessageDate from AdminMessage;
+-----------+--------+---------------------+
| MessageID | Status | MessageDate         |
+-----------+--------+---------------------+
|         1 |      2 | 2000-12-01 12:20:23 |
|         2 |      2 | 2000-12-15 20:53:41 |
|         4 |      2 | 2002-07-23 17:21:33 |
|         3 |      2 | 2001-08-09 09:17:53 |
|         5 |      2 | 2002-10-30 15:36:06 |
+-----------+--------+---------------------+
5 rows in set (0.01 sec)

Now running the failing query including the text field in the select;

mysql> select MessageID, Body, Status, MessageDate from AdminMessage;
ERROR 1032 (HY000): Can't find record in 'AdminMessage'

mysql> show warnings;
+-------+------+-------------------------------------+
| Level | Code | Message                             |
+-------+------+-------------------------------------+
| Error | 1032 | Can't find record in 'AdminMessage' |
| Error | 1105 | Unknown error                       |
+-------+------+-------------------------------------+
2 rows in set (0.00 sec)

mysql> select Body from AdminMessage;
ERROR 1032 (HY000): Can't find record in 'AdminMessage'
mysql> select * from AdminMessage;
ERROR 1032 (HY000): Can't find record in 'AdminMessage'

select Body from AdminMessage where MessageID=4;

Works fine. I thought length might be an issue so on another copy of the data used length() to figure that out (seems fairly normal to me);

 length( Body )
163
998
903
381
1227

I have two databases on the cluster, one is live another (the larger of the two) is in testing. This does not seem to effect all tables which have involved text fields. But is effecting a number of them.

CREATE TABLE `AdminMessage` (
  `MessageID` int(11) NOT NULL auto_increment,
  `Body` text NOT NULL,
  `Status` tinyint(4) NOT NULL default '0',
  `MessageDate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`MessageID`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

How to repeat:
FYI I created a table with the same structure called AdminMessage111 and inserted the same data, and the data was selectable. So to reproduce this error I just dont know. This particular database has had this 'stagnant' copy of our database on the cluster for a couple of weeks and I just happen to notice it when a backup had an error using mysql dump on a table. I do beleieve it was once working, otherwise I would of noticed earlier back when I was testing.

However this is happening on a number of tables, and it only seems to effect the selectability of TEXT coloumns.
[24 Nov 2005 20:35] Hartmut Holzgraefe
Hi,

have you tried running ndb hotbackup?
"ndb_mgm> start backup"

Does that work?
Can you restore it to different cluster, and if so do you get same error?
[25 Nov 2005 8:39] Adam Dixon
I have a cluster backup running every 24hours at the moment, and no issues or errors arise in any log file.

However the cluster is 8x6gbRAM machine, with a 5200mb datamemory per host. 
How do you advise I test this? (Note that the current setup is a live production system)
[29 Nov 2005 1:17] Adam Dixon
I also found this in the mysql api err log file;
051122 10:02:57 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find record in 'Calendar'
051122 10:02:57 [ERROR] /usr/local/mysql/libexec/mysqld: Sort aborted
051122 10:03:03 [ERROR] /usr/local/mysql/libexec/mysqld: Can't find record in 'Calendar'
051122 10:03:03 [ERROR] /usr/local/mysql/libexec/mysqld: Sort aborted

Im going to setup a cluster on my machine and see If I can replicate the issue with the same tables involved etc.
[30 Nov 2005 7:53] Valeriy Kravchuk
Can it be so, that data from that original table with TEXT column was selected or updated form the other session at the same time? If yes, it explains why you can get them without problem from the other table - nobody else works with it.

Please, looks also at http://bugs.mysql.com/bug.php?id=15005 and http://bugs.mysql.com/bug.php?id=15282 bug reports. They looks very similar to your in a real reason for a problem.
[1 Dec 2005 0:27] Adam Dixon
These bugs may apply, But i do not believe so, This error is effecting all tables in my testing database (which at all times, has nothing to do with one another). This database is currently in testing, and has only had one user (me) run selects on it from a webinterface. While the in production database is not effected by this major issue.

This problem does not seem to be effecting tables in another database I am running on the cluster.

One of the tables is a notes tables, no updates are done, only inserts then selects. The other tables are mostly in the same state they were in when imported. We dont actually even use transactions in our databases.

The error I get mentions nothing of lock timeouts or such;
ERROR 1032 (HY000): Can't find record in 'AdminMessage'
Which shows up when doing a select * from AdminMessage which containst only 5 rows of 4 columns, and has never been updated or inserted into since being imported onto the cluster.
This same error applies to all tables which contain a text column in.

While at the same time the other databases tables with TEXT type column is not effected and Select * from table works.

FYI, dropping an effected table and creating it + dumping the same data back on, the select * statement works fine. I hope this can give you more clues as to what the issue is here.

Is this some kind of corruption of how mysql is internally storing the blob/textx data > 256bytes? or an Index issue?
[15 Dec 2005 5:51] Adam Dixon
Would running mysqldump accross the cluster twice a day potentially cause this?
[10 Jan 2006 14:02] Hartmut Holzgraefe
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.
[29 Jul 2006 3:10] Michael Caplan
I'm experiancing a similar, if not identical, issue.  I posted details in this thread:

http://forums.mysql.com/read.php?25,105434,105434
[29 Jul 2006 16:10] Valeriy Kravchuk
Michael,

What exact version of MySQL server did you use?
[29 Jul 2006 16:47] Hartmut Holzgraefe
Looks like bug 19956 to me, this bug was supposed to be fixed in MySQL 5.0.22 but as .22 was a security hotfix release only the fix for this bug is not yet available in any released MySQL 5.0 version.