Opened 15 months ago
Closed 14 months ago
#34799 closed Bug (fixed)
inspectdb fails on tables with cross-schema relations on MySQL.
Reported by: | John Whitman | Owned by: | John Whitman |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.1 |
Severity: | Normal | Keywords: | inspectdb |
Cc: | mysql | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
We generate models for a legacy MySQL database whose tables include cross-schema foreign keys (the MySQL instance uses schemas to namespace tables, but they all belong to the same system).
A change to the inspectdb command that was released in Django 4.1 broke generating models for these tables: https://github.com/django/django/commit/aaf9b558583d9bb75a0c9d53b135dc8c1b75b6a2#diff-e4bb601306bc4dba91357e4235d394d008c9e582f5b4d146b9c93c80924a75d4R124
Specifically, the change was meant to address an issue when foreign-keys referenced a non-primary-key on the other side. The fix involved looking up metadata on the foreign relation, which assumes the foreign table is in the same schema as the source table. When the relation points to a cross-schema table, it fails to look up the table.
It seems like the Django project has chosen not to support relationships across schemas (Cross-Database Relations docs), so it would be nice if inspectdb would ignore them. I was able to whip up a quick patch locally that does just that. I would be happy to turn this into a PR after adding tests if necessary.
There are two spots to fix this. The first is necessary, and prevents the cross-schema relation from being added to the model's relations:
https://github.com/django/django/blob/39c9aa1d85c678451b867004c7d797a6f9313224/django/db/backends/mysql/introspection.py#L177-L187
diff --git a/django/db/backends/mysql/introspection.py b/django/db/backends/mysql/introspection.py index 9f57cd599a..3441d6df0c 100644 --- a/django/db/backends/mysql/introspection.py +++ b/django/db/backends/mysql/introspection.py @@ -180,6 +180,7 @@ class DatabaseIntrospection(BaseDatabaseIntrospection): FROM information_schema.key_column_usage WHERE table_name = %s AND table_schema = DATABASE() + AND referenced_table_schema = DATABASE() AND referenced_table_name IS NOT NULL AND referenced_column_name IS NOT NULL """,
The second prevents the foreign key from being added to constraints. If you omit this patch it, inspectdb will still produce the model but it seems cleaner to ignore.
https://github.com/django/django/blob/39c9aa1d85c678451b867004c7d797a6f9313224/django/db/backends/mysql/introspection.py#L233-L247
@@ -239,6 +240,10 @@ class DatabaseIntrospection(BaseDatabaseIntrospection): information_schema.table_constraints AS c WHERE kc.table_schema = DATABASE() AND + ( + kc.referenced_table_schema = DATABASE() OR + kc.referenced_table_schema IS NULL + ) AND c.table_schema = kc.table_schema AND c.constraint_name = kc.constraint_name AND c.constraint_type != 'CHECK' AND
Change History (7)
comment:1 by , 15 months ago
Cc: | added |
---|---|
Component: | Uncategorized → Database layer (models, ORM) |
Has patch: | unset |
Summary: | Django 4.1 broke inspectdb on MySQL tables with cross-schema relations → inspectdb fails on tables with cross-schema relations on MySQL. |
Type: | Uncategorized → Bug |
comment:2 by , 15 months ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:3 by , 14 months ago
Created a PR with a test that exercises the issue if the DB connection is MySQL. One quick note: the test assumes the connection's user can CREATE TABLE on both the "default" and "other" databases (unsure if that's a safe assumption outside of the settings I linked in the PR).
https://github.com/django/django/pull/17205
comment:4 by , 14 months ago
Has patch: | set |
---|
comment:5 by , 14 months ago
Needs tests: | set |
---|---|
Owner: | changed from | to
Patch needs improvement: | set |
Status: | new → assigned |
comment:6 by , 14 months ago
Needs tests: | unset |
---|---|
Patch needs improvement: | unset |
Triage Stage: | Accepted → Ready for checkin |
Thanks for the report. I haven't reproduce it but is seems to be valid.