Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space ML1 and version 9.2

Table of Contents
maxLevel1

All DDL is performed online. See also Online Schema Changes

Anchor
DDL
DDL
Supported DDL

ClustrixDB supports standard MySQL DDL to CREATE, ALTER, and DROP objects, always with online schema changes. See below list of caveats. 

ClustrixDB-specific DDL

ClustrixDB also supports the following ClustrixDB-specific DDL:

Executable Comments

ClustrixDB executes comments using the format:

/*$ clustrix-specific comment */ 

These comments are ignored by other databases. ClustrixDB does not execute any other format of comments. 

Unsupported DDL

ClustrixDB does not support:

  • ALTER TABLE...CONVERT
  • CREATE SEQUENCE
  • CREATE VIEW ... WITH CHECK OPTION 

Caveats to DDL Support

CREATE

  • ClustrixDB accepts the syntax for CREATE TABLE with CHECK CONSTRAINT, but no constraint checking is performed. 
  • ALTER TABLE does not support the CHECK CONSTRAINT syntax. 
  • ClustrixDB accepts the syntax for ROW_FORMAT=COMPRESSED but does not support compression. 
  • ClustrixDB allows longer unique keys than MySQL.
  • ClustrixDB allows specifying the position of a default ENUM value, e.g. 
    CREATE TABLE CountryLanguage (id INT, IsOfficial enum('T','F') NOT NULL default 1);

    results in a default value of 'T'. 

  • ClustrixDB allows specifying a default value for ENUM that is not in the list of possible values, e.g.  CREATE TABLE Sunny (Pig ENUM('little', 'big') DEFAULT 'pink');

  • ClustrixDB does not trim trailing whitespaces in ENUM values. 

  • ClustrixDB does not raise an error if a NOT NULL column specifies NULL as the default value but treats the columns as NOT NULL. 

  • Clustrix does not recommend using the FLOAT data type for primary keys. 

  • ClustrixDB does not error if the length for DECIMAL exceeds the maximum precision and will silently truncate, even if STRICT_TRANS_TABLES is enabled. 
  • CREATE TABLE does not raise an error when the default value for an INT type field is set to a quoted string. However, inserts with the default value will be cast to 0
  • The % wild card is not supported in database names.

SHOW 

  • ClustrixDB does not support WHERE clauses as part of SHOW COLUMNS or SHOW FIELDS 
  • After an unconstrained DELETE, the auto_increment value for a table will appear to be incorrect when viewed via SHOW CREATE TABLE. The value will be properly reset once the next auto_increment value is used. 

Caveats for Views

  • If you create a view using SELECT *, the view definition will change if the underlying table definition changes. 

  • ClustrixDB will accept the syntax for the ALGORITHM option for views, but does not apply any algorithms. 
  • When using DESCRIBE on a view, if the underlying column has a default value of 0, ClustrixDB will display NULL instead. 

DROP

  • DROP TABLE will not wait for other sessions to commit transactions before proceeding with dropping the table. 
  • ClustrixDB does not support dropping primary keys via DROP INDEX `PRIMARY`
  • To drop a primary key, specify LOCK=SHARED as part of the ALTER statement:

    sql> ALTER TABLE T1 DROP PRIMARY KEY, LOCK=SHARED; 

Other caveats for DDL

  • % wild cards are not supported in database names.

 

See also:

Children Display
depth1