inspectdb fails on tables with cross-schema relations on MySQL.

[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 (​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

[Mariusz Felisiak]

Thanks for the report. I haven't reproduce it but is seems to be valid.

[John Whitman]

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

[Mariusz Felisiak <felisiak.mariusz@…>]

In 68a8996b:

Fixed #34799 -- Made MySQL introspection skip cross-database relations.