Fast index creation fails with DROP/ADD index

Bug #1089870 reported by Raghavendra D Prabhu
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Medium
Hrvoje Matijakovic
5.1
Fix Released
Medium
Hrvoje Matijakovic
5.5
Fix Released
Medium
Hrvoje Matijakovic

Bug Description

show create table sbtest1;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=30001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 30000 |
+----------+
1 row in set (0.51 sec)

mysql> alter table sbtest1 drop index k_1, add index k_1 (k);
Query OK, 30000 rows affected (1.38 sec)
Records: 30000 Duplicates: 0 Warnings: 0

However, following works fine:

mysql> alter table sbtest1 drop index k_1, add index k_2 (k);
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0

Tags: doc

Related branches

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

This is probably related to the fix for http://bugs.mysql.com/bug.php?id=54927, http://bugs.mysql.com/bug.php?id=49838 etc. Let me quote http://bugs.mysql.com/bug.php?id=51451:

"[8 Apr 2010 15:09] Vasil Dimov

I think this is irrelevant after the fix of Bug#49838 because now MySQL does copy the table if the same index is dropped and added in the same ALTER."

So this is probably an (un)documented limitation.

Revision history for this message
Raghavendra D Prabhu (raghavendra-prabhu) wrote :

I presume this limitation can be added to the documentation here -- http://www.percona.com/doc/percona-server/5.5/management/innodb_fast_index_creation.html

tags: added: doc
Changed in percona-server:
assignee: nobody → Hrvoje Matijakovic (hrvojem)
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1288

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.