1

I'm using Django 1.6 + MySQL. My model has a custom_user class that extends AbstractUser. 

class CustomUser(AbstractUser):
    dob             = models.DateField()    
    class Meta:
        db_table    = 'custom_user'

I want to delete the database with ./manage.py sqlclear | ./manage.py dbshell (as mentioned here)

The output of sqlclear is 

BEGIN;
DROP TABLE `design`;
DROP TABLE `company`;
ALTER TABLE `custom_user_user_permissions` DROP FOREIGN KEY `customuser_id_refs_id_da27cb33`;
ALTER TABLE `custom_user_groups` DROP FOREIGN KEY `customuser_id_refs_id_d24c897a`;
DROP TABLE `custom_user`;
DROP TABLE `custom_user_user_permissions`;
DROP TABLE `custom_user_groups`;
DROP TABLE `book`;
DROP TABLE `author`;

COMMIT;

This fails with error : 

ERROR 1217 (23000) at line 6: Cannot delete or update a parent row: a foreign key constraint fails

The first 2 tables (design & company) get deleted. If I run the command again, I get 

ERROR 1091 (42000) at line 2: Can't DROP 'customuser_id_refs_id_da27cb33'; check that column/key exists
Community
  • 1
  • 1
user
  • 17,781
  • 20
  • 98
  • 124

1 Answers1

1

Sounds like error 1 happened when you tried to drop custom_user table which some other table has a dependency on. Error 2 happens because it's trying to drop foreign keys that already got dropped.

ERROR 1217 can be caused by three things in my Django MySQL experience:

1.The table you're trying to manipulate(drop) has a foreign key ref to it in another table.

Try: Trace all ForeignKey references to your 'custom_user' table, one of your tables might have a dependency on it so you'll need to find and drop that table first to resolve Error 1217.

Example table structure:
Given 3 tables Customer, Orders, Shipment:
 Customer has no foreign keys
 Orders has a x = models.ForeignKey(Customer)
 Shipment has a y = models.ForeignKey(Orders)

So the one to many relations would be: customer->orders->shipment

so if you tried:

 mysql>drop table customer;   #you'd throw ERROR 1217.

this would work (in this order because of the backward dependencies):

mysql>drop table shipment;
mysql>drop table orders;
mysql>drop table customer;

2.The MySQL database engines for your tables aren't the same. Try changing them to all be the same thing. To check, run: mysql>select TABLE_NAME,ENGINE from information_schema.TABLES;

If the results of that query show your model tables having different engines(INNODB/MYISAM) change the different model tables to be the same by running this statement in your mysql prompt.

#For INNODB
mysql>ALTER TABLE custom_user ENGINE=INNODB;
#For MyISAM
mysql>ALTER TABLE custom_user_user_permissions ENGINE=MyISAM;

3.Try turning off the foreign key check in your settings.py file. Add this code in your data base dictionary.

DATABASES = {
'default': {
    ...         
    'OPTIONS': {
         "init_command": "SET foreign_key_checks = 0;",
    },
 }
}
Higgs Bogson
  • 361
  • 2
  • 5
  • I'm extending Django's [AbstractUser model](https://docs.djangoproject.com/en/dev/topics/auth/customizing/). The tables `custom_user_user_permissions` & `custom_user_groups` are created automatically. I suspect the answer is linked to it. – user May 09 '14 at 04:35
  • @buffer Did you get this fixed?, try dropping the tables without the alter statements in your original query and in different orders. I would suggest dropping 'custom_user' after `custom_user_user_permissions` and `custom_user_groups`; Either custom_user, customer_user_user_permission or custom_user_groups have foreign keys that are tying entries between tables. – Higgs Bogson Jun 03 '14 at 01:19
  • The sql is auto generated by Django. I suppose it's a problem with the `./manage.py sqlclear` Django command. For the time being, I've bypassed Django and doing a direct `drop database;` SQL statement. – user Jun 03 '14 at 04:05