Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8212

alter table - failing to ADD PRIMARY KEY IF NOT EXISTS when existing index of same as column name

    Details

    • 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 |
      +-------+---------------------------------------------------------------------------------------------------------------------------------------------+

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                holyfoot Alexey Botchkov
                Reporter:
                danblack Daniel Black
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: