18

I'm running the following ALTER command on a MySQL 5.6 database on a large table with 60 million rows:

ALTER TABLE `large_table` ADD COLUMN `note` longtext NULL, 
ALGORITHM=INPLACE, LOCK=NONE;

Despite specifying both ALGORITHM=INPLACE and LOCK=NONE, the table gets locked and essentially takes down the app until the migration is complete.

I verified that the table was indeed locked by checking the value of the In_use column on the output of the SHOW OPEN TABLES command. It was set to 1.

From what I gather in the MySQL documentation, this operation should not be locking the table. And, MySQL is supposed to fail the command if it is not able to proceed without a lock. I upgraded the database to MySQL 5.7 to see if it's any better, but I face the same problem on 5.7 too.

Is this an expected behavior? How do I find out what's going wrong here?

jeffreyveon
  • 13,400
  • 18
  • 79
  • 129
  • 2
    It's not InnoDB? You might wanna check [this question](https://stackoverflow.com/q/35424543/5403468). – Til Feb 18 '19 at 15:10
  • Hi Jeffrey, I believe I encounter the same issue recently. The table is locked for writes periodically during a time consuming DDL for a large table. We are planning to use 3rd party tools to do the DDL. But have you ever figured out the reason why it's locking the table even though `ALGORITHM=INPLACE, LOCK=NONE;` is specified? – Allen Hsu Feb 19 '22 at 14:05
  • 1
    @AllenHsu We could not and it was very unpredictable, so had to eventually move to MySQL 8 which solved these issues, with the availability of `ALGORITHM=INSTANT`. I highly recommend that switch: made life so much easier. – jeffreyveon Feb 20 '22 at 02:55
  • Thanks for the prompt response @jeffreyveon. I'll assess the effort of upgrading to MySQL 8 and give it a try. – Allen Hsu Feb 21 '22 at 06:43

2 Answers2

12

I assume you were not doing some other DDL on that table at about the same time?

For the future:

8.0.12 has ALTER TABLE .. ALGORITHM=INSTANT for ADD COLUMN. See Discussion and ALTER Reference and Online DDL Reference

The following limitations apply when the INSTANT algorithm is used to add a column:

  • Adding a column cannot be combined in the same statement with other ALTER TABLE actions that do not support ALGORITHM=INSTANT.
  • A column can only be added as the last column of the table. Adding a column to any other position among other columns is not supported.
  • Columns cannot be added to tables that use ROW_FORMAT=COMPRESSED.
  • Columns cannot be added to tables that include a FULLTEXT index.
  • Columns cannot be added to temporary tables. Temporary tables only support ALGORITHM=COPY.
  • Columns cannot be added to tables that reside in the data dictionary tablespace.
  • Row size limits are not evaluated when adding a column. However, row size limits are checked during DML operations that insert and update rows in the table.

Multiple columns may be added in the same ALTER TABLE statement.

If you can't upgrade then consider Percona's pt-online-schema-change or a new, competing, product gh-ost (which uses the binlog).

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    I second the suggestion of using pt-osc or gh-ost. I just counted our logs, and discovered my employer has done over 70,000 schema migrations using pt-osc. – Bill Karwin Feb 20 '19 at 21:25
8

I also have experienced issues with MySQL 5.6 locking even when ALGORITHM=INPLACE, LOCK=NONE; is used. I would first check the following:

Check constraints on table

The ALTER TABLE clause LOCK=NONE is not permitted if there are ON...CASCADE or ON...SET NULL constraints on the table.

Source: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

Does the table have a foreign key relationship?

An online DDL operation on a table in a foreign key relationship does not wait for a transaction executing on the other table in the foreign key relationship to commit or rollback. The transaction holds an exclusive metadata lock on the table it is updating and shared metadata lock on the foreign-key-related table (required for foreign key checking). The shared metadata lock permits the online DDL operation to proceed but blocks the operation in its final phase, when an exclusive metadata lock is required to update the table definition. This scenario can result in deadlocks as other transactions wait for the online DDL operation to finish.

Source: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

And Read about Metadata locking here: https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

Alter tables from old timeformat first

If you have created your tables from before MySQL 5.6 with DATETIME or TIMESTAMP fields, they need to be upgraded to MySQL's 5.6 new format.

InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns the following error:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Source: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

Check if the table has Partitioning

Partitioning changes how alter table rules apply. Check partitioning status of table

show table status;

Look for 'Engine' not equalling InnoDB

Sources: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html#online-ddl-partitioning

Finally as Rick James in his answer mentioned, upgrading from 5.6 to 8.0 may be an option as it provides other improvements.

Chris
  • 460
  • 8
  • 16
  • 1
    Thanks, this is very useful. Do you know if some of the shortcomings mentioned (like foreign key relationship) have been addressed in MySQL 8? – jeffreyveon Jul 22 '19 at 06:22
  • @jeffreyveon I have not had any personal experience with MySQL 8. However from the following link to their documentation on limitations for 8.0, there are some shared, some removed and some different limitations. https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html – Chris Jul 22 '19 at 22:54