Opened 8 years ago

Last modified 5 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


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:

  `ACID` int(8) unsigned NOT NULL,
  `SITE` varchar(8) character set latin1 default NULL,

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

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

So it tries to use as the table to introspect instead of the appropriate Notice it not only needs to use the schema as part of the table name, but also not quote the '.'. Also, 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 8 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 Changed 8 years ago by brockweaver

Has patch: set
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Status: newassigned
Triage Stage: UnreviewedReady for checkin

Posted patch.

comment:2 Changed 8 years ago by Malcolm Tredinnick

Triage Stage: Ready for checkinAccepted

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 8 years ago by brockweaver

Attachment: support_other_schema.diff added

comment:3 Changed 8 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 8 years ago by crippledcanary@…

Related to #6148

comment:5 Changed 6 years ago by Luke Plant

Severity: Normal
Type: Bug

comment:6 Changed 5 years ago by Julien Phalip

Easy pickings: unset
Needs tests: set

comment:7 Changed 5 years ago by Ramiro Morales

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