Bug #25001 MEDIUMTEXT column not stored on disk
Submitted: 12 Dec 2006 11:01 Modified: 4 Mar 2007 9:44
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S1 (Critical)
Version:5.1.14 OS:Linux (Redhat AS 4)
Assigned to: Jonas Oreland CPU Architecture:Any
Tags: cluster, disk data, Mediumtext

[12 Dec 2006 11:01] [ name withheld ]
Description:
A MEDIUMTEXT column of a table that is stored in a TABLESPACE on disk using the NDB ENGINE, is stored in MEMORY.

ndb_desc reports the DATA column of the NDB$BLOB... table that is associated with the MEDIUMTEXT column of the table that is stored in a TABLESPACE on disk, to be stored in MEMORY.

ndb_desc reports a VARCHAR column in the same table to be stored on DISK

In my opinion this is a bug: TEXT (and BLOB) columns of a table that is stored in a TABLESPACE on disk using the NDB ENGINE should also be stored on disk.

How to repeat:
1) Create LOGFILE GROUP, TABLESPACE and TABLE in the cluster:

CREATE LOGFILE GROUP lg_test
    ADD UNDOFILE 'undo_test.dat'
    INITIAL_SIZE 16M
    UNDO_BUFFER_SIZE 2M
    ENGINE NDB;

CREATE TABLESPACE ts_test
    ADD DATAFILE 'data_test.dat'
    USE LOGFILE GROUP lg_test
    INITIAL_SIZE 32M
    ENGINE NDB;

CREATE TABLE test (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data MEDIUMTEXT,
    other_data VARCHAR(255)
    )
    TABLESPACE ts_test STORAGE DISK
    ENGINE NDB;

2) Use ndb_desc to describe the table test:

-- testtext --
Version: 3
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 3
Number of primary keys: 1
Length of frm data: 287
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved
-- Attributes --
id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
data Text(256,4000;8;latin1_swedish_ci) NULL AT=FIXED ST=MEMORY
other_data Varchar(255;latin1_swedish_ci) NULL AT=FIXED ST=DISK

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

3) use ndb_show_tables to find the NDB$BLOB... table that is associated with the MEDIUMTEXT column, and use ndb_desc to describe that table:

-- NDB$BLOB_13_1 --
Version: 3
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 4
Number of primary keys: 3
Length of frm data: 0
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved
-- Attributes --
PK Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
DIST Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
PART Unsigned PRIMARY KEY AT=FIXED ST=MEMORY
DATA Char(4000;binary) NOT NULL AT=FIXED ST=MEMORY

-- Indexes --
PRIMARY KEY(PK, DIST, PART) - UniqueHashIndex

Suggested fix:
Probably the "Store_on_Disk" flag gets lost when creating the (hidden) NDB$BLOB... table for the MEDIUMTEXT column.
[12 Dec 2006 12:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16826

ChangeSet@1.2354, 2006-12-12 13:09:20+01:00, jonas@perch.ndb.mysql.com +2 -0
  ndb - bug#25001
    make sure DISK flag is set on DATA column for blob table
[27 Dec 2006 18:16] Tomas Ulin
pushed to 5.1.15
[28 Dec 2006 14:10] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.15 changelog.
[1 Mar 2007 21:35] Nathan Babb
I'm still seeing this behavior on 5.1.16.
[2 Mar 2007 19:17] Jonas Oreland
hi

i get behaviour as described below...
and this is as I intended...
i.e blob head is in memory and blob "part" is on disk...

what do you think?

/Jonas

jonas@perch:~/run/cluster/ndb_api.1> ndb_desc -d test t1
-- t1 --
Version: 2
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 250
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved
-- Attributes -- 
a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Text(256,4000;8;latin1_swedish_ci) NULL AT=FIXED ST=MEMORY

-- Indexes -- 
PRIMARY KEY(a) - UniqueHashIndex
PRIMARY(a) - OrderedIndex

NDBT_ProgramExit: 0 - OK

jonas@perch:~/run/cluster/ndb_api.1> ndb_desc -d test 'NDB$BLOB_9_1'
-- NDB$BLOB_9_1 --
Version: 2
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 4
Number of primary keys: 3
Length of frm data: 0
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved
-- Attributes -- 
PK Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
DIST Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
PART Unsigned PRIMARY KEY AT=FIXED ST=MEMORY
DATA Char(4000;binary) NOT NULL AT=FIXED ST=DISK

-- Indexes -- 
PRIMARY KEY(PK, DIST, PART) - UniqueHashIndex

NDBT_ProgramExit: 0 - OK
[2 Mar 2007 19:35] Nathan Babb
Sorry, I wasn't aware of the hidden table.  After checking ndb_show_tables and ndb_desc I see that it is on DISK after all.  Am I correct in believing it's the first 256 bytes that are stored in memory and the remainder is in the hidden table?
[2 Mar 2007 21:30] Jonas Oreland
yes, that is correct...

i'll reclose this, ok?
[2 Mar 2007 21:32] Nathan Babb
Yes, thanks.  Sorry for the trouble.
[3 Mar 2007 15:26] Tomas Ulin
Jon,

setting to documenting just to make sure this behaviour gets documented...  i.e. head (includeing first 256 bytes) in memory and rest on disk, in the "hidden" table

T
[4 Mar 2007 9:44] Jon Stephens
Added note to Disk Data section of Cluster chapter.