matrixorigin / matrixorigin/matrixone
[Bug]: ALTER TABLE 同时 ADD COLUMN 和 ADD FOREIGN KEY 在 copy mode 下失败
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Summary
MatrixOne 在一条 `ALTER TABLE` 中同时执行 `ADD COLUMN` 和 `ADD CONSTRAINT ... FOREIGN KEY` 时进入 copy mode,但 copy mode 不支持添加外键约束,导致合法的 MySQL/Django migration 失败。
该问题已在 MatrixOne 最新 `main` 提交上通过 Docker 独立实例复现。
## Environment
- Repository: `matrixorigin/matrixone`
- Branch: `main`
- Commit: `8a4c84f4516d5098de9eef50b8afecc7524f37a2`
- MatrixOne version: `8.0.30-MatrixOne-v1.3.0`
- Runtime: local Docker image built from the commit above
- MySQL client: `mysql:8.4.10`
- Endpoint: `127.0.0.1:26002`
The running server was verified with:
```sql
SELECT git_version(), version();
```
Result:
```text
8a4c84f451 8.0.30-MatrixOne-v1.3.0
```
## Reproduction
```sql
DROP DATABASE IF EXISTS verify_fk;
CREATE DATABASE verify_fk;
USE verify_fk;
CREATE TABLE wagtailcore_pagerevision (
id INTEGER NOT NULL PRIMARY KEY
);
CREATE TABLE wagtailcore_page (
id INTEGER NOT NULL PRIMARY KEY
);
ALTER TABLE wagtailcore_page
ADD COLUMN live_revision_id INTEGER NULL,
ADD CONSTRAINT wagtailcore_page_live_revision_id_930bd822_fk_wagtailco
FOREIGN KEY (live_revision_id)
REFERENCES wagtailcore_pagerevision(id);
```
The statement fails with:
```text
ERROR 20301 (HY000): invalid input: unsupported alter option in copy mode: add constraint wagtailcore_page_live_revision_id_930bd822_fk_wagtailco foreign key fk_0 (live_revision_id) references wagtailcore_pagerevision(id)
```
The same pattern from django-cms also fails:
```sql
CREATE TABLE django_content_type (
id INTEGER NOT NULL PRIMARY KEY
);
CREATE TABLE cms_placeholder (
id INTEGER NOT NULL PRIMARY KEY
);
ALTER TABLE cms_placeholder
ADD COLUMN content_type_id INTEGER NULL,
ADD CONSTRAINT cms_placeholder_content_type_id_a7659d9c_fk_django_co
FOREIGN KEY (content_type_id)
REFERENCES django_content_type(id);
```
It returns the same 20301 copy-mode error.
## Control
The equivalent operations succeed when split into two statements:
```sql
ALTER TABLE wagtailcore_page
ADD COLUMN live_revision_id INTEGER NULL;
ALTER TABLE wagtailcore_page
ADD CONSTRAINT wagtailcore_page_live_revision_id_930bd822_fk_wagtailco
FOREIGN KEY (live_revision_id)
REFERENCES wagtailcore_pagerevision(id);
SHOW CREATE TABLE wagtailcore_page;
```
The resulting table contains the foreign-key constraint. This indicates that foreign-key creation itself works; the failure is specific to combining it with `ADD COLUMN` in one `ALTER TABLE` statement.
## Expected behavior
MatrixOne should accept the combined `ALTER TABLE` form through the MySQL protocol, as used by Wagtail, django-cms, and Django migrations, and apply both the column and foreign-key constraint atomically.
At minimum, the statement should not be rejected as an unsupported copy-mode operation when the same operations are supported separately.
## Impact
Applications using Django migrations cannot complete schema initialization or upgrades when their migration compiler emits this combined ALTER form. Wagtail and django-cms migrations fail during startup, preventing the application from becoming usable with MatrixOne.
## Workaround
Split the operation into separate statements: add the column first, then add the foreign-key constraint.
## Suspected area
The error indicates that the ALTER planner selects copy mode for the combined operation and then rejects `ADD CONSTRAINT ... FOREIGN KEY` as an unsupported copy-mode option. The ALTER planning/execution path should either support both operations in copy mode or route this combination through a supported execution path.
Contributor guide
Assessment
This issue has not been assessed yet.