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.

Add Index

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

ItUses won't block during the update, however it can block at the very start and end when attempting to get a table metadata lock if other long-running transactions are active. If this happens you can either wait or kill those longer-running transactions so that this command can complete.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 extending a varchar type, not changing types entirely. 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_id BIGINT 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.


Back to top