Crash safety of foreign key DDL

High level design

Dictionary

• Old state: state before DDL command;
• New state: state expected after DDL command;
• Current table: table being modified by DDL command;
• Ref table: parent or child table in relation to current table.
• Normal frm: old FRM of ref table corresponding to its old TABLE_SHARE
• Backup frm: renamed normal frm;
• Shadow frm: new FRM for ref table with updated FK info.

Algorithm

0. Get info, lock ref tables;
1. for each ref table: modify TABLE_SHARE;
2. for each ref table:
2.1. Write DDL_LOG_DELETE_ACTION for shadow frm;
2.2. Write shadow frm.
3. Modify current table and its frm by ongoing DDL;
4. for each ref table:
4.1. Write DDL_LOG_REPLACE_ACTION from backup to normal frm;
4.2. Rename normal to backup frm.
5. for each ref table:
5.1. move shadow to normal frm;
5.2. deactivate DDL_LOG_DELETE_ACTION for shadow.
6. for each ref table: deactivate DDL_LOG_REPLACE_ACTION
7. for each ref table: write DDL_LOG_DELETE_ACTION for backup
8. for each ref table:
8.1. delete backup frm;
8.2. deactivate DDL_LOG_DELETE_ACTION for backup frm.

Failure analysis

If failure happens at 0-3: the DDL fails, current table and ref tables restored to old state.
If failure happens at 4: operation continues, 5-8 are executed for non-failed ref tables.
If failure happens at 5: revert 4 for failed ref and continue (excluding failed tables).
If failure happens at 6: drop normal, revert 4 and continue (excluding failed tables).
If failure happens at 7-8: just ignore (possibly print warning).

Failure at 0-3 results with error message. Current and ref tables are at old state;
Failures at 4-6 result with success and warning info about failed ref tables. Failed ref tables contain old data about foreign keys and can be fixed by REPAIR TABLE.
Failures at 7-8 result with success and possibly some stale files and warnings about them

Crash analysis

If crash happens before 3: everything is at its old state;
If crash happens before 6: current table is new, but ref tables are old;
If crash happens at 6: current table is new, some ref tables are new, some ref tables are old;
If crash happens after 6: current table is new, ref tables are new.

Low level design

p.2 is realized by fk_write_shadow_frm() which is done by:

• TABLE_SHARE::fk_handle_create()
• Alter_table_ctx::fk_handle_alter()
• fk_handle_drop()
• fk_handle_rename()

pp.4-8 are realized by fk_safe_install_shadows().

TABLE_SHARE::fk_handle_create()

Is done by ha_create_table() after share is inited. It does:

1. Collect and locks parent tables;
2. Acquire and updates parent shares;
3. Write shadow frms.

fk_safe_install_shadows() is done by ha_create_table() after closefrm().

Alter_table_ctx::fk_handle_alter()

Is done by:

• mysql_alter_table() after wait_while_table_is_used() (TDC is cleared);
• mysql_inplace_alter_table() after wait_while_table_is_used()

It does:

1. Upgrade ref locks to MDL_EXCLUSIVE (they are locked earlier by mysql_prepare_alter_table());
2. Update ref shares according to collected operations;
3. Write shadow frms.

fk_safe_install_shadows() is done by:

• mysql_alter_table() before deleting the backup of the old table;
• mysql_inplace_alter_table() after close_all_tables_for_name().

fk_handle_rename()

Is done by:

• do_rename() before tdc_remove_table();
• simple_rename_or_index_change() after wait_while_table_is_used() (TDC is cleared).

The difference in order here is because of different roles of TDC clear in these cases. For do_rename() it is only and final TDC clear, but fk_handle_rename() acquires share again.

It does:

1. Collect ref tables;
2. Upgrade old table to MDL_EXCLUSIVE;
3. Lock ref tables;
4. Acquire and update ref shares;
5. Write shadow frms.

fk_safe_install_shadows() is done by:

• mysql_rename_tables() after succesful rename_tables();
• simple_rename_or_index_change() after succesful mysql_rename_table() and rename_table_in_stat_tables().

fk_handle_drop()

Is done by mysql_rm_table_no_locks() before tdc_remove_table() (by the same reason as for do_rename()).

It does:

1. Collect and locks ref tables;
2. Acquire and updates ref shares;
3. Write shadow frms.

fk_safe_install_shadows() is done by mysql_rm_table_no_locks() after succesful drop of triggers.