PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-view files' for key 'PRIMARY'

Just got bit by this. Apparently something else went through and created permissions with the correct name before the update hook was run, so when it tries to change the names of old permissions to the new name, it'll complain that the newly named permissions already exist. Recipe for a bad day.

Without having enough in front of me to determine the root cause (likely a missed updb somewhere), what you can do is run the following pairs of queries, and make sure there are no duplicate 'edit file' and 'edit any files' permissions or 'view file' and 'view files' permissions for any given rid.

SELECT * FROM role_permission WHERE permission='view file'
SELECT * FROM role_permission WHERE permission='view files'

SELECT * FROM role_permission WHERE permission='edit file'
SELECT * FROM role_permission WHERE permission='edit any files'

It doesn't particularly matter which permission each rid has (that update hook normalizes them, which is the whole point), only that a given role doesn't have both of them, and that you don't accidentally lop any off altogether.

As this seems like an unfortunate artifact of of database updates not being run immediately, I'm going to say this is "by design" unless anyone can provide forensics suggesting otherwise. The update hook does exactly as advertised.

How to reproduce
* disable module
* exchange with new version
* enable module (which already creates permissions)
* update-db (which should check imho)

;-)

When update 7203 fails, all the following updates will not be run (using drush at least). So raising prio.

I also think found the reason for the permission clash: the admin role is set on module enable!

I am seeing this too. Can you please clarify the workaround? Is it just a permission issue? Why the dup key? thx.

I'm also seeing this. Unable to update the database.
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-view files' for key 'PRIMARY'

Any help is appreciated.

guessed workaround:
* go to db and remove the new "view files" and "edit any files" permissions
* run db-update which will rename the old "view file" and "edit file" to this

solution see @todo:

/**
 * Update permission names.
 */
function file_entity_update_7203() {
  $permissions = array(
    'view file' => 'view files',
    'edit file' => 'edit any files',
  );
// @todo If we find old permission records, first delete the new records (autogenerated on module enable) to not get exceptions.
  foreach ($permissions as $old => $new) {
    db_update('role_permission')
      ->fields(array('permission' => $new))
      ->condition('permission', $old)
      ->execute();
  }
}

Thanks axel.rutz! That did it.

DELETE FROM role_permission WHERE permission='view files';
Then it updated fine.

Two years later, but this still worked for me. I followed https://www.drupal.org/node/2082037#comment-9576951 to update from Media 7.x-1.6 to 7.x-2.0 and got the SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-view files' for key 'PRIMARY' error. Following #9 here resolved all my issues. Now updated! Thanks.

I encountered this error after I installed File Entity (dependency) in order to try out the Workbench Media module.
I'm a site builder so I am not equipped to work directly with the database. The only thing I can recommend when encountering this error (which crashes your drupal site) is using Drush (drush pmu file_entity) to uninstall the File_Entity module and then rerunning the database-update script (displayed as a link on the modules config page). That at least will get your site back up.
But can anyone explain if File Entity will ever be safe to use?

@mattstein care to share with us the version of media, file_entity and workbench_media you experienced this with? database updated needed to run, tell me that you also upgraded existing version of media/file_entity?

Needs a patch based on number 8 and 9
Asap

Could not reproduce this issue. I attempted the steps described in https://www.drupal.org/node/2073317#comment-9422587

after looking more closely, it looks like if someone follows the correct module installation procedure that this won't happen.

basically, a module should not normally be disabled when doing an upgrade of the modules files.

as for the workbench_media module, I didn't test that one, it might be out of date, however I am guessing that the upgrade steps were not followed correctly.

with that being said, I tried several times repeating the steps outlined in comment #4 but I was unable to get the PDO exception to occur.

I am guessing that this issue might be fixed completely by using the latest D7 core 7.54 at this time of writing or latest ctools dependency of file_entity.

cannot reproduce, if you experience this issue, please see the workarounds as described above.

I did however make a test patch, turns out I didn't need it, however might as well leave it here for someone in case they come accross this again. I couldn't reproduce the issue therefore didn't need this patch.

see the patch by @dnotes in the child issue:
#2871153: Upgrade from 1.x breaks at update_7203 with duplicate entry 1-view files for primary key

A new release of media 7.x-2.4 was tagged that has a fix for this.
#2871153: Upgrade from 1.x breaks at update_7203 with duplicate entry 1-view files for primary key

so the issue was caused by some issue on the 1.x to 2.x upgrade path that affected some (but not all) and it's been resolved with 7.x-2.4