CODEWITHSUNDEEP
mysqldjangopython-2.7database-migration
WAF
WAF

Reputation: 1013

Django AutoField default value error

Django 1.7.1, MySQL 5.6, Python 2.7.8

I had a model that looked like this:

class Host(models.Model):
    hostName = models.CharField(max_length=45, primary_key=True)
    ...

I removed primary_key=True manually, which caused manage.py sqlmigrate to show that the primary key was being dropped, an autoincrementing 'id' column was being added, and it was getting the primary key. I was prompted for a default value for the new 'id' column, and mistakenly gave it 1, which was already in the table. The relevant SQL read:

ALTER TABLE `Host` ADD COLUMN `id` integer AUTO_INCREMENT DEFAULT 1 NOT NULL PRIMARY KEY;

and the migration code included:

    operations = [
    migrations.AddField(
        model_name='host',
        name='id',
        field=models.AutoField(auto_created=True, primary_key=True, default=1, serialize=False, verbose_name='ID'),
        preserve_default=False,
    ),

The result was that I can still modify my models and makemigrations works, but each migrate command gives this error:

django.db.utils.OperationalError: (1067, "Invalid default value for 'id'")

and does not take effect.

I've tried reverting the primary key change, manually pointing the immediately subsequent migration at the immediately previous migration (which raised consistency problems), and migrating explicitly to the immediately previous migration. What I would really like to do is simply erase/ignore the problem migration. I would also be satisfied with suppressing the error since I have since reverted the change. How can I do either of these?

Edit: Also, if an autoincrement column can never have a default value why does Django allow itself to pass the SQL with ...AUTO_INCREMENT DEFAULT 1...?

Upvotes: 7

Views: 15644

Answers (3)

hua Stefan
hua Stefan

Reputation: 1

primary_key can't have this default parameter, so you should delete this default parameter.

Upvotes: -1

planet260
planet260

Reputation: 1474

You cannot set default value for Auto Increment Columns in MySQL.

You have to change your DB table schema. The problem is that your Primary Key "id" (Auto Generated by Django) has Auto Increment = "True". As Auto increment is true you are unable to set default value to 1. Each time you run your migration script it throws error because of that

For Example if you run following query 

ALTER TABLE YOU_TABLE CHANGE id id INT(11) AUTO_INCREMENT NOT NULL DEFAULT 1;

It will throw an error an error

MySQL Database Error: Invalid default value for 'id'    1

So you have to change your Primary key to have no default value.

ALTER TABLE YOU_TABLE CHANGE id id INT(11) AUTO_INCREMENT NOT NULL;

This way the schema will be changed and you will no longer get this error.

Also in Django you can set default value like this

column = models.CharField(max_length=7, default='0000000', editable=False)

For Django Reference: https://docs.djangoproject.com/en/dev/ref/models/fields/#editable StackOverflow Question: Default value for field in Django model

Upvotes: 0

Villiers Strauss
Villiers Strauss

Reputation: 395

You can fix the error in SQL (if you haven't already) and then just run ./manage.py migrate [your_app_name] [the_migration_number] --fake

The --fake will tell Django to pretend it ran the migration and it will not try to run it again.

Just be sure that the changes you make in SQL are accurately reflected in the fake migration, because Django does not compare the models to the database when you run makemigrations.

Regarding the error about the id column, if I remember correctly, you cannot assign a default value to a mysql auto increment field.

Upvotes: 9

Related Questions