Bug #25428 The Text column, not being indexed, although it is still stored in memory

Submitted: 5 Jan 2007 7:06 Modified: 5 Jan 2007 8:04
Reporter: Nilnandan Joshi Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S2 (Serious)
Version:MySQL 5.1.14 OS:Linux (RHEL 4)
Assigned to: CPU Architecture:Any

[5 Jan 2007 7:06] Nilnandan Joshi
Description:
I have created cluster environment on 4 pc
>> (THIS IS THE TESTING ENVIRONMENT)
>>
>> 1-SQL node (512 RAM)
>> 2-MGM node (512 RAM)
>> 3-data node-1 (2GB RAM)
>> 4-datanode-2  (2GB RAM)
>>
>> my config.ini is like this:
>>
>> [NDBD DEFAULT]
>> NoOfReplicas= 2
>> RedoBuffer=32M
>> TimeBetweenLocalCheckpoints=6
>> NoOfFragmentLogFiles=32
>> DataDir= /var/lib/mysql-cluster
>> DataMemory = 512M
>> IndexMemory = 64M
>> MaxNoOfConcurrentTransactions = 500
>> MaxNoOfConcurrentOperations = 250000
>>
>> [NDB_MGMD]
>> Id=1
>> HostName= 172.18.1.139
>>
>> [NDBD]
>> Id=3
>> HostName=172.18.1.140
>>
>> [NDBD]
>> Id=4
>> HostName=172.18.1.141
>>
>> [TCP DEFAULT]
>>
>> [TCP]
>> NodeId1=3
>> NodeId2=4
>> HostName1=172.18.1.140
>> HostName2=172.18.1.141
>>
>> [MYSQLD DEFAULT]
>>
>> [MYSQLD]
>> Id=2
>> HostName=172.18.1.138
>>
>> [MYSQLD]
>> Id=5
>>
>> For disk space storage i have created log group and tablespace like
>>
>> CREATE LOGFILE GROUP lg_1
>> ADD UNDOFILE 'undo_1.dat'
>> INITIAL_SIZE 32M
>> UNDO_BUFFER_SIZE 8M
>> ENGINE NDB;
>>
>> CREATE TABLESPACE ts_1
>> ADD DATAFILE 'data_1.dat'
>> USE LOGFILE GROUP lg_1
>> INITIAL_SIZE 200M
>> ENGINE NDB;
>>
>> ALTER TABLESPACE ts_1
>> ADD DATAFILE 'data_2.dat'
>> INITIAL_SIZE 200M
>> ENGINE NDB;
>>
>> NOW I have created 1 database on cluster named db_1. It has just 1 table
>> name temp_1 like
>>
>> CREATE TABLE temp_1
>> (id bigint(64) NOT NULL,
>> name varchar(32) NOT NULL,
>> address text NOT NULL,
>> location varchar(255) NOT NULL,
>> jtype varchar(100) NOT NULL,
>> married char(1) NOT NULL,
>> sex char(1) NOT NULL,
>> bdate timestamp NOT NULL,
>> id_2 bigint(64) NOT NULL)
>> TABLESPACE ts_1 STORAGE DISK
>> ENGINE=ndbcluster DEFAULT CHARSET=latin1 COMMENT='Job Keywords
>> Information';
>>
>> But now, I can just add 253,184 records in this table then after it gives
>> error like:
>>
>> Error: 'The table temp_1 is full';
>>
>> I have checked the NDB_DESC its output is:
>>
>> [root@rhel4mysql2 mysql-cluster]# ndb_desc -c localhost temp_1 -d db_1 –p
>> -- temp_1 --
>> Version: 33554435
>> Fragment type: 5
>> K Value: 6
>> Min load factor: 78
>> Max load factor: 80
>> Temporary table: no
>> Number of attributes: 10
>> Number of primary keys: 1
>> Length of frm data: 419
>> Row Checksum: 1
>> Row GCI: 1
>> TableStatus: Retrieved
>> -- Attributes --
>> id Bigint NOT NULL AT=FIXED ST=DISK
>> name Varchar(32;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> address Text(256,2000;16;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
>> location Varchar(255;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> jtype Varchar(100;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> married Char(1;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> sex Char(1;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> bdate Timestamp NOT NULL AT=FIXED ST=DISK
>> id_2 Bigint NOT NULL AT=FIXED ST=DISK
>> $PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
>>
>> -- Indexes --
>> PRIMARY KEY($PK) - UniqueHashIndex
>>
>> in this output we can see that text field is stored in memory

How to repeat:
whenever any on make table on cluster 5.1.14 and give text datatype in anyfield.
then after just check

ndb_desc -c localhost temp_1 -d db_1 –p

u got follwing output
[root@rhel4mysql2 mysql-cluster]# ndb_desc -c localhost temp_1 -d db_1 –p
>> -- temp_1 --
>> Version: 33554435
>> Fragment type: 5
>> K Value: 6
>> Min load factor: 78
>> Max load factor: 80
>> Temporary table: no
>> Number of attributes: 10
>> Number of primary keys: 1
>> Length of frm data: 419
>> Row Checksum: 1
>> Row GCI: 1
>> TableStatus: Retrieved
>> -- Attributes --
>> id Bigint NOT NULL AT=FIXED ST=DISK
>> name Varchar(32;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> address Text(256,2000;16;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
>> location Varchar(255;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> jtype Varchar(100;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> married Char(1;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> sex Char(1;latin1_swedish_ci) NOT NULL AT=FIXED ST=DISK
>> bdate Timestamp NOT NULL AT=FIXED ST=DISK
>> id_2 Bigint NOT NULL AT=FIXED ST=DISK
>> $PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
>>
>> -- Indexes --
>> PRIMARY KEY($PK) - UniqueHashIndex
[5 Jan 2007 8:04] Jonas Oreland
duplicate of http://bugs.mysql.com/bug.php?id=25001

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.