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

Language:
English Edit question
Status:
Solved
For:
Percona Server Edit question
Assignee:
No assignee Edit question
Solved by:
Viacheslav Biriukov
Solved:
2012-08-14
Last query:
2012-08-14
Last reply:
2012-08-14
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: http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

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.