Percona Server moved to https://jira.percona.com/projects/PS

alter table disable keys on InnoDB table and uncommitted transactions

Asked by Viacheslav Biriukov

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 moved to https://jira.percona.com/projects/PS Edit question
Assignee:
No assignee Edit question
Solved by:
Viacheslav Biriukov
Solved:
Last query:
Last reply:
Revision history for this message
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

Revision history for this message
Viacheslav Biriukov (v-v-biriukov) said : 		#2

Thanks, you have answered on my question.

Revision history for this message
Andrii Grytsenko (andrii-grytsenko) said : 		#3

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!

Revision history for this message
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).

Revision history for this message
Andrii Grytsenko (andrii-grytsenko) said : 		#5

Thank you made everything clear to me.