MySQL: Waiting for metadata lock with ALGORITHM=COPY-CodePudding

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:

Maybe the problem is due the lock over the SCHEMA?

CodePudding user response：

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.

See also:

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-metadata-locks-table.html

https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html

CodePudding user response：

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,
  ALGORITHM=INSTANT;

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;

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.