Hi!
The new on-line alter table interface is now in mysql-5.2 source tree,
see below.
Note also that there is a syntax extension to ALTER TABLE:
ALTER [ONLINE | OFFLINE] TABLE ...
where ALTER ONLINE will fail if the storage engine doesn't support this
operation on-line, and ALTER OFFLINE will always copy the table.
BR
-- Martin
/*
On-line ALTER TABLE interface
*/
#define HA_MAX_ALTER_FLAGS 39
typedef Bitmap<HA_MAX_ALTER_FLAGS> HA_ALTER_FLAGS;
#define HA_ADD_INDEX (0)
#define HA_DROP_INDEX (1)
#define HA_ALTER_INDEX (2)
#define HA_RENAME_INDEX (3)
#define HA_ADD_UNIQUE_INDEX (4)
#define HA_DROP_UNIQUE_INDEX (5)
#define HA_ALTER_UNIQUE_INDEX (6)
#define HA_RENAME_UNIQUE_INDEX (7)
#define HA_ADD_PK_INDEX (8)
#define HA_DROP_PK_INDEX (9)
#define HA_ALTER_PK_INDEX (10)
#define HA_ADD_COLUMN (11)
#define HA_DROP_COLUMN (12)
#define HA_CHANGE_COLUMN (13)
#define HA_ALTER_COLUMN_NAME (14)
#define HA_ALTER_COLUMN_TYPE (15)
#define HA_ALTER_COLUMN_ORDER (16)
#define HA_ALTER_COLUMN_NULLABLE (17)
#define HA_COLUMN_DEFAULT_VALUE (18)
#define HA_COLUMN_STORAGE (19)
#define HA_COLUMN_FORMAT (20)
#define HA_ADD_FOREIGN_KEY (21)
#define HA_DROP_FOREIGN_KEY (22)
#define HA_ALTER_FOREIGN_KEY (23)
#define HA_ADD_CONSTRAINT (24)
#define HA_ADD_PARTITION (25)
#define HA_DROP_PARTITION (26)
#define HA_ALTER_PARTITION (27)
#define HA_COALESCE_PARTITION (28)
#define HA_REORGANIZE_PARTITION (29)
#define HA_CHANGE_CHARACTER_SET (30)
#define HA_SET_DEFAULT_CHARACTER_SET (31)
#define HA_CHANGE_AUTOINCREMENT_VALUE (32)
#define HA_ALTER_STORAGE (33)
#define HA_ALTER_TABLESPACE (34)
#define HA_ALTER_ROW_FORMAT (35)
#define HA_RENAME_TABLE (36)
#define HA_ALTER_STORAGE_ENGINE (37)
#define HA_RECREATE (38)
/* Remember to increase HA_MAX_ALTER_FLAGS when adding more flags! */
/* Return values for check_if_supported_alter */
#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
/*
Check if a storage engine supports a particular alter table on-line
SYNOPSIS
check_if_supported_alter()
altered_table A temporary table show what table is to change to
create_info Information from the parsing phase about new
table properties.
alter_flags Bitmask that shows what will be changed
table_changes Shows if table layout has changed (for backwards
compatibility with check_if_incompatible_data
RETURN
HA_ALTER_ERROR Unexpected error
HA_ALTER_SUPPORTED_WAIT_LOCK Supported, but requires DDL lock
HA_ALTER_SUPPORTED_NO_LOCK Supported
HA_ALTER_NOT_SUPPORTED Not supported
NOTES
The default implementation is implemented to support fast
alter table (storage engines that support some changes by
just changing the frm file) without any change in the handler
implementation.
*/
virtual int check_if_supported_alter(TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_FLAGS *alter_flags,
uint table_changes)
/*
Tell storage engine to prepare for the on-line alter table (pre-alter)
SYNOPSIS
alter_table_phase1()
thd The thread handle
altered_table A temporary table show what table is to change to
alter_info Storage place for data used during phase1 and
phase2
alter_flags Bitmask that shows what will be changed
RETURN
0 OK
error error code passed from storage engine
NOTES
*/
virtual int alter_table_phase1(THD *thd,
TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_INFO *alter_info,
HA_ALTER_FLAGS *alter_flags)
/*
Tell storage engine to perform the on-line alter table (alter)
SYNOPSIS
alter_table_phase2()
thd The thread handle
altered_table A temporary table show what table is to change to
alter_info Storage place for data used during phase1 and
phase2
alter_flags Bitmask that shows what will be changed
RETURN
0 OK
error error code passed from storage engine
NOTES
If check_if_supported_alter returns HA_ALTER_SUPPORTED_WAIT_LOCK
this call is to be wrapped with a DDL lock. This is currently NOT
supported.
*/
virtual int alter_table_phase2(THD *thd,
TABLE *altered_table,
HA_CREATE_INFO *create_info,
HA_ALTER_INFO *alter_info,
HA_ALTER_FLAGS *alter_flags)
/*
Tell storage engine that changed frm file is now on disk and table
has been re-opened (post-alter)
SYNOPSIS
alter_table_phase3()
thd The thread handle
table The altered table, re-opened
NOTES
*/
virtual int alter_table_phase3(THD *thd, TABLE *table)
Martin Skold wrote:
> Hi Jan!
>
> We are in the process of re-designing the current on-line alter table
> interface (which currently
> only supports add/drop index). The old interface is based on handlers
> having "capability"
> flags (handlerton::alter_table_flags) and the MySQL server
> orchestrates the steps needed for
> different alter table operations. After discussions this approach has
> been decided to
> be too complex since different storage engines need to do various
> operations in
> different ways. Also adding flags and new handler calls for each
> possible change will
> make the handler interface huge.
> The current plan is to add more general calls (note that the interface
> is still under design
> and might change, but you should get the idea):
>
> One call for asking the handler if it can do the alteration on-line:
>
> int handler::check_if_supported_alter(TABLE *altered_table,
> HA_CREATE_INFO *create_info,
> HA_ALTER_FLAGS alter_flags,
> uint table_changes);
>
> where 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_ADD_FOREIGN_KEY (1L << 16)
> #define HA_DROP_FOREIGN_KEY (1L << 17)
> #define HA_ADD_CONSTRAINT (1L << 18)
> #define HA_ADD_PARTITION (1L << 19)
> #define HA_DROP_PARTITION (1L << 20)
> #define HA_COALESCE_PARTITION (1L << 21)
> #define HA_REORGANIZE_PARTITION (1L << 22)
> #define HA_CHANGE_CHARACTER_SET (1L << 23)
> #define HA_SET_DEFAULT_CHARACTER_SET (1L << 24)
> #define HA_RENAME_TABLE (1L << 25)
>
>
> Two calls are currently planned for executing the actual alteration:
>
> int handler::prepare_alter_table(TABLE *altered_table,
> HA_CREATE_INFO *create_info,
> HA_ALTER_FLAGS alter_flags);
>
> int handler::alter_table(TABLE *altered_table,
> HA_CREATE_INFO *create_info,
> HA_ALTER_FLAGS alter_flags);
>
>
> The only flag I see missing here would be for setting/changing column
> defaults, I will consider
> adding some flag(s) for that. The flags are supposed to hint what
> changes are made to the table,
> and the 'altered_table' argument will allow the handler to check
> exactly what has changed.
>
> Please feel free to comment if you see something missing in the
> current proposal.
>
> BR
> -- Martin
> Jan Lindström wrote:
>> Hi,
>>
>> I propose few additional functions to MySQL storage engine handler
>> interface:
>>
>> ::add_foreign_keys(THD* thd, TABLE *table_arg, foreign_key* fk_key, uint
>> n_fkeys); Assuming that HA_CAN_ADD_FOREIGN_KEYS is set this
>> function is used for implementing alter table add
>> foreign key ...
>> ::drop_foreign_keys(THD* thd, TABLE *table_arg, char* fk_names, uint
>> n_fkeys);
>> Assuming that HA_CAN_DROP_FOREIGN_KEYS is set
>> this function is used to implement alter table drop foreign
>> key...
>>
>> ::set_column_defaults(THD* thd, TABLE *table_arg, char* field_names,
>> mysql_byte* default_values, uint n_defvalues);
>> Assuming that HA_CAN_SET_DEFAUL_CASCADE is set this function
>> is used to modify column default value in alter table modify
>> column a set default ...
>>
>> Note that this would require that storage engine can in ::create
>> determine default value for the column (e.g.
>> Field->get_default_value())
>>
>> ::add_columns(THD* thd, TABLE *table_arg, Field* fields, uint n_fields);
>>
>> Assuming that HA_CAN_ADD_COLUMNS is set this function is used
>> to implement alter table add column ...
>>
>> ::drop_columns(THD* thd, TABLE *table_arg, char* field_names, uint
>> n_fields);
>> Assuming that HA_CAN_DROP_COLUMNS is set this function is used
>> to implement alter table drop column ...
>>
>> R: Jan Lindström
>> Solid Information Technology Ltd
>>
>>
>>
>
>
