Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL)
-
None
-
Docker container using the official mariadb:10.3.2 image
Description
CREATE DATABASE tmp_1;
|
use tmp_1;
|
|
CREATE TABLE users
|
(
|
u_id INT NOT NULL,
|
UNIQUE INDEX UNIQ_1483A5E967CB5338 (u_id),
|
PRIMARY KEY (u_id)
|
) ENGINE = InnoDB;
|
|
CREATE TABLE contracts
|
(
|
id INT AUTO_INCREMENT NOT NULL,
|
user_id INT NOT NULL,
|
INDEX IDX_950A973BE43E81 (user_id),
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB;
|
|
ALTER TABLE contracts ADD CONSTRAINT FK_950A973BE43E81 FOREIGN KEY (user_id) REFERENCES users (u_id);
|
|
ALTER TABLE contracts ADD status ENUM ('a', 'b', 'c'); |
|
ALTER TABLE users
|
CHANGE u_id id INT NOT NULL,
|
ADD name VARCHAR(255) DEFAULT NULL; |
|
ALTER TABLE users CHANGE id u_id INT NOT NULL;
|
|
ALTER TABLE contracts CHANGE status status VARCHAR(20) DEFAULT NULL; |
Expected outcome:
Column status in contracts table is changed to type VARCHAR(20) successfully.
Actual outcome:
The queries (the last line) fail with this error:
ERROR 1025 (HY000): Error on rename of './tmp_1/#sql-1_8' to './tmp_1/contracts' (errno: 150 "Foreign key constraint is incorrectly formed") |
After the error occurs, the table contracts does not exist anymore (in the filesystem it is replaced with a file #sql-1_8).
Reproduction and details:
This seems to be a very specific bug which I cannot reproduce in any other way. Some points that I've noticed while trying to reproduce this:
- This is reproducable on all version from mariadb:10.3.2 and upwards (probably introduced with https://mariadb.com/kb/en/library/instant-add-column-for-innodb/)
- The last line must be an ALTER TABLE statement which changes a columns type from enum to varchar or vice versa, no other types work as far as I know.
- Before changing a column type which triggers the error, there must be a new column added to the table which is the owning side of a foreign key in a single SQL query. i.e. if we changed
ALTER TABLE users CHANGE u_id id INT NOT NULL, ADD name VARCHAR(255) DEFAULT NULL;
to
ALTER TABLE users CHANGE u_id id INT NOT NULL;
ALTER TABLE users ADD name VARCHAR(255) DEFAULT NULL;
There would not be an error anymore.
Attachments
Issue Links
- duplicates
-
MDEV-19641 ALTER TABLE ... ADD COLUMN damages foreign keys which are pointed to the table being altered
- Closed
- is part of
-
MDEV-20938 Double free of dict_foreign_t during instant ALTER TABLE
- Closed