Bug #25346 Disk-based information not available in INFORMATION_SCHEMA

• Submitted: 1 Jan 2007 12:26
• Modified: 14 Jun 2007 12:58
• Reporter: Geert Vanderkelen
• Status: Not a Bug
• Category: MySQL Cluster: Disk Data
• Severity: S3 (Non-critical)
• Version: 5.1.14
• OS: Linux (Linux)
• CPU Architecture: Any
• Tags: cluster, disk-based, information_schema

[1 Jan 2007 12:26] Geert Vanderkelen

Description:
Currently it is not possible to see whether a table is a Cluster table created with the option STORAGE DISK. The INFORMATION_SCHEMA.TABLES table doesn't have CREATE_OPTIONS filed in. It would be good to have a foreign key to the INFORMATION_SCHEMA.FILES table though.

The create options are correctly shown using a SHOW CREATE TABLE though.

How to repeat:
Using the example log-group, table-space and table from the manual:
 http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data.html

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='dt_1'\G                                                               *************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: test
     TABLE_NAME: dt_1
     TABLE_TYPE: BASE TABLE
         ENGINE: NDBCLUSTER
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: number_of_replicas: 2
1 row in set (0.00 sec)

Doing this using the ndb_desc utility we get:

$ ./bin/ndb_desc -c localhost:1406 dt_1 -d test
..
-- Attributes -- 
member_id Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
last_name Varchar(50;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
first_name Varchar(50;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
dob Date NOT NULL AT=FIXED ST=DISK
joined Date NOT NULL AT=FIXED ST=DISK

-- Indexes -- 
PRIMARY KEY(member_id) - UniqueHashIndex
last_name(last_name, first_name) - OrderedIndex
PRIMARY(member_id) - OrderedIndex

Suggested fix:
Include the table options in the INFORMATION_INFO.TABLES.CREATE_OPTIONS field.
Much better: add relation between the TABLES and FILES table.

[1 Jan 2007 12:31] Geert Vanderkelen

Verified using 5.1.14 and from BK.

[26 Mar 2007 12:59] Stewart Smith

How is the information in INFORMATION_SCHEMA.PARTITIONS not adequate? Since tablespace is really going to be per partition, not table, this seems like the right place to store the information.

[14 Jun 2007 12:58] Stewart Smith

information is available (see previous comment). no further response since March... Not A Bug.