Bug #37453 Dropping/creating index on partitioned table with InnoDB Plugin locks table
Submitted: 17 Jun 2008 17:10 Modified: 10 Dec 2008 20:29
Reporter: Partha Dutta Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.25-rc OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: innodb plugin partition fast index creation

[17 Jun 2008 17:10] Partha Dutta 
Description:
The documentation in the innodb plugin manual states that there is only a shared lock on tables while the command is running.  This does not work with partitioned tables, and the table is recreated.

For a normal table, dropping indexes is fast and does not lock tables. Adding index still locks table.

How to repeat:
Very roughly....

CREATE TABLE `foo1` (
   id int not null auto_increment primary key,
   uuid char(36) not null
) engine=innodb row_format=compressed key_block_size=8
partition by hash(id) subpartitions 10;

insert into foo1 (uuid) values (uuid());
insert into foo1 (uuid) values (uuid());
insert into foo1 (uuid) values (uuid());
insert into foo1 (uuid) values (uuid());
insert into foo1 (uuid) values (uuid());

insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;
insert into foo1(uuid) select uuid() from foo1;

From 1st session:
create index `ix_2` on foo1(uuid);
mysql> create index `ix_2` on foo1(uuid);
Query OK, 1310720 rows affected (31.95 sec)
Records: 1310720  Duplicates: 0  Warnings: 0

From 2nd session:
select * from foo1 limit 20;
mysql> select * from foo1 limit 20;
+----+--------------------------------------+
| id | uuid                                 |
+----+--------------------------------------+
|  1 | 9f6695b8-8dde-102b-ad6e-001b78cebc8c | 
|  2 | 9fe30ac6-8dde-102b-ad6e-001b78cebc8c | 
|  3 | a0436f06-8dde-102b-ad6e-001b78cebc8c | 
|  4 | a09b5e8c-8dde-102b-ad6e-001b78cebc8c | 
|  5 | a6001746-8dde-102b-ad6e-001b78cebc8c | 
|  6 | b995d2dc-8dde-102b-ad6e-001b78cebc8c | 
|  7 | b995d458-8dde-102b-ad6e-001b78cebc8c | 
|  8 | b995d4e4-8dde-102b-ad6e-001b78cebc8c | 
|  9 | b995d552-8dde-102b-ad6e-001b78cebc8c | 
| 10 | b995d5ca-8dde-102b-ad6e-001b78cebc8c | 
| 11 | ba1c0672-8dde-102b-ad6e-001b78cebc8c | 
| 12 | ba1c0762-8dde-102b-ad6e-001b78cebc8c | 
| 13 | ba1c07da-8dde-102b-ad6e-001b78cebc8c | 
| 14 | ba1c083e-8dde-102b-ad6e-001b78cebc8c | 
| 15 | ba1c08b6-8dde-102b-ad6e-001b78cebc8c | 
| 16 | ba1c091a-8dde-102b-ad6e-001b78cebc8c | 
| 17 | ba1c0988-8dde-102b-ad6e-001b78cebc8c | 
| 18 | ba1c09ec-8dde-102b-ad6e-001b78cebc8c | 
| 19 | ba1c0adc-8dde-102b-ad6e-001b78cebc8c | 
| 20 | ba1c0b9a-8dde-102b-ad6e-001b78cebc8c | 
+----+--------------------------------------+
20 rows in set (30.58 sec)

Dropping index:

mysql> drop index `ix_2` on foo1;
Query OK, 1310720 rows affected (18.81 sec)
Records: 1310720  Duplicates: 0  Warnings: 0

Concurrent select:
mysql> select * from foo1 where id=1;
+----+--------------------------------------+
| id | uuid                                 |
+----+--------------------------------------+
|  1 | 9f6695b8-8dde-102b-ad6e-001b78cebc8c | 
+----+--------------------------------------+
1 row in set (8.47 sec)

==================================
Without partitioning:

create table foo1 ( id int not null auto_increment primary key, uuid char(36) not null ) engine=innodb row_format=compressed key_block_size=8;

