MySQL: Waiting for metadata lock with ALGORITHM=COPY

Question

I'm trying to execute an ALTER TABLE in MySQL. MySQL only lets me execute it with the ALGORITHM=COPY (because I need to change the type of a column).

There aren't queries using that table (to write neither to read).

But, I don't know why, when I execute the ALTER there are queries (UPDATES) which are not using this table (they are in a transaction) locked. MySQL says "mysql waiting for metadata lock".

So the question is, why the query is waiting for metadata lock if the UPDATE is not using the table altered?

I read some doc:

But I don't understand why the queries are locked for metadata.

Reproduction of the problem in dev environment:

First, do the alter:

ALTER TABLE API.SEARCHES_ELEMENTS
  MODIFY COLUMN TYPE ENUM('A', 'B') NOT NULL,
  ALGORITHM=COPY;

Second, change values in other tables (there isn't transaction):

UPDATE CLIENTS
SET NAME = CONCAT('test-', RAND())
WHERE ID_CLIENT = 1;

The locks:

SELECT *
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE
  PROCESSLIST_ID <> CONNECTION_ID();

Maybe the problem is due the lock over the SCHEMA?

user_1581433 · Answer

Seems like the idea is to avoid the ALGORITHM=COPY (rebuilds without in-place mode)

So instead of modify the column type

ALTER TABLE API.SEARCHES_ELEMENTS
  MODIFY COLUMN TYPE ENUM('A', 'B') NOT NULL,
  ALGORITHM=COPY;

is better to create a new column, copy the data and remove the old one:

ALTER TABLE API.SEARCHES_ELEMENTS
  ADD COLUMN TYPE_NEW ENUM('A', 'B') NOT NULL AFTER TYPE,
  ALGORITHM=INSTANT;

LOCK TABLES API.SEARCHES_ELEMENTS WRITE;

UPDATE API.SEARCHES_ELEMENTS SET TYPE_NEW = TYPE;

ALTER TABLE API.SEARCHES_ELEMENTS
  RENAME COLUMN TYPE TO TYPE_OLD,
  RENAME COLUMN TYPE_NEW TO TYPE,
  ALGORITHM=INSTANT;

UNLOCK TABLES;

ALTER TABLE API.SEARCHES_ELEMENTS
  DROP COLUMN TYPE_OLD,
  ALGORITHM=INPLACE;

Note: adding a value in ENUM might be use the algorithm=instant

Modifying the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

user_4290768 · Answer

A tables metadata is not only locked when a running query is using it, but also if it has previously been used in an active transaction until that transaction commits or rolls back, to prevent the table from changing while still being referenced by the transaction.

If you are running at least MySQL 5.7 and have the performance_schema enabled you can check for current metadata locks via the performance_schema.metadata_locks table.

MySQL: Waiting for metadata lock with ALGORITHM=COPY

2 Answers