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
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 bugs.mysql. com/bug. php?id= 61146 bugs.mysql. com/bug. php?id= 61146 /bugs.launchpad .net/bugs/ 775661
> with plugin in both 5.1 and 5.5 (and hence, in Percona Server 5.1 and
> 5.5).
>
> I have reported http://
>
> ** Bug watch added: MySQL Bug System #61146
> http://
>
> --
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
> https:/
>
> 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 www.percona. com/live/ nyc-2011/
http://