Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 28.7Mb
PDF (A4) - 28.7Mb
Man Pages (TGZ) - 189.1Kb
Man Pages (Zip) - 302.2Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Setting NDB Comment Options

13.1.17.7 Setting NDB Comment Options

In NDB Cluster, a column comment in a CREATE TABLE or ALTER TABLE statement can also be used to specify an NDB_COLUMN option. NDB 7.3 and 7.4 support a single column comment option MAX_BLOB_PART_SIZE; syntax for this option is shown here:

COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE[={0|1}]'

The = sign and the value following it are optional. Using any value other than 0 or 1 results in a syntax error.

The effect of this option is to set the blob part size of a TEXT or BLOB column to the maximum number of bytes supported for this by NDB (13948). This option can be applied to any blob column type supported by MySQL except TINYBLOB or TINYTEXT (BLOB, MEDIUMBLOB, LONGBLOB, TEXT, MEDIUMTEXT, or LONGTEXT).

To see the effects of this option, we first run the following SQL statement in the mysql client to create a table with two BLOB columns, one (c1) with no extra options, and another (c2) with MAX_BLOB_PART_SIZE:

mysql> CREATE TABLE test.t (
    ->   p INT PRIMARY KEY, 
    ->   c1 BLOB, 
    ->   c2 BLOB COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE'
    -> ) ENGINE NDB;
Query OK, 0 rows affected (0.32 sec)

From the system shell, run the ndb_desc utility to obtain information about the table just created, as shown in this example:

$> ndb_desc -d test t
-- t --
Version: 1
Fragment type: HashMapPartition
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: 324
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
HashMap: DEFAULT-HASHMAP-3840-2
-- Attributes --
p Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
c1 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_22_1
c2 Blob(256,13948,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_22_2
-- Indexes -- 
PRIMARY KEY(p) - UniqueHashIndex
PRIMARY(p) - OrderedIndex

Column information in the output is listed under Attributes; for columns c1 and c2 it is displayed here in emphasized text. For c1, the blob part size is 2000, the default value; for c2, it is 13948, as set by MAX_BLOB_PART_SIZE.

You can change the blob part size for a given blob column of an NDB table using an ALTER TABLE statement such as this one, and verifying the changes afterwards using SHOW CREATE TABLE:

mysql> ALTER TABLE test.t 
    ->    DROP COLUMN c1, 
    ->     ADD COLUMN c1 BLOB COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE',
    ->     CHANGE COLUMN c2 c2 BLOB AFTER c1;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test.t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `p` int(11) NOT NULL,
  `c1` blob COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE',
  `c2` blob,
  PRIMARY KEY (`p`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> EXIT
Bye

The output of ndb_desc shows that the blob part sizes of the columns have been changed as expected:

$> ndb_desc -d test t
-- t --
Version: 16777220
Fragment type: HashMapPartition
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: 324
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
HashMap: DEFAULT-HASHMAP-3840-2
-- Attributes --
p Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
c1 Blob(256,13948,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_26_1
c2 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_26_2
-- Indexes -- 
PRIMARY KEY(p) - UniqueHashIndex
PRIMARY(p) - OrderedIndex

NDBT_ProgramExit: 0 - OK

Changing a column's blob part size must be done using a copying ALTER TABLE; this operation cannot be performed online (see Section 18.6.12, “Online Operations with ALTER TABLE in NDB Cluster”).

For more information about how NDB stores columns of blob types, see String Type Storage Requirements.