sql_alter_table_tokudb
NAME
sql_alter_table_tokudb: Generate ALTER TABLE SQL statements for converting tables to TokuDBTYPE
ViewDESCRIPTION
sql_alter_table_tokudb provides with SQL statements to alter a table to TokuDB in two compression formats, and overcoming issue of KEY_BLOCK_SIZE
There is a known issue with converting COMPRESSED InnoDB tables to TokuDB (<= 7.0.4 at this time).
When the KEY_BLOCK_SIZE create option is specified, TokuDB wrongly applies it
to all indexes, which makes for file size bloating instead of compression.
The only solution known to the author of this tool is to DROP all keys, reset the KEY_BLOCK_SIZE to 0 and add all keys again. Fortunately, this can be done in one (long) ALTER statement.
This view provides such statements.
STRUCTURE
mysql> DESC sql_alter_table_tokudb; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | alter_fast_clause | mediumtext | YES | | NULL | | | sql_alter_fast | mediumtext | YES | | NULL | | | alter_small_clause | mediumtext | YES | | NULL | | | sql_alter_small | mediumtext | YES | | NULL | | +--------------------+-------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- TABLE_SCHEMA: schema of current table
- TABLE_NAME: current table name
- ENGINE: current engine name
- alter_fast_clause: The clause for ALTER TABLE to convert current table to TokuDB in TOKUDB_FAST (lightweight compression) row format.
- sql_alter_fast: A complete ALTER TABLE statement to convert current table to TokuDB in TOKUDB_FAST (lightweight compression) row format.
- alter_small_clause: The clause for ALTER TABLE to convert current table to TokuDB in TOKUDB_SMALL (aggressive compression) row format.
- sql_alter_small: A complete ALTER TABLE statement to convert current table to TokuDB in TOKUDB_SMALL (aggressive compression) row format.
The SQL statements are not terminated by ';'.
EXAMPLES
Generate ALTER TABLE statements for a test table:
mysql> create table test.t (
id int,
c char,
dt datetime,
d double,
PRIMARY KEY (id),
KEY (dt, c)
);
mysql> select * from common_schema.sql_alter_table_tokudb where table_schema='test' and table_name='t' \G
TABLE_SCHEMA: test
TABLE_NAME: t
ENGINE: InnoDB
alter_fast_clause: DROP KEY `dt`, DROP PRIMARY KEY, ADD KEY `dt`(`dt`,`c`), ADD PRIMARY KEY (`id`), engine=tokudb row_format=tokudb_small key_block_size=0;
sql_alter_fast: alter table `test`.`t` DROP KEY `dt`, DROP PRIMARY KEY, ADD KEY `dt`(`dt`,`c`), ADD PRIMARY KEY (`id`), engine=tokudb row_format=tokudb_small key_block_size=0;
alter_small_clause: DROP KEY `dt`, DROP PRIMARY KEY, ADD KEY `dt`(`dt`,`c`), ADD PRIMARY KEY (`id`), engine=tokudb row_format=tokudb_small key_block_size=0;
sql_alter_small: alter table `test`.`t` DROP KEY `dt`, DROP PRIMARY KEY, ADD KEY `dt`(`dt`,`c`), ADD PRIMARY KEY (`id`), engine=tokudb row_format=tokudb_small key_block_size=0;