mysql> insert into foo1(uuid) select (uuid());Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select (uuid());
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select (uuid());
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select (uuid());
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select (uuid());
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 40 rows affected (0.00 sec)
Records: 40  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 80 rows affected (0.00 sec)
Records: 80  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 160 rows affected (0.01 sec)
Records: 160  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 320 rows affected (0.01 sec)
Records: 320  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 640 rows affected (0.02 sec)
Records: 640  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 1280 rows affected (0.03 sec)
Records: 1280  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 2560 rows affected (0.05 sec)
Records: 2560  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 5120 rows affected (0.12 sec)
Records: 5120  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 10240 rows affected (0.16 sec)
Records: 10240  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 20480 rows affected (0.31 sec)
Records: 20480  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 40960 rows affected (0.61 sec)
Records: 40960  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 81920 rows affected (1.23 sec)
Records: 81920  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 163840 rows affected (2.64 sec)
Records: 163840  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 327680 rows affected (4.85 sec)
Records: 327680  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 655360 rows affected (9.69 sec)
Records: 655360  Duplicates: 0  Warnings: 0

mysql> insert into foo1(uuid) select uuid() from foo1;
Query OK, 1310720 rows affected (20.05 sec)
Records: 1310720  Duplicates: 0  Warnings: 0

Create index from 1st session:
mysql> create index `ix_2` on foo1(uuid);
Query OK, 0 rows affected (31.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Select from 2nd session:
mysql> select * from foo1 where id=1;
+----+--------------------------------------+
| id | uuid                                 |
+----+--------------------------------------+
|  1 | e133b902-8ddf-102b-ad6e-001b78cebc8c | 
+----+--------------------------------------+
1 row in set (29.51 sec)

Dropping index :
mysql> drop index `ix_2` on foo1;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
The behavior should be the same for partitioned tables and non-partitioned tables.
[19 Jun 2008 13:33] Sveta Smirnova 
Thank you for the report.

But how you detected different behavior with partitioned and not partitionde table? I see "copy to tmp table" in SHOW PROCESSLIST output and get SELECT locked for both cases.
[20 Jun 2008 16:28] Sveta Smirnova 
I created bug #37550 about "copy to tmp table"
[22 Jun 2008 23:41] Partha Dutta 
From the original post, I simply timed the execution times of each select statement, and observed the output. The select should return in a few miliseconds, but gets blocked by the create/drop index statements.
[23 Jun 2008 17:22] Heikki Tuuri 
Assigning this to Marko.
[23 Jun 2008 17:24] Heikki Tuuri 
The MySQL partition engine should tell the engine to use fast index create / drop.

Hmm... reassigning this as a partition bug, so that people can check what does the partition engine instruct the storage engine to do.
[9 Jul 2008 19:25] Sveta Smirnova 
Thank you for the report.

Re-creating of tables when creating index if using partitioning verified as described.

Lock issues is another problem and probably new bug should be created.

Method used for verification:

follow original instruction, then in time of index creating examine datadir.
[4 Oct 2008 10:17] Mattias Jonsson 
This will need a change of the handler interface which might affect the storage engine api.

The check for which alter table flags a handler have is done through the handlerton, which will not work correctly for any partitioned table. It must be through the handler interface.

To be able to fix this I have to add a virtual function to the handler class:
=== modified file 'sql/handler.h'
--- sql/handler.h	2008-08-11 18:02:03 +0000
+++ sql/handler.h	2008-10-03 08:07:28 +0000
@@ -1724,6 +1724,12 @@
     but we don't have a primary key
   */
   virtual void use_hidden_primary_key();
+  virtual uint alter_table_flags(uint flags)
+  {
+    if (ht->alter_table_flags)
+      return ht->alter_table_flags(flags);
+    return 0;
+  }
 
and then implement it in ha_partition.
[4 Oct 2008 22:41] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55348

2746 Mattias Jonsson	2008-10-05
      Bug#37453: Dropping/creating index on partitioned table with
      InnoDB Plugin locks table
      
      The fast/on-line add/drop index handler calls was not implemented
      whithin the partitioning.
      
      This implements it in the partitioning handler.
      
      Since this is only used by the not included InnoDB plugin, there
      is no test case. (Have tested it manually with the plugin, and
      it does not allow unique indexes not including partitioning
      function, or removal of pk, which in innodb generates a new pk,
      which is not in the partitioning function.)
      
      NOTE: This introduces a new handler method, and because of that
      changes the storage engine api. (One cannot use a handlerton to
      see the capabilities of a table's handler if it is partitioned.
      So I added a wrapper function in the handler that defaults to
      the handlerton function, which the partitioning handler overrides.
[8 Oct 2008 9:12] Mattias Jonsson 
Pushed the handler.h change to 5.1.29-rc (and null-merged it to 6.0, since that part of the api is changed in 6.0)

Will push the rest of the fix when it is approved
[9 Oct 2008 17:32] Bugs System 
Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081008074630-4wzwlyzftpun0dvg) (version source revid:mats@sun.com-20081008113713-2vxny72m5w1tywoi) (pib:4)
[16 Oct 2008 8:16] Mikael Ronström 
Needs a null merge to 6.0 and a new patch for 6.0
using the new online interface introduced in 6.0.
[17 Oct 2008 16:41] Bugs System 
Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081008074630-4wzwlyzftpun0dvg) (version source revid:kgeorge@mysql.com-20081008092313-4wzrak2duhsz7ylm) (pib:5)
[28 Oct 2008 21:02] Bugs System 
Pushed into 5.1.29-ndb-6.2.17  (revid:mattias.jonsson@sun.com-20081008074630-4wzwlyzftpun0dvg) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:21] Bugs System 
Pushed into 5.1.29-ndb-6.3.19  (revid:mattias.jonsson@sun.com-20081008074630-4wzwlyzftpun0dvg) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[29 Oct 2008 22:41] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57384

