Opened 7 years ago

Last modified 4 years ago

#7556 assigned Bug

inspectdb fails in MySql if a table references a table outside the current schema

Reported by: brockweaver Owned by: brockweaver
Component: Core (Management commands) Version: master
Severity: Normal Keywords: mysql foreign key schema inspectdb
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

If a table contains a foreign key that refers to another table that sits outside the schema, that index includes a '.' in the table name. Suppose our current schema is schema1:

CREATE TABLE `T1` (
  `ACID` int(8) unsigned NOT NULL,
  `SITE` varchar(8) character set latin1 default NULL,
  CONSTRAINT `FK_T1_SITE` FOREIGN KEY (`SITE`) REFERENCES `schema2`.`site` (`SITE`)
)

For this situation, inspectdb will return something similar to this:

_mysql_exceptions.ProgrammingError: (1146, "Table 'schema1.site' doesn't exist")

So it tries to use schema1.site as the table to introspect instead of the appropriate schema2.site Notice it not only needs to use the schema as part of the table name, but also not quote the '.'. Also, introspection.py was not pulling the schema name and just assuming the current schema, which for most cases is right, but in this case causes issues for legacy databases.

Attachments (1)

support_other_schema.diff (5.0 KB) - added by brockweaver 7 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 Changed 7 years ago by brockweaver

  • Has patch set
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Status changed from new to assigned
  • Triage Stage changed from Unreviewed to Ready for checkin

Posted patch.

comment:2 Changed 7 years ago by mtredinnick

  • Triage Stage changed from Ready for checkin to Accepted

Do your changes to the introspection code work with MySQL 4.x (i.e. did the schema columns exist in that version)?

We don't really have schema support in Django at the moment and it's not immediately clear whether adding some kind of rough workaround at the moment is a good idea, or to implement a more holistic approach. I'm going to punt this back to accepted for the time being (it's not a good idea to triage your own ticket to "ready for checkin" in any case) whilst we consider the right approach.

Changed 7 years ago by brockweaver

comment:3 Changed 7 years ago by brockweaver

I attached a new patch that has been tested with both MySQL 4.1 and 5.0. I agree this is a rough workaround, but it is one that was holding me up, so I figured I'd submit the workaround until a better approach had been implemented. Sorry for the etiquette snafu on ready for checkin -- I didn't know what was appropriate.

I'm still getting my feet wet with django -- and already I can tell it is a great code base and a great community! Thank you to everybody involved.

comment:4 Changed 7 years ago by crippledcanary@…

Related to #6148

comment:5 Changed 4 years ago by lukeplant

  • Severity set to Normal
  • Type set to Bug

comment:6 Changed 4 years ago by julien

  • Easy pickings unset
  • Needs tests set

comment:7 Changed 4 years ago by ramiro

  • Component changed from Database layer (models, ORM) to Core (Management commands)
  • Keywords inspectdb added
  • UI/UX unset
Note: See TracTickets for help on using tickets.
Back to Top