id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34799	inspectdb fails on tables with cross-schema relations on MySQL.	John Whitman	John Whitman	"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
}}}
"	Bug	closed	Database layer (models, ORM)	4.1	Normal	fixed	inspectdb	mysql	Ready for checkin	1	0	0	0	0	0