2895 Mattias Jonsson	2008-10-29 [merge]
      Null merge of bug#37453, since the fast/online alter
      interfaces differs between 5.1 and 6.0.
      (Was done to support InnoDB PlugIn in 5.1)

-- 
MySQL Code Commits Mailing List
For list archives: http://lists.mysql.com/commits
To unsubscribe:    http://lists.mysql.com/commits?unsub=commits@bugs.mysql.com
[30 Oct 2008 8:09] Mattias Jonsson 
Pushed into 5.1-bugteam, null-merged to 6.0-bugteam (6.0's fast/online alter interface differs, and the innodb plug-in is not available for 6.0)
[1 Nov 2008 9:46] Bugs System 
Pushed into 5.1.29-ndb-6.4.0  (revid:mattias.jonsson@sun.com-20081008074630-4wzwlyzftpun0dvg) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)
[1 Nov 2008 16:54] Paul DuBois 
Noted in 5.1.30 changelog.

Dropping or creating an index on a partitioned table managed by the
InnoDB Plugin locked the table.

There is no 6.0.x entry (null merge).
[1 Nov 2008 19:53] Jon Stephens 
Fix is also documented in the 5.1.29-ndb-6.2.17 and 5.1.29-ndb-6.3.19 changelogs. Closed.
[10 Nov 2008 10:51] Bugs System 
Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081029224044-25b62zau0dk9jpux) (version source revid:mattias.jonsson@sun.com-20081029224044-25b62zau0dk9jpux) (pib:5)
[10 Nov 2008 11:35] Bugs System 
Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081004224030-axbfltifujlxeaui) (version source revid:mattias.jonsson@sun.com-20081029203004-ve8mmm5i9cfo32rf) (pib:5)
[10 Dec 2008 15:32] Joe Grasse 
It doesn't look like this change actually made it into 5.1.30 is it actually going into 5.1.31?
[10 Dec 2008 15:54] Georgi Kodinov 
Correct : it's not in 5.1.30. Will be in 5.1.31. Moving back to documenting.
[10 Dec 2008 20:29] Paul DuBois 
Moved 5.1.x changelog entry from 5.1.30 to 5.1.31.
[19 Jan 2009 11:22] Bugs System 
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:00] Bugs System 
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:05] Bugs System 
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)