http://drupal.org/node/1050800

I'm having the same problem, even after updating to RC2 and trying dev. Here's my error message:

PDOException: SQLSTATE[23000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.file_managed' with unique index 'uri_unique'. in drupal_write_record() (line 6776 of E:\content\includes\common.inc).

Comments

Ryan S’s picture

Ryan S commented
Version: 7.x-1.0-rc2 » 7.x-1.0-rc3

Using RC3 now. Still having the same problem. Image uploads work sporadically, but it seems that once one or a few are successful, the rest get the above error message displayed. That would make sense if some field in the database is being truncated. But when I look at the rows in dbo.file_managed, this is what I see as the results:

_unique_uri

0x2861BB2B48E429A83B9124A018427DFC
0x8B31F44762B8CF7E684355C36B52FD91
0x953C58A2BBE259205269A51CCA4E5232
0x979925C58AE1AF36AAA23A33DD9F80E6
0xCB01C3DE77E79B06CE9ECFCA39DD0BBE

Wouldn't this signify that the "unique_uri" is unique? If that's the case, what could be causing the problem?

damien tournoud’s picture

damien tournoud commented

The issue you are facing is that we have no way to properly update the schema structure between the different versions of the database driver, because Drupal core prevents us to do so (see #1010188: Add support for database-driver updates).

You updated the module, but the already created database tables have not been updated.

One way of fixing this is to use Portable Database dump to create a dump of the site, then reimport it in place.

Ryan S’s picture

Ryan S commented

Is there any other way of fixing it besides using Drush? All our machines run Windows... and the documentation for Drush says Windows support is lacking.

Would using SQL Server Management Studio to export / import work?

Also, I tried using Drush on Windows but it says it could not find the command "portabledb-export"...

Jeroentje’s picture

Jeroentje commented

I have the same problem in RC3. On a clean installation with a new (empty) database, after adding the first Article with an image uploaded, the second Article directly failes with the above error after trying to upload a second image.

The problem seems to come from the 'Article', because if I add an Image to the 'Basic page' I can as many Basic pages with images as I want.

Ryan S’s picture

Ryan S commented

Ok. I decided to copy my drupal directory over from the IIS server so that I can run it in a VM running Ubuntu. I've gotten further in the portabledb dump, but I'm now getting the following error when running the Drush commands you provided,

PHP Fatal error: Undefined class constant 'SQLSRV_ATTR_DIRECT_QUERY' in /home/irriadin/drush/includes/database/sqlsrv/database.inc on line 59

Looking up the previous error message documented here #1037558: Undefined class constant 'SQLSRV_ATTR_DIRECT_QUERY' is confusing, because I *am* using SQL Server 2008. What should I do now?

Ryan S’s picture

Ryan S commented
Version: 7.x-1.0-rc3 » 7.x-1.0

Ok, I was able to fix this problem without using Drush. For those of you running IIS on Windows machines, try using this method. Keep in mind that I ran into some problems when doing this, so make sure you make a backup and thoroughly test your database afterwards.

Using the DBTNG Migrator (http://drupal.org/project/dbtng_migrator), I was able to export the database into another Drupal database (going from SQLSRV to SQLSRV). However, when doing this, I highly recommend uninstalling and removing your custom modules. When doing this the first time, Chaos Tools essentially caused my entire database to implode. Just to be safe, I removed and uninstalled all my custom modules.

Ryan S’s picture

Ryan S commented
Status: Active » Closed (fixed)

Marking this as closed, since it was proven to be schema structure issue related to Drupal's inability to manipulate the database.

kpastore’s picture

kpastore commented

Should this be closed or remain open to be fixed? This is a major problem if Drupal cannot work with MS SQL, yet says it is supported. I am having this exact problem with Profile2, and receive this error while trying to run update.php.

PDOException: SQLSTATE[23000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.profile_type' with unique index 'type_unique'. in drupal_write_record() (line 6859 of site\includes\common.inc).

Ryan S’s picture

Ryan S commented

Feel free to change it to active if you think there's a legitimate problem. I was able to confirm that Damien was right about Drupal's locked down schema updating being the root of the problem for me. Have you been using the sqlsrv driver for a while now? Chances are you've got the old and busted database tables. Reference: http://drupal.org/node/1161664#comment-4540872

skhot’s picture

skhot commented

@Damien Tournound or anyone else who might know, you mentioned that using drush (http://drupal.org/sandbox/damz/1174672) was one way to fix the problem, do you know of any other methods you could suggest?

I have a dev site that was planning to go to production soon, when all of the sudden (likely from updating modules and core), we started getting these kinds of errors:

PDOException: SQLSTATE[23000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.file_managed' with unique index 'uri_unique'. in drupal_write_record() (line 6776 of E:\content\includes\common.inc).

PDOException: SQLSTATE[23000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.block' with unique index 'tmd_unique'. in drupal_write_record() (line 6859 of E:\content\includes\common.inc).

PDOException: SQLSTATE[42S02]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'shib_auth'.: SELECT * FROM {shib_auth}; Array ( ) in _shib_auth_list_rules() (line 101 of E:\content\sites\all\modules\shib_auth\shib_auth_roles_forms.inc).

I suspect that these errors are due to no way to properly update the schema structure between the different versions of the database driver. We are using Microsoft SQL Server 10.50.1600 and Microsoft-IIS/7.5 with Drupal 7 driver for SQL Server and SQL Azure. Any advice on how to overcome this problem?

Ryan S’s picture

Ryan S commented
Status: Closed (fixed) » Active

Changing back to active since there is a lot of activity.

@skhot, I found an alternative to Drush was using the DBTNG experimental module to export the database and reimport it, from SQLSRV to SQLSRV. Reference my post: http://drupal.org/node/1161664#comment-4661778 and please keep in mind that you may run into problems with this method, particularly if you have modules like Chaos Tools installed. To ensure that my database integrity was sound, I removed and uninstalled ALL my modules before using the DBTNG.

skhot’s picture

skhot commented

Thanks for marking this active and for reiterating your suggestion (I'm concerned about the stability of this method as I'm not the one who controls the our db/server environment). I do think this is a significant problem for this modules. :/

coastaldrupaladmin’s picture

coastaldrupaladmin commented
Version: 7.x-1.0 » 7.x-1.1

This is still an issue for 7x-1.1 with Drupal 7.7 core. When attempting to upload a logo into a theme or as a default logo, we get this error every time. Same setup as Comment #10 skhot. This is a brand new install with minimal changes (yes, there used to be another logo, so there is a conflict there for sure), so it is not a SQL DB inconsistancy issue.

coastaldrupaladmin’s picture

coastaldrupaladmin commented

I was able to solve the issue by going into the SQLExpress database via SQL Server Management Studio and changed the 'uri_unique' index from the field '__unique_uri' to the field 'uri'

artusamak’s picture

artusamak
he/him
Primary language French
Location Bzh
commented
Status: Active » Fixed

According to the fact that we have a fix and that Drush support on windows has been hugely improved i'm closing this one.

Status: Fixed » Closed (fixed)

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