Please read Part I first

When we come to altering table, there is a lot happening under the hood. You can find a list of ALTER TABLE ... PARTITION ... here. And this is a short list:

| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| TRUNCATE PARTITION {partition_names | ALL}
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}

Key Functions


As usual, a command from the client goes through do_command/dispatch_command/mysql_parse and for altering table, it goes into the alter table's statement execution. And finally, it reaches mysql_alter_table.

This function is a pretty big one.

  • It does a lot of checking stuff, and calls prep_alter_part_table. This one gathers partition info and updates it acoordingly.
  • Then uses mysql_compare_tables and other checks to decide wheather we need a temp table for a full copy.
  • If we can do a fast alter, then call fast_alter_partition_table
  • Otherwise
    • just create a temp table with mysql_create_table_no_lock, and
    • copy data from old table to temp table with copy_data_between_tables
    • delete old one, rename temp table to the old name, and make it back online

One thing to note is that altering a table is pretty similar to creating a table, especially when we need a full copy and call mysql_create_table_no_lock. Most of the time mysql_alter_table is trying to fill in the create_info object.

Another important thing for this function (and all other functions probably) is that they mainly maintain two core things: data in the database, and metadata about them. Here it updates the .frm file, and updates data accordingly.


In some cases, we don't need a full copy to alter table. It could be adding/droping/coalesce partitions, or some cases in rebuild/reorg partitions. From my understanding, this function mainly updates the metadata part, via mysql_change_partitions, mysql_drop_partitions, mysql_rename_partitions.

These three functions will call into more low level functions in /sql/ e.g.

  • change_partitions update metadata as a preperation
  • copy_partitions actually copy data from old partitions into new ones.
    • rnd_next this one is used to do a linear scan.
    • ha_write_row this ons is used to write the found record into new partition, this is a call to the underlying storage engine.
  • rename_partitions
    • ha_rename_table is a call to the underlying SE to rename a table.
  • drop_partitions
    • ha_delete_table this is a call to the underlying storage engine to delete the table. This is beacuse from the partition SE's view, it is a partition. But for the underlying SE, it is a table. Partition SE is built on top of some underlying engine.