Clueless !!!

A journal to keep track of my experimentation with different technologies

Is it a MySQL or Django fault ?

leave a comment »

Well I am not really sure whom to fault on that. But I am only super glad that I did double check the database constraints.

Django has this super cool feature which lets you create the tables in the database — “syncdb”. When you are using a MySQL database this is a part of what you will end up getting:

CREATE TABLE `auth_user_groups` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`user_id` integer NOT NULL REFERENCES `auth_user` (`id`),
`group_id` integer NOT NULL REFERENCES `auth_group` (`id
`),
UNIQUE (`user_id`, `group_id`)
);

That sounds like a perfectly valid SQL statement and tables get created perfectly fine. The lines in bold indicate that there is a foreign key to another table’s column from the current table. But when I run syncdb the foreign key constraints just won’t get created on the database. I spent a whole evening trying to figure out what I might be doing wrong since everythign seemed to be perfectly alright. Finally I found the culprit after reading through MySQL Documentation.

Important

The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored by InnoDB. InnoDB only accepts REFERENCES clauses when specified as part of a separate FOREIGN KEY specification.

I am not really sure whose fault it should be. As a user I would either want

a) MySQL to throw errors.

OR

b) Django to generate the format that MySQL would really use and not just ignore.

I am not sure how many django developers who use MySQL really know that this is the case. If not for me using mysql in linux which defaults to using myISAM and my histrionics to make the app use InnoDB, I probably wouldnt have checked or delved deep into this.

I need to check if there is a ticket in django already for this. If not maybe this is probably the first patch I can work on for django.

Anyways, I ended up using sqlall to generate the sql statements and store it in a file and parse the file to create the alter table commands. I have put this as a django snippet in case anyone is as lazy as me to write the alter table commands manually.

About these ads

Written by vidyanand

June 16, 2008 at 7:43 pm

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: