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

    XMLWordPrintable

Details

    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.