WL#3749: MySQL Cluster: On-line DDL, new handler interface
Affects: Server-6.0
—
Status: Complete
In WL#3348 Online DDL, add/drop column, several steps are defined for performing an on-line ALTER TABLE. In step 9: 9) Build anything (build index, build unique key, reorganise partitions,,,) there is need for a new interface to the storage engines to perform the actual alter table operation(s). Also WL#3348 only discusses on-line ALTER TABLE, but there will always be a need for the old copying alter table, and storage engines need to be able to declare if they will be able to support an ALTER TABLE on-line, or not. This WL defines what needs to be implemented to extend the current handler interface.
Current implementation in mysql_alter_table for determining if an on-line
-------------------------------------------------------------------------
operation should be done
------------------------
The current implementation (5.1) only provides support for on-line
add/drop index and uses four new handler methods, check_if_incompatible_data,
add_index, prepare_drop_index, and final_drop_index. This limited interface
must be changed to support a general on-line ALTER TABLE interface.
1. Checking if a storage engine supports an ALTER TABLE
The method compare_tables does most of the checking if an ALTER TABLE
can be done on-line, including calling the handler::check_if_incompatible_data
method:
/*
SYNOPSIS
compare_tables()
table The original table.
create_list The fields for the new table.
key_info_buffer An array of KEY structs for the new indexes.
key_count The number of elements in the array.
create_info Create options for the new table.
alter_info Alter options.
order_num Number of order list elements.
index_drop_buffer OUT An array of offsets into table->key_info.
index_drop_count OUT The number of elements in the array.
index_add_buffer OUT An array of offsets into key_info_buffer.
index_add_count OUT The number of elements in the array.
varchar Table contains varchar (undocumented !!)
DESCRIPTION
'table' (first argument) contains information of the original
table, which includes all corresponding parts that the new
table has in arguments create_list, key_list and create_info.
By comparing the changes between the original and new table
we can determine how much it has changed after ALTER TABLE
and whether we need to make a copy of the table, or just change
the .frm file.
If there are no data changes, but index changes, 'index_drop_buffer'
and/or 'index_add_buffer' are populated with offsets into
table->key_info or key_info_buffer respectively for the indexes
that need to be dropped and/or (re-)created.
RETURN VALUES
0 No copy needed
ALTER_TABLE_DATA_CHANGED Data changes, copy needed
ALTER_TABLE_INDEX_CHANGED Index changes, copy might be needed
*/
static uint compare_tables(TABLE *table, List *create_list,
KEY *key_info_buffer, uint key_count,
HA_CREATE_INFO *create_info,
ALTER_INFO *alter_info, uint order_num,
uint *index_drop_buffer, uint *index_drop_count,
uint *index_add_buffer, uint *index_add_count,
bool varchar)
bool handler::check_if_incompatible_data(HA_CREATE_INFO *create_info,
uint table_changes)
where table_changes is IS_EQUAL_NO(0), IS_EQUAL_YES(1), or
IS_EQUAL_PACK_LENGTH(2) reflects any changes to the table fields.
Returns one of
#define COMPATIBLE_DATA_YES 0
#define COMPATIBLE_DATA_NO 1
This call is not sufficient for a storage engine to analyze any ALTER
TABLE to determine if it is supported or not.
The current implementation is based on that handlers specify "capability"
flags in handlerton::alter_table_flags. These currently only define
add/drop indexes.
Re-factoring of mysql_alter_table
---------------------------------
The general structure of compare tables can be re-used, but with only
creating a parameters needed to send to the handler on a following
call. Change compare_tables to:
SYNOPSIS
compare_tables()
table The original table.
create_list The fields for the new table.
key_info_buffer An array of KEY structs for the new indexes.
key_count The number of elements in the array.
create_info Create options for the new table.
alter_info Alter options.
order_num Number of order list elements.
alter_flags OUT Indicate what has changed
alter_table_info OUT Data structures useful for online alter table
static uint compare_tables(TABLE *table, List *create_list,
KEY *key_info_buffer, uint key_count,
HA_CREATE_INFO *old_create_info,
HA_CREATE_INFO *new_create_info.
ALTER_INFO *alter_info, uint order_num,
HA_ALTER_FLAGS *alter_flags,
HA_ALTER_INFO *alter_table_info);
Call new method after:
New method
int handler::check_if_supported_alter(TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_FLAGS alter_flags,
HA_ALTER_INFO *alter_table_info);
(with default implementation calling handler::check_if_incompatible_data with
create_info, table_changes and returning HA_ALTER_SUPPORTED_WAIT_LOCK if
COMPATIBLE_DATA_YES or HA_ALTER_NOT_SUPPORTED if COMPATIBLE_DATA_NO).
handler::check_if_incompatible_data will be marked as depricated
HA_ALTER_FLAGS is defined by:
#define HA_ADD_INDEX (1L << 0)
#define HA_DROP_INDEX (1L << 1)
#define HA_ALTER_INDEX (1L << 2)
#define HA_RENAME_INDEX (1L << 3)
#define HA_ADD_UNIQUE_INDEX (1L << 4)
#define HA_DROP_UNIQUE_INDEX (1L << 5)
#define HA_ALTER_UNIQUE_INDEX (1L << 6)
#define HA_RENAME_UNIQUE_INDEX (1L << 7)
#define HA_ADD_PK_INDEX (1L << 8)
#define HA_DROP_PK_INDEX (1L << 9)
#define HA_ALTER_PK_INDEX (1L << 10)
#define HA_ADD_COLUMN (1L << 11)
#define HA_DROP_COLUMN (1L << 12)
#define HA_ALTER_COLUMN_NAME (1L << 13)
#define HA_ALTER_COLUMN_TYPE (1L << 14)
#define HA_ALTER_COLUMN_ORDER (1L << 15)
#define HA_ALTER_COLUMN_DEFAULT_VALUE (1L << 16)
#define HA_ADD_FOREIGN_KEY (1L << 17)
#define HA_DROP_FOREIGN_KEY (1L << 18)
#define HA_ALTER_FOREIGN_KEY (1L << 19)
#define HA_ADD_CONSTRAINT (1L << 20)
#define HA_ADD_PARTITION (1L << 21)
#define HA_DROP_PARTITION (1L << 22)
#define HA_COALESCE_PARTITION (1L << 23)
#define HA_REORGANIZE_PARTITION (1L << 24)
#define HA_CHANGE_CHARACTER_SET (1L << 25)
#define HA_SET_DEFAULT_CHARACTER_SET (1L << 26)
#define HA_CHANGE_AUTOINCREMENT_VALUE (1L << 27)
#define HA_RENAME_TABLE (1L << 28)
HA_ALTER_INFO is defined as
typedef struct st_ha_alter_information
{
KEY *key_info_buffer;
uint key_count;
uint index_drop_count;
uint *index_drop_buffer;
uint index_add_count;
uint *index_add_buffer;
} HA_ALTER_INFO;
where more data structures can be added for different online operations,
currently only add/drop index and add attribute has been studied.
handler::check_if_supported_alter returns one of
#define HA_ALTER_ERROR -1
#define HA_ALTER_SUPPORTED_WAIT_LOCK 0
#define HA_ALTER_SUPPORTED_NO_LOCK 1
#define HA_ALTER_NOT_SUPPORTED 2
The handlerton::alter_table_flags will not be needed anymore, since
handlers will declare their capabilities dynamically through the
handler::check_if_supported_alter() call. Instead a table_flag,
HA_ONLINE_ALTER will be added, that specifies if a handler supports
any kind of on-line alter table at all.
The calculation of offsets to keys added and dropped should be moved out
of compare_tables and into an utility function that could be called from
prepare_alter_table (defined below), since these are actually only needed
if operation is to be done on-line. Note also the a table object representing
the new table definition will here be created earlier.
Current Handler Interface for Online CREATE/DROP INDEX
------------------------------------------------------
int handler::add_index(TABLE *table_arg, KEY *key_info, uint num_of_keys)
int handler::prepare_drop_index(TABLE *table_arg, uint *key_num,
uint num_of_keys)
int handler::final_drop_index(TABLE *table_arg)
These methods are only handling one of many possible on-line operations,
and adding methods for all of them will make the handler interface huge.
Adding general alter methods is needed.
New Handler Interface
---------------------
Two new methods are needed (will replace the above add/drop index methods)
that will support all possible on-line ALTER TABLE operations.
int handler::alter_table_phase1(TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_FLAGS alter_flags,
HA_ALTER_INFO *alter_table_info)
Prepares for the alter table, such as re-numbering indexes at drop index,
adds indexes and new attributes (if added last). Operations should not be
visible to applications using the old table definition, hence can done without
locking and can easily be rolled back.
int handler::alter_table_phase2(TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_FLAGS alter_flags,
HA_ALTER_INFO *alter_table_info)
Performs the structural changes from alter table command that affect
ongoing transactions using this table, such as dropping indexes and
dropping and reordering (not adding new attributes last) attributes.
Requires locking the table (if check_if_supported_alter returned
HA_ALTER_SUPPORTED_WAIT_LOCK).
Error handling:
These methods should not fail, if they still do something unexpected must have
happened and the complete ALTER TABLE should fail.
Note that these will be called regardless if there need to be any change done
by the handler, not all changes need to be two phase and most handlers don't
need to do anything during column rename or change to compatible type.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.