Opened 16 years ago

Last modified 2 years ago

#7556 new Bug

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

Reported by: brockweaver Owned by:
Component: Core (Management commands) Version: dev
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 16 years ago.

Download all attachments as: .zip

Change History (9)

comment:1 by brockweaver, 16 years ago

Has patch: set
Status: newassigned
Triage Stage: UnreviewedReady for checkin

Posted patch.

comment:2 by Malcolm Tredinnick, 16 years ago

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.

by brockweaver, 16 years ago

Attachment: support_other_schema.diff added

comment:3 by brockweaver, 16 years ago

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 by crippledcanary@…, 16 years ago

Related to #6148

comment:5 by Luke Plant, 13 years ago

Severity: Normal
Type: Bug

comment:6 by Julien Phalip, 13 years ago

Easy pickings: unset
Needs tests: set

comment:7 by Ramiro Morales, 13 years ago

Component: Database layer (models, ORM)Core (Management commands)
Keywords: inspectdb added
UI/UX: unset

comment:8 by Mariusz Felisiak, 2 years ago

Owner: brockweaver removed
Status: assignednew
Note: See TracTickets for help on using tickets.
Back to Top