Django - sqlclear fails to delete database with Ab

Question

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

Answer 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;",
    },
 }
}