Details
Minor
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).
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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/
Re: Incorrect index name when dropping/adding index in the same statement
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
Re: [Bug 775661] Re: Incorrect index name when dropping/adding index in the same statement
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/
Re: Incorrect index name when dropping/adding index in the same statement
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.
Re: Incorrect index name when dropping/adding index in the same statement
Is also a duplicate of http://bugs.mysql.com/bug.php?id=54927, patch with a fix: http://lists.mysql.com/commits/137700
Re: Incorrect index name when dropping/adding index in the same statement
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)
Launchpad bug id: 775661
The upstream bug http://bugs.mysql.com/bug.php?id=54927 was fixed in 5.6.3, so the fix should be merged into 10.x when the time comes.
The fix wasn't backported into former versions of MySQL.
Re: Incorrect index name when dropping/adding index in the same statement
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.