Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.5.4, 10.5.5, 10.5.6
-
None
-
Docker, mariadb/latest
Description
Renaming a column (usually the primary key) that is used as a foreign key in another table with ALTER TABLE syntax (not the new RENAME COLUMN syntax from 10.5.2) causes a Cannot drop index 'PRIMARY': needed in a foreign key constraint error.
For example, say you have these two tables:
create table `test` ( |
`testId` int unsigned not null auto_increment primary key, |
`name` varchar(255) not null |
);
|
|
|
create table `test_ref` ( |
`id` int unsigned not null auto_increment primary key, |
`refId` int unsigned not null, |
`name` varchar(255) not null |
);
|
And you add a foreign key constraint:
alter table `test_ref` |
add constraint `test_ref_id` |
foreign key (`refId`) |
references `test` (`testId`); |
And then try to rename test.testId to test.id with this syntax:
ALTER TABLE test |
CHANGE testId id INT UNSIGNED AUTO_INCREMENT NOT NULL; |
You will get the error mentioned.
Turning off foreign key checks before and after this alter statement will make it work though, and from what I can tell the references in information_schema.KEY_COLUMN_USAGE is correctly updated, but it does not feel right.
Also, if using the more recent RENAME COLUMN syntax, the change goes through without any errors. The problem is that this syntax is fairly new and likely not implemented in a lot of libraries yet, like doctrine/dbal for instance.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
it looks like the same problem as
MDEV-22775. please provide the output of SHOW variables LIKE 'collation%';
I agree, it does seem like the same problem.
My output:
MariaDB [(none)]> show variables like 'collation%';
|
+----------------------+--------------------+
|
| Variable_name | Value |
|
+----------------------+--------------------+
|
| collation_connection | utf8mb4_general_ci |
|
| collation_database | utf8mb4_general_ci |
|
| collation_server | utf8mb4_general_ci |
|
+----------------------+--------------------+
|
I have attached the output of
SHOW VARIABLES
|
in its entirety to this issue now by the way.
it looks like the same problem as
MDEV-22775. please provide the output of SHOW variables LIKE 'collation%';