0

I'm trying to alter a previously created table on MariaDB 10.4.21 and I'm getting the following error:

Import failed with: 'Incorrect index name 'pk_i_id''

The table:

CREATE TABLE IF NOT EXISTS /*TABLE_PREFIX*/t_mmessenger_recipients (
    message_id INT(10) UNSIGNED NOT NULL,
    recipient_id INT(10) UNSIGNED NOT NULL,
    readOn DATETIME,
    hidden BOOL DEFAULT FALSE,
    CONSTRAINT `fk_/*TABLE_PREFIX*/t_mmessenger_recipients_message` FOREIGN KEY(`message_id`) REFERENCES /*TABLE_PREFIX*/t_mmessenger_message(`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (`message_id`, `recipient_id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET 'UTF8' COLLATE 'UTF8_GENERAL_CI';

The alter that generates the error (line 4):

ALTER TABLE /*TABLE_PREFIX*/t_mmessenger_recipients ADD `pk_i_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE FIRST;
ALTER TABLE /*TABLE_PREFIX*/t_mmessenger_recipients DROP FOREIGN KEY `fk_/*TABLE_PREFIX*/t_mmessenger_recipients_message`;
ALTER TABLE /*TABLE_PREFIX*/t_mmessenger_recipients DROP PRIMARY KEY;
ALTER TABLE /*TABLE_PREFIX*/t_mmessenger_recipients ADD PRIMARY KEY (`pk_i_id`);
ALTER TABLE /*TABLE_PREFIX*/t_mmessenger_recipients ADD CONSTRAINT `fk_/*TABLE_PREFIX*/t_mmessenger_recipients_message`
    FOREIGN KEY(`message_id`)
    REFERENCES /*TABLE_PREFIX*/t_mmessenger_message(`id`) ON UPDATE CASCADE ON DELETE CASCADE;

This seems to happen only on MariaDB, as on MySQL 5.7 it worked fine.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Turbo
  • 7
  • 6
  • 1
    There's no column named `pk_i_id`. The parentheses are supposed to contain a list of columns in the key, not the name of the key. – Barmar Nov 23 '21 at 21:49
  • Which columns should be in the new primary key? – Barmar Nov 23 '21 at 21:50
  • The `ADD` should be getting an error because `AUTO_INCREMENT` has to be the primary key, so you need to remove the old primary key first. – Barmar Nov 23 '21 at 22:20
  • So should I run the `ALTER TABLE /*TABLE_PREFIX*/t_mmessenger_recipients DROP PRIMARY KEY;` before the ADD `pk_i_id` column? Because doing this returns another error. And why does it act up only on MariaDB 10.4 and not on MySQL 5.7? – Turbo Nov 23 '21 at 22:28
  • I was wrong, it's not required to be the primary key. – Barmar Nov 23 '21 at 23:49
  • Sadly it's as I thought, a MariaDB 10.4 bug: https://jira.mariadb.org/plugins/servlet/mobile#issue/MDEV-19598 – Turbo Nov 24 '21 at 00:14

0 Answers0