What replacement algorithm does this ALTER TABLE use

Question

Question

I am trying to figure out what kind of replacement algorithm a command like the following would use

ALTER TABLE `catalog_category_product_index_store1` COMMENT='Catalog Category Product Index Store1'

I am thinking that it would be INPLACE as the documentation at https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-performance says

ALTER TABLE operations that support the INPLACE algorithm include:

...

An example of such a change is a change to the column comment.

However, that is for a column comment not a table comment and I wonder if that may be different? Can anyone help me figure out what algorithm is used for this statement or if there's a way to log it or something?

Thanks

Additional context

The table definition

mysql> show create table catalog_category_product_index_store1\G
*************************** 1. row ***************************
       Table: catalog_category_product_index_store1
Create Table: CREATE TABLE `catalog_category_product_index_store1` (
  `category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Category Id',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product Id',
  `position` int(11) DEFAULT NULL COMMENT 'Position',
  `is_parent` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Is Parent',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id',
  `visibility` smallint(5) unsigned NOT NULL COMMENT 'Visibility',
  PRIMARY KEY (`category_id`,`product_id`,`store_id`),
  KEY `CAT_CTGR_PRD_IDX_STORE1_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY` (`product_id`,`store_id`,`category_id`,`visibility`),
  KEY `IDX_216E521C8AD125E066D2B0BAB4A08412` (`store_id`,`category_id`,`visibility`,`is_parent`,`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Category Product Index Store1 Replica'
1 row in set (0.00 sec)

I believe this query is causing some kind of lock which causes downtime on our site, but I'm not 100% sure on that. I'm trying to understand a little more about what replacement this alter table statement is actually doing and how it achieves it.

Answer 1

MySQL supports metadata comments on the table, columns, indexes, and partitions. In all cases, modifying the comments is a metadata-only action, and it can be done without doing a table copy.

However, any ALTER TABLE, even an inplace alter, requires a metadata lock. The acquisition of a metadata lock is blocked while any transaction exists that has touched that table.

I can do a demo using two windows:

Window 1:

mysql> create table mytable (id int primary key);
Query OK, 0 rows affected (0.05 sec)

Window 2:

mysql> start transaction;

mysql> select * from mytable;
Empty set (0.00 sec)

So far so good, that's what we expect. But note that even though that query is done, the transaction is still uncommitted. Therefore the session in window 2 holds the metadata lock on that table.

Window 1:

mysql> alter table mytable comment='my table';
<hangs>

This is blocked, waiting for the outstanding transaction in window 2 to finish.

Furthermore, any other subsequent query can't get its own metadata lock while window 1 is waiting.

Window 3:

mysql> select * from mytable;
<hangs>

It's queued up behind the metadata lock request in Window 1.

If I commit in window 2:

mysql> commit;

Then finally the alter table finishes:

mysql> alter table mytable comment='my table';
Query OK, 0 rows affected (4 min 23.98 sec)

And then the query in third window also finishes:

mysql> select * from mytable;
Empty set (4 min 8.32 sec)

In this way, even an ALTER TABLE that does an inplace alter can accidentally cause an outage.