Running syncdb with initial data that inserts a primary key value fails

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Set up an initial_data fixture
2. run manage.py syncdb

What is the expected output? What do you see instead?

It's expected that the fixture is installed, what actually happens (on
FreeTDS at least) is this:

IntegrityError: ('23000', "[23000] [FreeTDS][SQL Server]Cannot insert
explicit value for identity column in table 'TABLENAME' when
IDENTITY_INSERT is set to OFF. (544) (SQLExecDirectW)")

What version of the product are you using? On what operating system?

Trunk as of the beginning of Feburary (don't know the revision sorry).
Ubuntu 9.10 + FreeTDS

Please provide any additional information below.

Using the same fix as this patch from django-mssql it works fine: 
http://code.google.com/p/django-mssql/source/detail?r=227

Original issue reported on code.google.com by Kaz...@gmail.com on 10 Feb 2010 at 11:36

[GoogleCodeExporter]

Please submit model, fixture and test case, thanks.

Original comment by vcc.ch...@gmail.com on 30 Apr 2010 at 5:42

[GoogleCodeExporter]

I'm not the original submitter, but I guess the model is not managed and the 
primary
key is an autoincrement field, whose value is explicit defined in the
"create"/"get_or_create".

Original comment by claudiom...@gmail.com on 17 May 2010 at 8:27

[GoogleCodeExporter]

I am also not the submitter, but I can narrow down the problem: create a django 
model with an AutoField attribute, set its primary_key = True, and set its 
db_column to something other than the attribute name. The if statement

if (meta.pk.attname in self.columns) and (meta.pk.__class__.__name__ == 
"AutoField"):

is thus incorrectly false: it should be (meta.pk.db_column in self.columns).

The linked issue in django-mssql corrects the attname to db_column (it also 
happens to correct the case of subclasses to AutoField, but that's a 
coincidence).

Note that there are two places that this bug appears: query.py and compiler.py.

Original comment by SheepN...@gmail.com on 19 Jul 2010 at 7:58

[GoogleCodeExporter]

I've created a model, fixture and test case.

Problem installing fixture 
'D:\borken_db\borken_prj\borken_app\fixtures\initial_data.json': Traceback 
(most recent call last):
  File "m:\a\obj\web_portal\python\lib\site-packages\django\core\management\commands\loaddata.py", line 169, in handle
    obj.save(using=using)
  File "m:\a\obj\web_portal\python\lib\site-packages\django\core\serializers\base.py", line 165, in save
    models.Model.save_base(self.object, using=using, raw=True)
  File "m:\a\obj\web_portal\python\lib\site-packages\django\db\models\base.py", line 528, in save_base
    result = manager._insert(values, return_id=update_pk, using=using)
  File "m:\a\obj\web_portal\python\lib\site-packages\django\db\models\manager.py", line 195, in _insert
    return insert_query(self.model, values, **kwargs)
  File "m:\a\obj\web_portal\python\lib\site-packages\django\db\models\query.py", line 1479, in insert_query
    return query.get_compiler(using=using).execute_sql(return_id)
  File "m:\a\obj\web_portal\python\lib\site-packages\django\db\models\sql\compiler.py", line 783, in execute_sql
    cursor = super(SQLInsertCompiler, self).execute_sql(None)
  File "m:\a\obj\web_portal\python\lib\site-packages\django\db\models\sql\compiler.py", line 727, in execute_sql
    cursor.execute(sql, params)
  File "m:\a\obj\web_portal\python\lib\site-packages\django\db\backends\util.py", line 15, in execute
    return self.cursor.execute(sql, params)
  File "m:\a\obj\web_portal\python\lib\site-packages\sql_server\pyodbc\base.py", line 314, in execute
    return self.cursor.execute(sql, params)
IntegrityError: ('23000', "[23000] [Microsoft][ODBC SQL Server Driver][SQL 
Server]Cannot insert explicit value for identity column in table 
'borken_app_borkentable' when IDENTITY_INSERT is set to OFF. (544) 
(SQLExecDirectW)")

Original comment by SheepN...@gmail.com on 20 Jul 2010 at 3:35

Attachments:

• IdentityInsertNotOff.zip

[GoogleCodeExporter]

Missed instructions: when I attempt to syncdb on the attached django project, I 
pop the posted error.

Original comment by SheepN...@gmail.com on 20 Jul 2010 at 3:36

[GoogleCodeExporter]

Hi I'm the original submitter. Sorry I haven't been able to provide more info, 
we switched to a MySQL backend not long after reporting the issue so the 
code/database that caused it hasn't been available. Glad there is now a test 
case for it though :)

Luke.

Original comment by Kaz...@gmail.com on 20 Jul 2010 at 3:39

[GoogleCodeExporter]

Fixed in r183, and also need patch pyodbc for insert null value when specifying 
the primary key, please see attached pyodbc patch (just comment out the 
GetParamType error raise, should not raise error).

Original comment by vcc.ch...@gmail.com on 22 Jul 2010 at 6:00

• Changed state: Fixed

Attachments:

• params.diff

[GoogleCodeExporter]

Please note:

1) The issue is not quite fixed in r183; line 76 in query.py
was set to

if meta.pk.db_column in self.columns and     meta.pk.__class__.__name__ == 
"AutoField":

It should be

if meta.has_auto_field and meta.auto_field.column in self.columns:

This correction is based on the django-mssql fix, which is now called 
http://code.google.com/p/django-mssql/source/detail?r=52b2cf58f484308c6b9d3e5655
8b234365c67fcf

It is better because it allows for an identity field that is:
- not a pk
- not directly AutoField but some child class

Additionally, it uses field.column instead of field.db_column. The latter is 
the parameter passed (or not) in field creation, the former is the object 
attribute that names the column in the db.

A similar change should be done in compiler.py line 281.

2) A different fix to the pyodbc problem with nulls has been committed there; 
trunk for pyodbc no longer has the error.

Original comment by shai2pla...@gmail.com on 5 Jan 2011 at 2:05

[GoogleCodeExporter]

The changes described above as a patch.

Original comment by shai2pla...@gmail.com on 5 Jan 2011 at 2:16

Attachments:

• pyodbc.75.patch