Bug #17927 Text Column not stored on disk
Submitted: 5 Mar 2006 10:14 Modified: 5 Mar 2006 10:34
Reporter: F Huber Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.1.7 OS:Linux (Redhat Linux ES 4)
Assigned to: Jonas Oreland CPU Architecture:Any

[5 Mar 2006 10:14] F Huber
Description:
I created a table in the Cluster stored on disk (I created a Tablespace before)

After that I filled it without any problems (a few million records). But I am wondering why the column "comment" is still in memory and not on the disk (since its not part of an index)? 

-- suggestions --
Version: 1
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 8
Number of primary keys: 1
Length of frm data: 395
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved
-- Attributes --
id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
comment Text(256,2000;16;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
datetime Datetime NOT NULL AT=FIXED ST=DISK
user Text(256,2000;16;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
ip Varchar(16;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
userid Int NOT NULL AT=FIXED ST=DISK
refid Int NOT NULL AT=FIXED ST=MEMORY
channel Int NOT NULL AT=FIXED ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
refid(refid) - OrderedIndex
idchannel$unique(id, channel) - UniqueHashIndex
idchannel(id, channel) - OrderedIndex
PRIMARY(id) - OrderedIndex

NDBT_ProgramExit: 0 - OK

mysql> select file_id, file_name , tablespace_name , engine, free_extents, total_extents, data_free, status from information_schema.files;
+---------+--------------+-----------------+------------+--------------+---------------+-----------+--------+
| file_id | file_name    | tablespace_name | engine     | free_extents | total_extents | data_free | status |
+---------+--------------+-----------------+------------+--------------+---------------+-----------+--------+
|       0 | datafile.dat | ts1             | ndbcluster |         2893 |         20000 |      NULL | NORMAL |
|       0 | undofile.dat | ts1             | ndbcluster |     33505296 |       8388608 |      NULL | NORMAL |
+---------+--------------+-----------------+------------+--------------+---------------+-----------+--------+
2 rows in set (0.00 sec)

mysql>

Additional question: 

-are varchar fields in 5.1 still handeld as fixed length CHAR fields if they are not stored on disk?

How to repeat:

CREATE TABLE `suggestions` (
`id` int(11) NOT NULL auto_increment,
`comment` text NOT NULL,
`datetime` datetime NOT NULL default '0000-00-00 00:00:00',
`user` text NOT NULL,
`ip` varchar(16) NOT NULL default '',
`userid` int(11) NOT NULL default '0',
`refid` int(11) NOT NULL default '0',
`channel` int(11) NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `idchannel` (`id`,`channel`),
KEY `refid` (`refid`)
)
TABLESPACE ts1 STORAGE DISK
ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
[5 Mar 2006 10:34] Jonas Oreland
Hi

Text/blob columns are stored in a separate table.
It's only the "header" which is stored in main memory.

If you do "ndb_show_tables" you will find an "extra" table, and if you do ndb_desc on that
  you will find that it's stored on disk.

Additional question: 
-are varchar fields in 5.1 still handeld as fixed length CHAR fields if they are
not stored on disk?

Varchar is stored using variable length, if not on disk
            is stored using fixed length if stored on disk