[Django] #34799: Django 4.1 broke inspectdb on MySQL tables with cross-schema relations

[Django]

#34799: Django 4.1 broke inspectdb on MySQL tables with cross-schema relations
--------------------------------------------+---------------------------
Reporter: johnjameswhitman | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 4.1
Severity: Normal | Keywords: inspectdb
Triage Stage: Unreviewed | Has patch: 1
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------------+---------------------------
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 ([https://docs.djangoproject.com/en/4.2/topics/db/multi-db
/#cross-database-relations 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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34799>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.