Comments - TokuDB Differences

Documentation CLUSTERED keyword is not correct

> CREATE TABLE foo (id int, key (id) CLUSTERED=YES) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
ERROR 1911 (HY000): Unknown option 'CLUSTERED'

should be

> CREATE TABLE foo (id int, key (id) CLUSTERING=YES) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
Query OK, 0 rows affected (0.01 sec)

> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11) DEFAULT NULL,
  KEY `id` (`id`) `CLUSTERING`=YES
) ENGINE=TokuDB DEFAULT CHARSET=utf8 `COMPRESSION`=TOKUDB_LZMA
1 row in set (0.00 sec)

It is not possible to create PARTITIONED table with CLUSTERING key in one go

> CREATE TABLE bar (id int, key (id) CLUSTERING=YES) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA PARTITION BY HASH(id) PARTITIONS 4;
ERROR 1911 (HY000): Unknown option 'CLUSTERING

but adding partitioning later works fine

> CREATE TABLE bar (id int, key (id) CLUSTERING=YES) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
Query OK, 0 rows affected (0.02 sec)

> ALTER TABLE bar PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

> SHOW CREATE TABLE bar\G
*************************** 1. row ***************************
       Table: bar
Create Table: CREATE TABLE `bar` (
  `id` int(11) DEFAULT NULL,
  KEY `id` (`id`) `CLUSTERING`=YES
) ENGINE=TokuDB DEFAULT CHARSET=utf8 `COMPRESSION`=TOKUDB_LZMA
/*!50100 PARTITION BY HASH (id)
PARTITIONS 4 */
1 row in set (0.00 sec)

If you want to add to the table and index partitioning, clustering, it is only in the following order:

create table,

1. alter add .... clustering = yes

2. and alter table ... partition to ...

reverse order :

1. alter table ... partition to ...

2. alter add .... clustering = yes

not WORK, rror 1911 (HY000): Unknown option 'clustering'.

EXAMPLE: drop table t1

CREATE TABLE `t1` ( `id_high` bigint(20) unsigned NOT NULL, `id_low` bigint(20) unsigned NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -- KEY `clstr_time` (`modified`) `clustering` = yes, -- KEY `clstr_key_id_id` (`id_high`) `clustering` = yes, -- PRIMARY KEY (`modified`,`id_high`) ) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

alter table n3.t1 add PRIMARY KEY `t1_pk` (`modified` ASC, `id_high` ASC); alter table t1 add key clstr_key (`id_high`) CLUSTERING=YES;

ALTER TABLE t1 PARTITION BY RANGE (UNIX_TIMESTAMP(modified)) ( PARTITION p201410 VALUES LESS THAN (1414796400) ENGINE = TokuDB, PARTITION p201411 VALUES LESS THAN (1417388400) ENGINE = TokuDB, PARTITION p201412 VALUES LESS THAN (1420066800) ENGINE = TokuDB, PARTITION p201501 VALUES LESS THAN (1422745200) ENGINE = TokuDB, PARTITION p201502 VALUES LESS THAN (1425164400) ENGINE = TokuDB, PARTITION p201503 VALUES LESS THAN (1427839200) ENGINE = TokuDB, PARTITION p201504 VALUES LESS THAN (1430431200) ENGINE = TokuDB, PARTITION p201505 VALUES LESS THAN (1433109600) ENGINE = TokuDB, PARTITION p201506 VALUES LESS THAN (1435701600) ENGINE = TokuDB, PARTITION p201507 VALUES LESS THAN (1438380000) ENGINE = TokuDB, PARTITION p201508 VALUES LESS THAN (1441058400) ENGINE = TokuDB, PARTITION p201509 VALUES LESS THAN (1443650400) ENGINE = TokuDB, PARTITION p2015010 VALUES LESS THAN (1446332400) ENGINE = TokuDB, PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = TokuDB );

I've fixed the CLUSTERING syntax, thanks. As far as partitioning is concerned, I've reported a bug about it: https://mariadb.atlassian.net/browse/MDEV-5058