Incorrect index name when dropping/adding index in the same statement

[Aurimas Mikalauskas]

Not sure if this is a Percona Server or a MySQL bug, I'll report it here and let you decide. I've tested this both against 5.5.11-55 Percona Server (GPL), Release 20.2 and 5.1.56-rel12.7 (Percona Server (GPL), 12.7, Revision 224). I can't seem to be able to drop and create an index with the same name with a single statement:

CREATE TABLE `txtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sec` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sec` (`sec`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

ALTER TABLE txtest DROP KEY sec, ADD KEY (sec, id);
ERROR 1280 (42000): Incorrect index name 'sec'

mysql> ALTER TABLE txtest DROP KEY sec;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE txtest ADD KEY (sec, id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

Oh and interestingly enough if I explicitly specify the index name, it seems to work:

mysql> ALTER TABLE txtest DROP KEY sec, ADD KEY sec (sec, id);
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0

As a side note, I wonder why when I'm running two statements I get 0 rows affected both times while in the last example it says 12 rows affected (which is total amount of rows in that table).

[Alexey Kopytov (akopytov)]

That behavior is also present in the upstream server. I only checked 5.5.11, but I'm fairly sure it's in 5.1 as well.

[Peter Zaitsev (pz-percona)]

Alexey.

Strange. I remember using exact command as Aurimas showed all the time to
extend index lengths
and it worked fine in 5.1

Should we file MySQL but when ?

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Live MySQL Conference comes to NYC
http://www.percona.com/live/nyc-2011/

[Alexey Kopytov (akopytov)]

It turns out it does work with built-in InnoDB in MySQL 5.1, but fails with plugin in both 5.1 and 5.5 (and hence, in Percona Server 5.1 and 5.5).

I have reported http://bugs.mysql.com/bug.php?id=61146

[Peter Zaitsev (pz-percona)]

Thanks Alex.

On Thu, May 12, 2011 at 2:20 AM, Alexey Kopytov
<email address hidden>wrote:

> It turns out it does work with built-in InnoDB in MySQL 5.1, but fails
> with plugin in both 5.1 and 5.5 (and hence, in Percona Server 5.1 and
> 5.5).
>
> I have reported http://bugs.mysql.com/bug.php?id=61146
>
> ** Bug watch added: MySQL Bug System #61146
> http://bugs.mysql.com/bug.php?id=61146
>
> --
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
> https://bugs.launchpad.net/bugs/775661
>
> Title:
> Incorrect index name when dropping/adding index in the same statement
>
> Status in Percona Server with XtraDB:
> New
>
> Bug description:
> Not sure if this is a Percona Server or a MySQL bug, I'll report it
> here and let you decide. I've tested this both against 5.5.11-55
> Percona Server (GPL), Release 20.2 and 5.1.56-rel12.7 (Percona Server
> (GPL), 12.7, Revision 224). I can't seem to be able to drop and create
> an index with the same name with a single statement:
>
> CREATE TABLE `txtest` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `sec` int(11) DEFAULT NULL,
> PRIMARY KEY (`id`),
> KEY `sec` (`sec`)
> ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
>
> ALTER TABLE txtest DROP KEY sec, ADD KEY (sec, id);
> ERROR 1280 (42000): Incorrect index name 'sec'
>
> mysql> ALTER TABLE txtest DROP KEY sec;
> Query OK, 0 rows affected (0.00 sec)
> Records: 0 Duplicates: 0 Warnings: 0
>
> mysql> ALTER TABLE txtest ADD KEY (sec, id);
> Query OK, 0 rows affected (0.00 sec)
> Records: 0 Duplicates: 0 Warnings: 0
>
> Oh and interestingly enough if I explicitly specify the index name, it
> seems to work:
>
> mysql> ALTER TABLE txtest DROP KEY sec, ADD KEY sec (sec, id);
> Query OK, 12 rows affected (0.00 sec)
> Records: 12 Duplicates: 0 Warnings: 0
>
>
> As a side note, I wonder why when I'm running two statements I get 0 rows
> affected both times while in the last example it says 12 rows affected
> (which is total amount of rows in that table).
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Live MySQL Conference comes to NYC
http://www.percona.com/live/nyc-2011/

[Alexey Kopytov (akopytov)]

According to a comment in http://bugs.mysql.com/bug.php?id=61146, this is fixed in 5.6.3 upstream, the fix will not be backported to 5.1/5.5.

[Alexey Kopytov (akopytov)]

Is also a duplicate of http://bugs.mysql.com/bug.php?id=54927, patch with a fix: http://lists.mysql.com/commits/137700

[Stewart Smith (stewart)]

As fixed in upstream mysql and not too critical, I'll make as Won't Fix unless there is a pressing need for a backport of the bug fix (e.g. customer wants)

[Shahriyar Rzayev (rzayev-sehriyar)]

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