Django - legacy db and problems with 'id' field

Question

I'm integrating a MySQL database from a php app into a new Django project. Inspectdb worked well, I just had to change a couple of fields to ForeignKeys and now all the reading and editing current data is working great.

The problem is when I try to create a new entry, I get the error "Field 'id' doesn't have a default value". The traceback starts from the form.save() call and the exception is coming from the MySQL cursor. In most cases the column is named id but in one case it is a named value:

class ModelOne(models.Model): #normal "id" named pk 
    id = models.AutoField(primary_key=True, db_column='id', default=None)
    other_fields = ...

class ModelTwo(models.Model): #specific pk
    named_pk = models.AutoField(primary_key=True, db_column='named_pk',
                                default=None)
    other_fields = ...

For ModelTwo, when I POST a valid form, I get the error, but then if I go back to my data list, the new item shows up! And after I checked the latest id values in the shell, I can see that they are incrementing correctly.

But for ModelOne (with just id), the error still shows up, and the pk become 2147483647 (the max) and subsequent saves fail because of duplicate ids. (the next highest pk is only 62158)

What do I need to do to get these id fields working correctly?

update: Still no luck fixing this. Thinking about dumping the data and importing it into fresh, Django-built tables. Still looking for a solution to this problem.

update2: Info from db shell

ModelOne:

+-------------+--------------+-------+------+---------+-----------------+
| Field       | Type         | Null  | Key  | Default | Extra           |
| id          | int(11)      | NO    | PRI  | NULL    | auto_increment  |

ModelTwo:

+-------------+--------------+-------+------+---------+-----------------+
| Field       | Type         | Null  | Key  | Default | Extra           |
| named_pk    | int(11)      | NO    | PRI  | NULL    | auto_increment  |

@Daniel Roseman - same exact thing. I only added it in an attempt to directly address what it seemed like the error was pointing at — j_syk, Sep 20 '11 at 20:19

Rachid · Answer 1 · 2014-06-05T22:59:08.420

I met the same issue after some complex South migration. We wanted to avoid to reload the database (dump/import), luckily it will help other peoples who fall on this post after searching for the same issue.

We found a solution which solve this problem without the need of exporting and importing the database.

For a table named auth_user, the following MySQL command will fixe the above error message:

ALTER TABLE auth_user MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT;

original solution from :

http://webit.ca/2012/01/field-id-doesnt-have-a-default-value/

Sergey Golovchenko · Answer 2 · 2011-09-23T19:32:26.857

3

It makes sense to define pk for ModelTwo (as your are already doing) because your pk has different name 'named_pk'. However, no need to explicitly define 'id' as your pk for ModelOne. Django will create id column by default. So do not define the id column at all for ModelOne.

UPDATE: remove "default=None" from the model and default NULL from the database for ModelTwo for named_pk

edited Sep 23 '11 at 19:32

answered Sep 21 '11 at 21:09

Sergey Golovchenko

18,203
15
55
72

I've tried this approach too. If I go into the SQL and look at the able structure there already is an 'id' column set to auto_increment. I think the problem lies in Django recognizing it. Any ideas why the error is coming up for ModelTwo despite it actually saving data correctly? – j_syk Sep 21 '11 at 21:52
I updated my question to show the MySQL output from `SHOW COLUMNS FROM table_name`. I don't quite follow what you are saying, ModelTwo has no column named 'id'. The field 'named_pk' has always been the PK column. For that model, the actual value of 'named_pk' is very important (it correlates to sequential order numbers in a purchasing system). – j_syk Sep 22 '11 at 13:12
If there is no 'id' column in ModelTwo table, then whats the error when saving ModelTwo? – Sergey Golovchenko Sep 22 '11 at 15:09
"Field 'named_pk' doesn't have a default value" -- But as I mentioned earlier, despite the error for ModelTwo, it saves properly with the right increment of "named_pk". I just have to leave the error page and go back to my list to see it. – j_syk Sep 22 '11 at 15:55
remove "default=None" from the model and default NULL from the database for ModelTwo for named_pk – Sergey Golovchenko Sep 22 '11 at 19:41
I can't seem to find the MySQL command to remove the default altogether. I tried `ALTER TABLE table_name ALTER id DROP DEFAULT;` but it's not making a difference, still showing as default null when I use the `SHOW COLUMNS` command. – j_syk Sep 26 '11 at 14:13

score 1 · Accepted Answer · answered Sep 29 '11 at 19:36

1

I ended up exporting the data from the original database, and loading it into a fresh db which was generated by my models/project.

I think I had tried too many things with the copy of the original database I was working on and botched up the underlying SQL for the pk field.

I'm just glad that I was only working with a copy.

answered Sep 29 '11 at 19:36

j_syk

6,511
2
39
56

Same thing happened to me, same fix. The data export, then recreation of the tables allowed django to set up the mysql auto-increment attribute properly delegating generation of the new pk to mysql. – hobs Jul 26 '12 at 20:07

Django - legacy db and problems with 'id' field

3 Answers3