Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.19
-
Fix Version/s: 10.0.21
-
Component/s: Data Definition - Alter Table
-
Labels:
-
Sprint:10.0.20
Description
in resulting table there is no primary key:
the warning is also in error as primary keys shouldn't have names.
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`)); Query OK, 0 rows affected (0.02 sec) alter table b DROP KEY IF EXISTS transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id); Query OK, 0 rows affected, 1 warning (0.00 sec) Records: 0 Duplicates: 0 Warnings: 1 show warnings; +-------+------+-------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------+ | Note | 1061 | Duplicate key name 'transaction_id' | +-------+------+-------------------------------------+ 1 row in set (0.00 sec) show create table b; +-------+-----------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `transaction_id` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------+
If the original key was called something other than the same as the column name it works
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `t` (`transaction_id`)); Query OK, 0 rows affected (0.02 sec) alter table b DROP KEY IF EXISTS t, ADD PRIMARY KEY IF NOT EXISTS (transaction_id); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 show create table b; +-------+---------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `transaction_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`transaction_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
It is the IF NOT EXISTS on PRIMARY KEY that is the critical aspect.
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`)); Query OK, 0 rows affected (0.03 sec) alter table b DROP KEY transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id); Query OK, 0 rows affected, 1 warning (0.00 sec) Records: 0 Duplicates: 0 Warnings: 1 show warnings; +-------+------+-------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------+ | Note | 1061 | Duplicate key name 'transaction_id' | +-------+------+-------------------------------------+ 1 row in set (0.01 sec) show create table b; +-------+-----------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `transaction_id` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------+
Without IF NOT EXISTS it works fine.
CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`)); Query OK, 0 rows affected (0.00 sec) alter table b DROP KEY transaction_id, ADD PRIMARY KEY (transaction_id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 show create table b; +-------+---------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `transaction_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`transaction_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-8351 alter table - failing to ADD KEY IF NOT EXISTS when existing dropping the same index name earlier in the alter table
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Alexey Botchkov thanks for the PK fix. It doesn't look like the second test in the first comment is corrected by this patch. I did look at the code and it seems the way the loops are set up that maybe this should be a separate bug as its a much more complicated fix.
> CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`));
> alter table b DROP KEY transaction_id, ADD UNIQUE KEY IF NOT EXISTS (transaction_id);
update: did a code look
That's the different issue indeed.
The server automatically assigns the 'transaction_id' to be the name for that UNIQUE KEY (transaction_id),
so we have in fact ALTER TABLE DROP A, ADD A IF NOT EXISTS;
And that statement will not create the A if it exists BEFORE the query.
So i'm not sure now if it's a bug at all.
same applies with other keys when creating a new key and dropping an existing one.