[MDEV-22775] [HY000][1553] Changing name of primary key column with foreign key constraint fails. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4.13, 10.4(EOL), 10.5
Fix Version/s: 10.4.19, 10.5.10
Component/s: Storage Engine - InnoDB
Labels:
- regression
Environment:
Debian 10

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;

Attachments

Issue Links

is duplicated by

MDEV-23950 Renaming a column that is used as a foreign key throws an error

Closed

Activity

Ascending order - Click to sort in descending order

Maarten Manders created issue - 2020-06-02 07:09

Maarten Manders made changes - 2020-06-02 07:09

Field	Original Value	New Value
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 {code:mysql} 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; {code}	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+ {code:mysql} 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; {code}

Alice Sherepa made changes - 2020-06-02 08:04

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2020-06-02 08:05

Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2020-06-02 08:05

Component/s

Storage Engine - InnoDB [ 10129 ]

Alice Sherepa made changes - 2020-06-02 08:24

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2020-06-02 08:24

Assignee

Marko Mäkelä [ marko ]

Maarten Manders added a comment - 2020-07-06 12:06

Any idea in what version this will be fixed?

Maarten Manders added a comment - 2020-07-06 12:06 Any idea in what version this will be fixed?

Anthony Rsl added a comment - 2020-09-04 14:37 - edited

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

Anthony Rsl added a comment - 2020-09-04 14:37 - edited 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

Rafał Głowacz added a comment - 2020-09-07 06:14 - edited

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;

Rafał Głowacz added a comment - 2020-09-07 06:14 - edited 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;

Alice Sherepa made changes - 2020-10-13 13:22

Link

This issue is duplicated by ~~MDEV-23950~~ [ ~~MDEV-23950~~ ]

Alice Sherepa made changes - 2020-10-13 13:22

Labels

regression

Alexander Karlstad added a comment - 2020-10-13 13:29

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.

Alexander Karlstad added a comment - 2020-10-13 13:29 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.

Alice Sherepa added a comment - 2020-10-13 14:19

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

Alice Sherepa added a comment - 2020-10-13 14:19 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

Alexander Karlstad added a comment - 2020-10-14 08:43

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

Alexander Karlstad added a comment - 2020-10-14 08:43 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

Nayuta Yanagisawa (Inactive) added a comment - 2021-01-03 16:58

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

Nayuta Yanagisawa (Inactive) added a comment - 2021-01-03 16:58 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

Sergei Golubchik made changes - 2021-01-28 12:26

Priority

Major [ 3 ]

Critical [ 2 ]

Marko Mäkelä added a comment - 2021-03-22 10:55

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?

Marko Mäkelä added a comment - 2021-03-22 10:55 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?

Alexander Barkov made changes - 2021-03-22 12:01

Assignee

Marko Mäkelä [ marko ]

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2021-03-24 14:05

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Alexander Barkov added a comment - 2021-03-24 14:06 - edited

sanja, can you please review?

The patch for 10.5

Branch:
https://github.com/MariaDB/server/tree/bb-10.5-bar-MDEV-22775
A direct link to the patch:
https://github.com/MariaDB/server/commit/ff1ee3fdd92ed5d91fa1641d35c2774fdd3fca13

The patch for 10.4:

Branch:
https://github.com/MariaDB/server/tree/bb-10.4-bar-MDEV-22775
A direct link to the patch:
https://github.com/MariaDB/server/commit/bbd216ec520c0c6ee160602062c6589fddd1d7c6

Alexander Barkov added a comment - 2021-03-24 14:06 - edited sanja , can you please review? The patch for 10.5 Branch: https://github.com/MariaDB/server/tree/bb-10.5-bar-MDEV-22775 A direct link to the patch: https://github.com/MariaDB/server/commit/ff1ee3fdd92ed5d91fa1641d35c2774fdd3fca13 The patch for 10.4: Branch: https://github.com/MariaDB/server/tree/bb-10.4-bar-MDEV-22775 A direct link to the patch: https://github.com/MariaDB/server/commit/bbd216ec520c0c6ee160602062c6589fddd1d7c6

Alexander Barkov made changes - 2021-03-24 14:06

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Alexander Barkov made changes - 2021-03-24 14:06

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2021-03-24 14:07

Assignee	Alexander Barkov [ bar ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2021-04-06 11:21

OK to push

Oleksandr Byelkin added a comment - 2021-04-06 11:21 OK to push

Oleksandr Byelkin made changes - 2021-04-06 11:21

Assignee	Oleksandr Byelkin [ sanja ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2021-04-07 17:46

issue.field.resolutiondate

2021-04-07 17:46:15.0

2021-04-07 17:46:15.366

Alexander Barkov made changes - 2021-04-07 17:46

Fix Version/s		10.4.19 [ 25205 ]
Fix Version/s		10.5.10 [ 25204 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:51

Workflow

MariaDB v3 [ 109390 ]

MariaDB v4 [ 157892 ]

People

Assignee:: Alexander Barkov

Reporter:: Maarten Manders

Votes:: 6 Vote for this issue

Watchers:: 13 Start watching this issue

Dates

Created:: 2020-06-02 07:09

Updated:: 2021-04-08 07:28

Resolved:: 2021-04-07 17:46

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

The patch for 10.5

The patch for 10.4:

People

Dates

Git Integration