Error when I change name of field that has primary key

Hi, I've noticed recently when I change the name of a field that has a primary key index I get this error:

I click OK then and try to view a different table. I am asked if i want to save the changes and I click NO. Then when I refresh the database table list in the left hand panel and view the table settings, I find the field name HAS been changed.

I am using MariaDB 10.11.14 and version 12.21.0.7344 of HeidiSQL.

Thanks Steve

The error message tells me you did not only rename a column, but also changed the visibility of the primary key, on the "Indexes" tab. Pressing the "Save" button then fired two ALTER TABLE queries, and the first one with the column rename succeeds. But the second one failed, which tried to alter the index:

You can examine the queries which HeidiSQL will fire in the "ALTER code" tab.

Hi Angsar. Thanks for the quick response.

I haven't changed the Visibility status (knowingly). I simply change the field name. I have never used IGNORE on indexes and haven't seen the Visibility column in Indexes until today (ironically it seems its default state is not to be visible).

When I view the indexes tab before attempting the change to the fieldname the index on my ID is Primary, BTREE, NOT IGNORED.

These are the alter queries I see in the log when I click save, having changed the fieldname.

[09:22:24.043] ALTER TABLE bootstrap CHANGE COLUMN ID NewID BIGINT(20) NOT NULL AUTO_INCREMENT FIRST, DROP PRIMARY KEY, ADD PRIMARY KEY (NewID) USING BTREE;

/* [09:22:24.120] Info: Records: 0 Duplicates: 0 Warnings: 0 /

[09:22:24.183] ALTER TABLE bootstrap ALTER INDEX PRIMARY NOT IGNORED;

/* [09:22:24.231] SQL Error (4174): A primary key cannot be marked as IGNORE /

Screenshot:

I wonder if this is a MariaDB issue as it seems to be disregarding the NOT in the alter index query.

I'll have a dig around MariaDB forums etc.

Thanks again. Steve

Thanks for the update. That's indeed a bit strange, and it could indeed be some issue on the MariaDB side.

After looking up: MariaDB does document the underlying restriction - primary keys cannot be ignored - but it does not appear to clearly spell out that an ALTER INDEX PRIMARY NOT IGNORED statement is rejected too. So, I will fix that in HeidiSQL, by suppressing that ALTER query if on a PK.

Perfect. Thanks!

Steve