Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.19
-
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 |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
Activity
holyfoot 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.
> CREATE TABLE b (`transaction_id` int(11) NOT NULL DEFAULT '0', KEY `transaction_id` (`transaction_id`));Query OK, 0 rows affected (0.01 sec)> alter table b DROP KEY transaction_id, ADD UNIQUE KEY IF NOT EXISTS (transaction_id);Query OK, 0 rows affected, 1 warning (0.00 sec)Records: 0 Duplicates: 0 Warnings: 1> show create table b;+-------+-----------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-----------------------------------------------------------------------------------------------------------+| b | CREATE TABLE `b` (`transaction_id` int(11) NOT NULL DEFAULT '0') ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-----------------------------------------------------------------------------------------------------------+