Integrity constraint violation: Duplicate entry for key 'PRIMARY' in role_permission table

Hi lsolesen,
The update function 7102 changed the text name of the Scheduler permission on the {role_permission} table for existing rows. It was not clear from your message, is that the table which had the integrity violation? The code is:

/**
 * Update role_permission table with clean machine name.
 */
function scheduler_update_7102() {
  // Change all values of 'schedule (un)publishing of nodes' to the cleaner
  // 'schedule publishing of nodes'
  // @see http://www.drupal.org/node/2538002
  $rows_updated = db_update('role_permission')
    ->fields(array('permission' => 'schedule publishing of nodes'))
    ->condition('permission', 'schedule (un)publishing of nodes', '=') ->execute();

  return format_plural($rows_updated, '1 row updated in role_permission table.', '@count rows updated in role_permission table.');
}

Do you recall exactly when the update was run? Could you possibly have used a dev version of Scheduler before running the update? Or maybe the update had been delayed and in the meantime you had added the new permission to a role, so that when the update was run that table already had the new text.

Upgrading via Aegir on a BOA stack

I have not used either of these, so I don't know what they might do that is different.

If you have access to the db tables directly it can be fixed very easily. It would interesting to see what rows you currently have.

I have the same error on nearly all on our production sites.

I recently opened another issue with Scheduler and followed your suggestion to check if updatedb had any problems. And actually it was this issue that had brought lots of DB updates to a halt.

I am not sure about the state of the database, but shouldn't this work anyway, even if there is some junk left in the db from possible earlier installations?

The update function was to make sure that any roles which had the old permission 'schedule (un)publishing of nodes' now have the renamed permission 'schedule publishing of nodes'. If you have granted (or re-granted) Scheduler permission to a role after upgrading to 1.4 but before running the update script then the new text will exist in the {roles} table and the update will fail due to duplicate values. I admit that I never thought of this scenario, always expecting admins to run the update before starting to use the new module. So maybe it could have been written in a more complex way, so that each row is checked for the new value before executing.

If you can view the {roles} table it should be easy to identify if any rows need to be updated. This can be done manually if you need to. Yes, Scheduler will work ok if there are old and new rows in the db table.

Is #2691589: 7.x-1.4 reset permissions, get WSOD on overview page the other issue you are referring to? If that is resolved please update it. Thanks.

Two months with no response from O.P. and one month with no response from second commenter, and no reports from other users.

However, as of the latest usage stats 18,571 sites are running 7.x-1.4 and 34,346 are still running 7.x-1.3. So there are plenty of potential upgrades still to happen. It may be worth considering making a change to the update function to remove the possibility of this integrity constraint if the admin uses the new permission before running the db updates.

I've created a patch for this issue. The update function now checks if there are already rows with the permission "schedule publishing of nodes". If there are any, it skips the update. See the attached patch.

Hi fabianderijk,
Thanks for looking at this and making the patch. I also had some thoughts on how the function should be re-written. Your method avoids the exception, but would mean that no roles would get updated if just one role had been assigned the new permission prior to running the update. This would mean that many roles would lose the Scheduler permission and the admin would have to re-assign all. It might be a better idea to do the update in the following parts:

1. select all roles which have the old permission and not the new one
2. for these roles update the permission string
3. delete all rows which have the old permission.

I was also wondering if this change could be retro-committed to 7.x-1.4. Is that possible? I am not sure there has been enough activity to warrant a release of 1.5 yet, see #2703335: Features and Fixes for Scheduler 7.x-1.5.

An alternative way to do the SQL which does not involve an inner join or subquery as in (1) above would be:

1. Select all rid (role id) from role_permission which have the new permission string
2. Use this in where criteria to delete all rows which have these rid and have the old permission string
3. Update the remaining rows which have the old permission text to the new text

We are running into the same problem here, except it's sporadic when and on what site it's popping up on. This is happening during initial deployment of the updated scheduler module, so we're not sure how the new permission string is getting into the database. My hunch is that during our deployment process we were doing a features revert before running the updates and the updated feature (that uses the new permission), is adding it to the table prematurely. I updated our deployment process to do the updates before the feature reset, and will see how that goes.

As a work around, i've just been deleting the the offending row and running the update again to restore it. Eg DELETE FROM role_permission WHERE permission = 'schedule publishing of nodes'

Hi jrearick,
Thanks for the background info. Sorry we have caused you these difficulties. Attach is a patch which does the update in two stages, as describe in #8. The function in full is:

/**
 * Update role_permission table with clean machine name.
 */
function scheduler_update_7102() {
  // Change all values of 'schedule (un)publishing of nodes' to the cleaner
  // 'schedule publishing of nodes'
  // @see http://www.drupal.org/node/2538002
  // Updates done in two stages to avoid integrity constraint violation.
  // @see http://www.drupal.org/node/2706119

  // Select all role ids which already have the new permission value.
  $query = db_select('role_permission', 'rp')
    ->fields('rp', array('rid', 'permission'))
    ->condition('permission', 'schedule publishing of nodes');

  // Delete the rows for these roles which also have the old permission value,
  // as these are no longer needed and should not be updated to the new value.
  $rows_deleted = 0;
  if ($rows_to_delete = $query->execute()->fetchCol()) {
    $rows_deleted = db_delete('role_permission')
      ->condition('rid', $rows_to_delete, 'IN')
      ->condition('permission', 'schedule (un)publishing of nodes', '=')
      ->execute();
  }

  // Now update any other rows which still have the old permission value.
  $rows_updated = db_update('role_permission')
    ->fields(array('permission' => 'schedule publishing of nodes'))
    ->condition('permission', 'schedule (un)publishing of nodes', '=')
    ->execute();

  return format_plural($rows_deleted, '1 row deleted', '@count rows deleted')
    . ', ' . format_plural($rows_updated, '1 row updated', '@count rows updated')
    . ' ' . t('in role_permission table');
}

For testing, in a php window on your site you can run:

include_once('includes/install.inc');
drupal_set_message('current installed schema version is ' . drupal_get_installed_schema_version('scheduler'));
drupal_set_installed_schema_version('scheduler', '7101');
drupal_set_message('after seting, version is now ' . drupal_get_installed_schema_version('scheduler'));

Then navigate to update.php. Here is the output when one role already had the new permission and two others had the old value.

I would like to get this fixed, but I know that many users will not want to install a dev version so I think the only solution is to release 7.x-1.5, even though we only released 1.4 in mid-March this year, four months ago.

I decided to replace update_7102 and rename the fixed version update_7103. This way, when the new 7.x-1.5 is released, we guarantee that the fixed update function will be run. For those who ran 7102 it will not matter also running 7103 as it will do nothing, and for those who had a failure with 7102 (or did not even upgrade to 7.x-1.4) we ensure they will run 7103 and everything should be fine.

There are 9 issues fixedin the 7.x-dev codebase (including this one) with another which I will look at - see #2703335: Features and Fixes for Scheduler 7.x-1.5 for details. Then I'll make a new release 1.5 so that the remaining users who have not upgraded to 1.4 can go straight to 1.5 and will avoid the potential constraint violation.

Thanks everyone for your contributions.