alter table disable keys on InnoDB table and uncommitted transactions

Asked by Viacheslav Biriukov on 2012-08-14

Percona Server 5.1

Start some transaction and don't commit it. In other console try to disable keys on InnoDB table (yes, I know that it is not possible fo InnoDB storage Engine):

> alter table t1 disable keys;

Query OK, 0 rows affected, 1 warning (0.2 sec)

> show warnings;
| Level | Code | Message                                                |
| Note  | 1031 | Table storage engine for 't1' doesn't have this option |
1 row in set (0.01 sec)

It's IMMEDIATELY show warnings.

With Percona Server 5.5 it is waiting for transaction commit.

Question information

English Edit question
Percona Server Edit question
No assignee Edit question
Solved by:
Viacheslav Biriukov
Last query:
Last reply:
Alexey Kopytov (akopytov) said : #1

I'm not sure about what is the question here.

In MySQL and Percona Server 5.5 the other session is waiting on a metadata lock. Metadata locking was introduced in MySQL 5.5:

Thanks, you have answered on my question.

I have the question here too. As far I know DISABLE/ENABLE KEYS are not supported by InnoDB, please correct me if I'm wrong. If that true why would MYSQL lock DISABLE/ENABLE KEYS with Metadata locking during unfinished transaction, instead of just ignore this statement like it was in 5.1 version.

Thank you!

Alexey Kopytov (akopytov) said : #4

Yes, InnoDB doesn't support DISABLE/ENABLE KEYS, though ALTER TABLE still acquires a metadata lock (and then generates a warning). Which might be considered as a bug. On the other hand, the workaround is trivial: don't use those operations, since they make no sense with InnoDB.

The reason is that metadata locks are above the storage engines layer, i.e. ALTER TABLE acquires a lock before talking to the corresponding storage engine (and hence, looking at the engine type).

Thank you made everything clear to me.