Skip to main content

Alter Table

These are native MySQL Alter Table recommendations for 8.0 and above production systems. If they don't fit your needs, check out Percona Tools as well.

The advice below will always suggest the least-blocking option. In general:

  • ALGORITHM=INPLACE - Requires a metadata table lock when the transaction starts and finishes. Otherwise will not block the table. If you have active long-running transactions you will need to kill them at the start and end of processing.
  • ALGORITHM=INSTANT - Does not require any locking. This is always the best option but only supported in a few scenarios.
  • ALGORITHM=COPY - Blocks the table during rebuild. Consider using pt-online-schema-change if you don't have (many) foreign keys. Cannot use LOCK=NONE with this algorithm.

Add Index

This alter command allows adding an index to an active table in production.

Uses ALGORITHM=INPLACE

ALTER TABLE table_name
  ALGORITHM=INPLACE,
  LOCK=NONE,
  ADD INDEX some_index_name (field,list,of,index,cols)

Modify Column - Non FK

This alter command allows modifying a column type in production.

Generally you can only use INPLACE for things like slightly extending a varchar type, or other scenarios where the column width/type doesn't change. You can always try INPLACE without fear and you will get an error if it's not allowed.

Warning: This option won't work for columns that are part of a foreign key.

Uses ALGORITHM=INPLACE or COPY

ALTER TABLE my_table
  ALGORITHM=INPLACE,
  LOCK=NONE,
  MODIFY COLUMN user_name VARCHAR(45) NOT NULL

Some types of updates that extend the data length may trigger the following error. In this case you can either use COPY which will block the table, or use pt-online-schema-change.

SQL Error [1846] [0A000]: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.


No comments to display

Back to top