When Updating Entity on SQL Server I get:

OException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot update identity column 'id'. en DBObject->save() (línea 95 de D:\_Webs\www.prevencionintegral.com\public\sites\all\modules\eck\eck.classes.inc).

This is because the PrimaryKey field is included in the Update Statement Data, MYSQL kind of silently swallows this kind of things, but some more strict database engines do complain on this kind of stuff.

Just unset the ID in the Update Data, patch attached.

--- /sites/all/modules/eck/eck.classes - Copy.inc Mon Sep 16 19:07:22 2013
+++sites/all/modules/eck/eck.classes.inc Mon Sep 16 19:07:22 2013
@@ -84,14 +84,15 @@
$this->id =
db_insert($this->table)
->fields($this->data)
->execute();
}else{
//well I need to know what the primary id is to set up the condition;
$primary_key = $this->primary_keys[0];
+ unset($this->data[$primary_key]);
db_update($this->table)
->condition($primary_key, $this->{$primary_key}, '=')
->fields($this->data)
->execute();
}

//now that we are done saving lets deserialize in case that for some

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

david_garcia’s picture

Issue summary: View changes
Status: Active » Needs review
david_garcia’s picture

FileSize
534 bytes
fmizzell’s picture

Priority: Normal » Minor

Lowering priority just because I don't have the means or bandwidth to work this myself.

@david_garcia, are you still working on this?

david_garcia’s picture

Version: 7.x-2.x-dev » 7.x-3.x-dev
FileSize
518 bytes
david_garcia’s picture

Rolled against latest dev, but I cannot review or RBTC myself...

Greetings.

  • fmizzell committed e3395f9 on 7.x-3.x authored by david_garcia
    Issue #2090251 by david_garcia: Cannot update identity column on SQL...
fmizzell’s picture

Status: Needs review » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

audriusb’s picture

I am getting same error in Drupal 8 version, is there a quick fix?

audriusb’s picture

Version: 7.x-3.x-dev » 8.x-1.x-dev
Status: Closed (fixed) » Active
david_garcia’s picture

@audriusb If you are using SQL Server, the Drupal 8 driver (8.x-2.x) has been expanded to deal with such situations in order to provide better compatibility with the default MySQL behaviour.

What database engine are you using? If you are using SQL Server on Drupal 8 and experiencing this issue with the 8.x-2.x please provide steps to reproduce and will take a look at it.

Thanks!

audriusb’s picture

Hi David, I see you are the brain behind Drupal support for Windows. Thanks a lot for the effort! Is free 8.x-1.x version not usable at all? I found quite a long discussion about the issue discussed here with patches not compatible with current build but still managed to deploy and hopefully fix it, but in general SQL Server is really complaining and deadlocking almost at every corner. Would be nice if 8.x-1.x branch would at least be in a state of *working* without bells and whistles... my setup is:
1. SQL Server 2012 R2
2. IIS 8.5
3. PHP 7.0.7
4. Wincache 2
5. * according to this, couchbase 2.2.0 starting 20th of June*

legolasbo’s picture

Status: Active » Postponed (maintainer needs more info)

The latest alpha gets its 'id' definition from Drupal core. Since I'm unable to test if this issue still exists I'm postponing this until someone can provide more information.

Beakerboy’s picture

I am using the 8.x-1.0-rc4 release and HAVE hit this error, so I'd say it still exists.

david_garcia’s picture

The underlying problem is that MySQL accepts identity updates (when there are no changes) but Sql Server being properly designed complains with this bad practice.

You are prompt to find this issue in several places, the most important in core itself #2342699: SqlContentEntityStorage tries to update identity/serial values by default.

@Beakerboy you are welcome to jump into #2342699: SqlContentEntityStorage tries to update identity/serial values by default and work on the core patch.

As I mentioned in #11, you can always switch to 8.x-2.x, the driver has measures in place to workaround this difference in behaviour between MySQL and SQL Server.

Beakerboy’s picture

@david_garcia, The 8.x-2.x branch is a pay-to-play branch that requires a library that YOU sell, correct?

david_garcia’s picture

@Beakerboy You are welcome to propose a patch for 8.x-1.x branch, get the reviews, add test coverage, etc. I'll be happy to commit anything that goes through the full community review process and makes sense for the driver.

Indeed, this is much more needed in the core issue #2342699: SqlContentEntityStorage tries to update identity/serial values by default than in the sql server driver itself, as this is just a workaround. You are also very welcome to fix the issue there. Or in both places :)

Looking forward to seeing your patches moving forward the review process in the corresponding issue queues.

Beakerboy’s picture

@david_garcia I created a core patch for 8.4-dev that passes tests and fixes the problem. Could you please review it at the issue you provided above? I'm surprised that 3 lines of code took care of it.

legolasbo’s picture

Status: Postponed (maintainer needs more info) » Closed (outdated)

Closing this since there hasn't been an update in over 6 months.

Beakerboy’s picture

@legolasbo

If you are interested in this bug, there has been a lot of activity here. There is a patch for D8.5 that has been awaiting review for 4 months. If this patch passes RTBC and is merged into core, if will greatly expand the ability for Drupal to be natively used with SQL Server. I would greatly appreciate some Drupal user to review that patch. There is also a D8.4 patch in that issue.