Django AutoField default value error

Question

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...?

score 9 · Accepted Answer · answered Dec 23 '14 at 12:54

9

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.

answered Dec 23 '14 at 12:54

Villiers Strauss

395
3
7

Is the `--fake` on the latest migration number or the problematic one? And do I need to use that flag every time I migrate this app for ever after? – WAF Dec 23 '14 at 13:04
The `--fake` should only be run on the problematic one, i.e. the one where you tell it that this field is a primary key again. After that Django shouldn't bug you about it again. – Villiers Strauss Dec 23 '14 at 13:12
Worked! Also, thanks for the warning about making sure the reality aligns with the remaining SQL. – WAF Dec 23 '14 at 13:26
1

Now my closely related but non-overlapping question is how to avoid the bug _a priori_ - i.e. add an `id` column (or have one auto-added) to an existing table with more than 0 rows already populated. It prompts me for a default value, but I know that is the trap that led to my original quandary. – WAF Dec 24 '14 at 12:47
@WAF have you found the answer to last question? – webdevbyjoss Jun 22 '15 at 13:57
1

@webdevbyjoss Nope. I gave up looking since I had a workaround for when it arises, but I am still curious. – WAF Jun 22 '15 at 14:29

score 0 · Answer 2 · edited May 23 '17 at 11:54

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

That SQL, with the AI default value, was generated by Django. I did remove the `default` and the `auto_increment`, but the problem is that the old fossilized migration which did have those features continues to thrown an exception because it is the target of subsequent dependencies. — WAF, Dec 23 '14 at 13:08
Are you syncing up your DB before the migration? python manage.py syncdb — planet260, Dec 23 '14 at 13:15

score -1 · Answer 3 · edited Nov 07 '17 at 14:08

-1

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

edited Nov 07 '17 at 14:08

Robert Columbia

6,313
15
32
40

answered Nov 07 '17 at 13:30

hua Stefan

1

It must be a comment – Anand Pandey Nov 07 '17 at 13:47
1

and it does not have to be bold – Jelmergu Nov 07 '17 at 13:49

Django AutoField default value error

3 Answers3