[MDEV-22775] [HY000][1553] Changing name of primary key column with foreign key constraint fails. Created: 2020-06-02  Updated: 2021-04-08  Resolved: 2021-04-07

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.4.13, 10.4, 10.5
Fix Version/s: 10.4.19, 10.5.10

Type: Bug Priority: Critical
Reporter: Maarten Manders Assignee: Alexander Barkov
Resolution: Fixed Votes: 6
Labels: regression
Environment:

Debian 10

Issue Links:
Duplicate
is duplicated by MDEV-23950 Renaming a column that is used as a f... Closed

 Description   

Changing the name of a primary key column with a foreign key constraint fails with the following error since 10.4.13:

[HY000][1553] (conn=17) Cannot drop index 'PRIMARY': needed in a foreign key constraint

create schema test collate utf8_unicode_ci;
 
create table test.input_type
 
(
 
    input_type_id int(11) unsigned auto_increment
 
        primary key
 
);
 
 
 
create table test.input
 
(
 
    input_id int unsigned auto_increment
 
        primary key,
 
    input_type_id int unsigned not null,
 
    constraint ibfk
 
        foreign key (input_type_id) references test.input_type (input_type_id)
 
);
 
 
 
create index type_id
 
    on test.input (input_type_id);
 
 
 
alter table test.input_type change input_type_id id int(11) unsigned auto_increment;



 Comments   
Comment by Maarten Manders [ 2020-07-06 ]

Any idea in what version this will be fixed?

Comment by Anthony Rsl [ 2020-09-04 ]

Hello,

We are also impacted by this issue since we migrated our MariaDB database from 10.4.12 to 10.4.14.

Here is an example and minimal SQL script to reproduce our issue with a UNIQUE KEY (columns have been anonymised):

CREATE TABLE `t2` (
 
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
 
  PRIMARY KEY (`id`)
 
);
 
 
 
CREATE TABLE `t1` (
 
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `parent_id` bigint(20) NOT NULL,
 
  `number` int(11) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `parent_id` (`parent_id`,`number`),
 
  CONSTRAINT `fk_t1_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `t2` (`id`) ON DELETE CASCADE
 
);
 
 
 
ALTER TABLE t1 CHANGE parent_id parent_id_renamed BIGINT;

With MariaDB 10.5.1 and 10.4.12, the ALTER TABLE t CHANGE statement was correctly executed:

> ALTER TABLE t1 CHANGE parent_id parent_id_renamed BIGINT;
 
Query OK, 0 rows affected (0.046 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

And as a result, the parent_id column was renamed to parent_id_renamed.

Since MariaDB 10.5.2 and 10.4.12, we have now the following error during the ALTER TABLE t CHANGE statement:

ERROR 1553 (HY000): Cannot drop index 'parent_id': needed in a foreign key constraint

Comment by Rafał Głowacz [ 2020-09-07 ]

As a workaround you can temporarily disable foreign key checks:

SET FOREIGN_KEY_CHECKS=0;
 
 
 
ALTER TABLE t1 CHANGE parent_id parent_id_renamed BIGINT;
 
 
 
SET FOREIGN_KEY_CHECKS=1;

Comment by Alexander Karlstad [ 2020-10-13 ]

There also seems to be no problem using the new 

ALTER TABLE t1 RENAME COLUMN parent_id parent_id_renamed

command though, if you're just renaming it and not changing anything else.

Comment by Alice Sherepa [ 2020-10-13 ]

repeatable on 10.4, 10.5 with utf8:

--source include/have_innodb.inc
 
 
 
create table t1 (id int primary key)
 
engine=innodb default charset=utf8;
 
 
 
create table t2 (input_id int primary key, id int not null,
 
	key a (id),
 
    constraint a foreign key (id) references t1  (id)
 
)engine=innodb default charset=utf8;
 
 
--error 1553
 
alter table t1  change id id2 int;
 
alter table t1 default charset=latin1;
 
alter table t1  change id id2 int;


alter table t1  change id id2 int;
 
ERROR HY000: Cannot drop index 'PRIMARY': needed in a foreign key constraint
 
alter table t1 default charset=latin1;
 
alter table t1  change id id2 int;
 
show create table t1;
 
Table	Create Table
 
t1	CREATE TABLE `t1` (
 
  `id2` int(11) NOT NULL,
 
  PRIMARY KEY (`id2`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Comment by Alexander Karlstad [ 2020-10-14 ]

Are there, by the way, any known bug reports over at MySQL for the same problems? I tested and had the same issue in MySQL 8, but not in 5.7, so I suspect they have the same issue as well

Comment by Nayuta Yanagisawa (Inactive) [ 2021-01-03 ]

By running git-bisect, I founded that the bug is introduced by df07e00a810890f6f6eb1334c76ee22133750777.

The following diff is probably the point. old_part.length and new_part.length are equal but old_cs->mbmaxlen and new_cs->mbmaxlen are not in the Alice's test case.

-  if (old_part.length != new_part.length)
 
+  if (old_part.length / old_cs->mbmaxlen != new_part.length / new_cs->mbmaxlen)


(gdb) p old_part.length
 
$1 = 4
 
(gdb) p new_part.length
 
$2 = 4
 
(gdb) p old_cs->mbmaxlen
 
$3 = 1
 
(gdb) p  new_cs->mbmaxlen
 
$4 = 3

Comment by Marko Mäkelä [ 2021-03-22 ]

nayuta-yanagisawa, thank you very much. It looks like the CHARACTER SET is wrongly affecting the check of the INT column.

bar, can you suggest how we could refine that code to determine the proper length of the column? The intention of the change was to allow an instant conversion from utf8mb3 to utf8mb4. Character sets are not applicable to columns that do not store character data, yet the information appears to be stored for them. How can we detect that old_part and new_part do not store character data, and thus we should ignore any change of character set?

Comment by Alexander Barkov [ 2021-03-24 ]

sanja, can you please review?

The patch for 10.5

The patch for 10.4:

Comment by Oleksandr Byelkin [ 2021-04-06 ]

OK to push

Generated at Thu Feb 08 09:17:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.