#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 Mariusz Felisiak, 15 months ago

Cc: mysql added
Component: UncategorizedDatabase layer (models, ORM)
Has patch: unset
Summary: Django 4.1 broke inspectdb on MySQL tables with cross-schema relationsinspectdb fails on tables with cross-schema relations on MySQL.
Type: UncategorizedBug

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

comment:2 by Mariusz Felisiak, 15 months ago

Triage Stage: UnreviewedAccepted

comment:3 by John Whitman, 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 John Whitman, 14 months ago

Has patch: set

comment:5 by Mariusz Felisiak, 14 months ago

Needs tests: set
Owner: changed from nobody to John Whitman
Patch needs improvement: set
Status: newassigned

comment:6 by Mariusz Felisiak, 14 months ago

Needs tests: unset
Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 14 months ago

Resolution: fixed
Status: assignedclosed

In 68a8996b:

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

Note: See TracTickets for help on using tickets.
Back to Top