Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Mailing Lists: lists.mysql.com > internals > 07 > 090298.html (Request Expert lists.mysql.com Support)

New online alter table interface [Re: Adding new funtions to handler interface]

From: Martin Skold <Martin.Skold(at)mysql.com>
Date: Wed Sep 19 2007 - 10:32:22 EDT


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! */

Do you need help?X

/* 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

Can we help you?X

        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)
Do you need more help?X

 /*

    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,

Can't find what you're looking for?X

>                                      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,

Don't know where to look next?X

>                         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 ...
>>

Confused? Frustrated?X

>> ::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
>>
>>
>>   
>
>

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com
Received on Wed Sep 19 08:32:41 2007

This archive was generated by hypermail 2.1.8 : Sun Oct 07 2007 - 07:59:31 EDT

Contact Us  Legal Notices  Order Services Online 
Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library