MariaDB

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

Bug #775661 reported by Aurimas Mikalauskas on 2011-05-02

This bug affects 2 people

	Status	Importance	Assigned to
MariaDB	New	Undecided	Unassigned
MySQL Server	Fix Released	Undecided	Unassigned
Percona Server moved to https://jira.percona.com/projects/PS	Won't Fix	Low	Unassigned

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).

Revision history for this message

Alexey Kopytov (akopytov) wrote on 2011-05-11:

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.

Revision history for this message

Peter Zaitsev (pz-percona) wrote on 2011-05-11: Re: [Bug 775661] Re: Incorrect index name when dropping/adding index in the same statement

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: +1 888 401 3401 ext 501 Skype: peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

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

Revision history for this message

Alexey Kopytov (akopytov) wrote on 2011-05-12:

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

Revision history for this message

Peter Zaitsev (pz-percona) wrote on 2011-05-12:

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: +1 888 401 3401 ext 501 Skype: peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

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

Thanks Alex.

On Thu, May 12, 2011 at 2:20 AM, Alexey Kopytov
<alexey.kopytov@percona.com>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: +1 888 401 3401 ext 501   Skype:  peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

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

Stewart Smith (stewart) on 2011-05-19

Changed in percona-server:
status:	New → Confirmed
importance:	Undecided → Low

Revision history for this message

Alexey Kopytov (akopytov) wrote on 2011-05-25:

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.

Revision history for this message

Alexey Kopytov (akopytov) wrote on 2011-05-25:

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

Revision history for this message

Stewart Smith (stewart) wrote on 2011-05-25:

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)

Changed in mysql-server:
status:	New → Fix Released
Changed in percona-server:
status:	Confirmed → Won't Fix

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-25:

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

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

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