Adding a new permission causes integrity constraint violation

Attached is a tiny patch, which adds the "module" key to exported permissions. Setting module as 'features' which I believe is how features 6.x handles it.

this patch worked well for me. thanks @careernerd!

Just talked to @agentrickard to clarify what "module" column signifies and looks like it should be set to the module that originally declared the permission (where corresponding hook_perm is). Therefore just setting module to "features" in all cases is incorrect, in general.

Fixing the patch and will commit.

Committed: http://drupalcode.org/project/features.git/commit/fc5790f

Thanks for finding/helping fix.

I'm still seeing this with Features 7.x-1.0-beta2.

Marked #1094400: Import feature contains taxonomy and #1011314: Writing tests for custom Features fails as duplicates.

The core issue that's at least partly responsible for causing this: #607238: Permissions are assumed to be unique among modules, but uniqueness is not enforced

This is definitely still an issue when enabling a feature during profile installation that includes a vocabulary, and the corresponding permission. The patch in #607238: Permissions are assumed to be unique among modules, but uniqueness is not enforced does not appear to resolve this.

I think this issue is related: #1265168: Rebuild the file list properly when a feature is enabled or disabled.

@jhedstrom it does, the export is fine, but enabling can be a problem.

Looks like #981248: Allow a feature to execute a secondary install function after features components are created is related as well, the problem appears to be caching, but it isn't in the end ...

Changing title, it's not related to adding user role at all. That's due to the permission being a dynamic one, that rely on other modules doing other stuff, in this case a taxonomy vocabulary.

This error also happens when:
- a view in the feature checks for a permission defined in "module A"
- "module A" is not enabled on the target site (features does not auto-detect this dependency)

Fixing this situation was as easy as adding "module A" to the dependency list manually.

I've seen this error:

Failed: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module' cannot be null: INSERT INTO {role_permission} (rid, permission, module) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => 4 [:db_insert_placeholder_1] => administer google analytics [:db_insert_placeholder_2] => ) in user_role_grant_permissions() (line 3028 of ...

We found out about the error:
1) make a feature
2) enable the feature
3) Now the feature gets updated and needs another module (in my case googleanalytics)
4) in update.php there was an update hook to enable the module & in the feature I had permissions sitting ready to get configured on feature revert
5) in the update hook before the one that enabled googleanalytics a drupal_flush_all_caches was done. Apparently the flush cache started reverting the feature before the module got enabled. Thus resulting in this error.

I hope this can help others in the same situation!

This also happens when you try to install a feature that defines permissions for a field that is provided by another module. In my case, I have a feature that defines permissions for the OG group_audience field (provided by OG). This is a problem when trying to install this feature as part of an installation profile run from drush site-install.

Had the same issue.

Looking at the current features release, the module key is added to the permission.

However, when no roles as assigned to the permission, the module key is not added.

Here's a small patch that adds the 'module' key to the exported permission data, even when the permission has no roles assigned.

DT

How about this instead? No reason to have same line twice.

Removed the isset part -- if the permission doesn't exist, it shouldn't be rendered, which would be a separate bug if it's happening.

Committed to 4ad96ab

I am still getting this error, in beta6 and in the latest dev. I appears when I try to activate the Feature (with drush) that contains the panelizer permissions.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module' cannot be null: INSERT INTO {role_permission} (rid, permission, module) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array
(
    [:db_insert_placeholder_0] => 4
    [:db_insert_placeholder_1] => administer panelizer node section choice
    [:db_insert_placeholder_2] => 
)
 in user_role_grant_permissions() (line 3030 of /xxx/modules/user/user.module).

I do not get it for other modules, just Panelizer.

Are you sure you have Panelizer listed as a dependency for your feature?
Does panelizer_permission() return an array with "administer panelizer node section choice" as one of the keys?

Yes, panelizer is listed as an dependecy, I will get back on what panelizer_permission() returns.

Yes it do return that:

   [administer panelizer node section choice] => Array
        (
            [title] => <em class="placeholder">Nod</em> <em class="placeholder">Section</em>: Choose panels
            [description] => Allows the user to choose which default panel the entity uses.
        )

Does it work if you enable the feature through the site rather than drush?
Is it part of an install profile? If so, you might want to check #1537838: Upgrading to 7.x-1.0-rc2 (from rc1) breaks taxonomy creation
Also check #1264716: Install fails when using automated install and #1265168: Rebuild the file list properly when a feature is enabled or disabled.

I have the same problem as @MiSc with panelizer. This is with a feature enabled as part of an install profile, using drush site-install. Will try looking through the above issues too.

We're seeing the same error trying to install a feature as part of an install profile. The system we're installing on is running features 7.x-1.0-rc3, although the feature was exported from an earlier release (rc2). In our case we the issue is caused trying to run the following query:

INSERT INTO role_permission (rid, permission, module) VALUES ('1', 'view file', NULL)

The 'view file' permission is provided by the file_entity module, which is correctly identified as a dependency of the feature.

Curiously we only see the issue on some installation sites, other machines seem to be able to install the feature without error.

When running installation via drush, build-up of drupal static cache issues can be quite painful, since those caches aren't refreshed periodically as they are during a normal install through a browser.

The issue with panelizer and permissions is resolved with this patch, but there may be a less heavy-handed way to solve it.

Note that specifically clearing the user_role_permissions static cache is not enough, since in the case of panelizer, it's a lower level static cache causing the problem.

I can confirm that the drupal_static_reset(); added in #27 solves an issue I was having with exporting bean permissions within an installation profile.

When adding the patch in #27, it does solve the integrity constraint violation, it makes the admin/structure/features page completely break and appear unthemed. As much as I hate to do this, I have to set this back to needs work.

I got this today..

I got an updated feature from a dev for our module that tracks ONLY installed modules and permission settings. It should have turned on profile2 and set the permissions appropriately but instead we got an SQL fail.

I suspect however in my case that not all settings required from profile2 were exported and therefore there are missing permissions that would normally apply to the new 'profile2 content setups'.

I figured I'd give an update on what I've done to bypass this issue. I've personally stopped using features to manage my users and roles and am instead using http://drupal.org/project/secure_permissions with great success. The simple concept of truly locking down the permissions system was something I needed and the bugs exposed by this issue led me to trying alternative solutions. Now several weeks into the switch, I can say that I'm very happy with this new approach.

Secure Permissions seems interesting. In the meantime, I was able to use the patch from #27 and by moving the call to drupal_static_reset() from _user_features_get_permissions() to user_permission_features_rebuild() I was able to remove the side-effects on the features overview page.

I have solved this by putting all my user permissions in a separate Feature and making this dependent on the feature that creates the user roles. This ensures that the user roles are created before the user permissions are set.

The patch in #16 that was committed as noted in #18 does not handle the case where features tries to insert records for permissions that do not exist, nor do any of the other proposed solutions thus far. Below are steps to reproduce the original error using only core modules and the Features module:

1. Create a new feature from a live site containing at least two vocabularies
2. Add only one vocabulary to the feature where it's vid >= 2
3. Add the "edit terms in vocabualry" and "delete terms in vocabulary" to the feature
   Note: These permissions are stored in the feature as "edit terms in vid" and "delete terms in vid".
4. Install the feature on a fresh Drupal 7 Minimal install
5. BUG: When the feature creates the vocabulary, it is assigned the vid "1" but the permissions are for a different vid

I propose creating a hook that can be implemented by modules to convert permissions into better identifiable strings. The Features module would implement this on behalf of all core modules. In the example above, rather than features storing "edit terms in vid", it would store "edit terms in machine_name".

For dealing with the issue of the vocabulary permissions being exported using the vid instead of machine_name, please use this issue: #1722524: Export vocabulary permissions using the machine name

For the original issue in this thread, let's stay focused on the issue with the cache clear and the patch in #32.

I wonder if just resetting the static cache was enough or whether we should actually test to see if a permission exists before setting it in Features. Here is a patch that checks for the existence of a permission before setting it. Does this get closer to the main issue?

-edited:-- DON'T USE THIS...Prevents any of your exported user permissions from importing ---

I'm getting #35 would address permissions set by dependencies, but what happens if the permissions are defined in the same module as the feature itself?

Hmm, very true. Ignore the patch in #35.

So let's go back to just the static_cache clear. Does #32 work for everybody?

subscribing

I have applied patch #32 and this worked for me. I was able to export panelizer permissions as well as taxonomy permissions with no errors.

Patch in #32 did not work for me. Trying to set up a CKEditor permission in the same feature that enables CKEditor:

WD php: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module' cannot be null: INSERT INTO {role_permission} (rid, permission, module) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array                                                                                               [error]
(
    [:db_insert_placeholder_0] => 3
    [:db_insert_placeholder_1] => allow CKFinder file uploads
    [:db_insert_placeholder_2] => 
)
 in user_role_grant_permissions() (line 3043 of /sandbox/d7/modules/user/user.module).

We enable a number of features for all new sites in our university deployment, so I overcame this issue by setting all permissions in the last feature enabled in the stack. I found it helpful to have a final feature at the end to overcome pesky settings or run custom code.

Patch #32 did work for me.

#32 works for me.

#32 does not work for me.
I'm using Drupal 7.16, Rules 7.x-2.2, Features 7.x-1.0 and Recruiter_features 7.x-rc1.

My issues come when enabling modules that contain Rules from the Recruiter_features project.

It seems that module installation fails when Drupal tries to enable new permissions for the Administrator role. The SQL queries fail when Drupal doesn't provide the module name that's required by the role_permission table. Role 3 is the default Administrator role and access rules link job_unpublish is a Role that apparently was exported using Features from the Rules module.

It seems to me that the exported Features do not provide everything that Rules is looking for.

Notice: Undefined index: access rules link job_unpublish in user_role_grant_permissions() (line 3041 of /webserver/quick-drupal-20121023071359/drupal/modules/user/user.module).
PDOException: SQLSTATE[23000]: Integrity constraint violation: 19 role_permission.module may not be NULL: INSERT INTO {role_permission} (rid, permission, module) VALUES (?, ?, ?); Array ( [0] => 3 [1] => access rules link job_unpublish [2] => ) in user_role_grant_permissions() (line 3043 of /webserver/quick-drupal-20121023071359/drupal/modules/user/user.module).

Wonder if that's specific to Rules. Rules is responsible for the export (Features just calls it's hook), so if it doesn't have everything it needs then that seems like a Rules issue. Can some other people try Rules with this?

@mpotter - I should be able to experiment a bit in a few days, though hope somebody also has time to look at it right now.

Can also confirm that #32 works for me.

This came up in another issue.

I never thought that it might be a problem with drush.

http://drupal.org/node/1822930#comment-6670338

Patch from #32 also works for the 7.x-2.x branch but I can't see a version tag for 7.x-2.x.

#32 didn't helped in my case.
The feature contains the export of an OAuth connector and its associated permission.
I think that feature tries to enable permission before importing the connector, thus tries to enable un unexistant permission.
The 'module' key seems correct, BTW.

It seems like a chicken and egg problem for me.
In my case I'm using the field_permissions module. I was trying to add the new permissions via a feature. Deploying to staging broke everything. I think using a module like this is a very simple and easy method to replicate the problem. Just make sure the permission doesn't already exist in any form on the target test site.

In my testing, none of the patches (IN THE ENTIRE WORLD) worked... so sad. I had to hack core for a temp fix. See the code below. Note that this will only work if you have a problem with one particular module adding the permissions and should be removed when not needed.

function user_role_grant_permissions($rid, array $permissions = array()) {
  $modules = user_permission_get_modules();
  // Grant new permissions for the role.
  foreach ($permissions as $name) {
    // !DD TODO : remove this hack that helps features set permissions properly for new permissions that didn't already exist in the DB.
    if(is_null($modules[$name])) {
      $modules[$name] = 'field_permissions';
    }
    db_merge('role_permission')
      ->key(array(
        'rid' => $rid,
        'permission' => $name,
      ))
      ->fields(array(
        'module' => $modules[$name],
      ))
      ->execute();
  }

  // Clear the user access cache.
  drupal_static_reset('user_access');
  drupal_static_reset('user_role_permissions');
}

Note that user_permission_get_modules() fails to return the module that implements the permissions. Perhaps we should be starting here and working backwards?

Upping to major because the only solution seems to be to hack core and no other features can be used or worked on when this PDO Exception is thrown.

FYI my version is 7.x-1.0+22-dev.

A few seemingly related issues:
#1187858: Node permissions fail to import
#1337066: Allow exporting permissions without forcing dependencies in D7

#32 fixes my problem, many thanks.

Pardon my comment if this has already been said in a way I am not aware of - I'm a newbie to Drupal programming.

I have the same issue as mentioned above with the field_permissions module. I have not applied any patches, but when I look at the code that the features module creates it looks strange. I have created a role student that sits between an authenticated user and the administrator. My feature will do two things. It will take core module and assign permissions and it will take the field permissions on custom content types and push it to code. In the case of core modules the features code will produce an array with the student role (as well as the others as defined through the interface). So no issue with that. But the code for the field_permissions stuff lacks the student role. Why would features treat the field_permissions module differently? When I put the roles in accordingly into the module and test I still get the same issue.

Finally, if I am to apply #32 for testing do I apply #27 first?

Is anyone looking into this? I am investigating and have found that this works, but it depends on the server environment. This works fine on my local Linux box, but get the error above in my vhost/cpanel environment. I'm not an apache expert so I wanted to throw this out there and see if this might spark something.

See my posts #54 and #55 to follow what I am doing. The issue is in the core function user_permission_get_modules(). This function will produce an array with keys of the permission and value of the module it belongs to. As my previous post noted, I had two different environments - one where it was working and the other where it wasn't. The issue is this: if you have not gone through the interface and selected a permission then it will not show up in user_permission_get_modules(). However, once you do this then exporting the features will work. You should be able to verify this by looking in two test environments and go to administration>>people. In the environment where it is working you will see the permission you want to set. In the environment where it is not working you will not see the permission you want to set. So this is not really an issue with the features module, but seems to be an issue in getting all of the permissions seen by user_permission_get_modules(). After I set the initial permission in the environment where it did not work I now see the permissions I want to set in both administration>>people and user_permission_get_modules(). Doing drush features-revert now works and does not get the error we are discussing. This makes sense on why we get a complaint about null values - because error user_permission_get_modules() isn't returning the permission we are trying to change!

So how do we make sure all of the permissions available to us is available through user_permission_get_modules() without going through the interface and configuring it first?

I'm having the same problem, but I can verify that the permission in question looks to be properly included in the exported Feature:

  // Exported permission: create field_organizational_role.
  $permissions['create field_organizational_role'] = array(
    'name' => 'create field_organizational_role',
    'roles' => array(
      'Site Admin' => 'Site Admin',
    ),
    'module' => 'field_permissions',
  );

With this I get the following PDO exception:
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module' cannot be null: INSERT INTO {role_permission} (rid, permission, module) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => 4 [:db_insert_placeholder_1] => edit field_organizational_role [:db_insert_placeholder_2] => )

For some reason Features seems to be dropping the module name in this instance. Any ideas why?

I think it's because that module doesn't exist/hasn't been enabled at that point. I feel like at the load point, features checks for the module and if it ain't there it puts in a null or blank value.

This is not so easy to solve in a patch, but if you understand the problem you can work around it easily and reliably. You have to make sure that the module and components for which you are setting permissions are already enabled and reverted at the moment the permissions are being reverted.

Imagine you have a feature that defines a taxonomy vocabulary as well as the permissions that go with the vocabulary. If Features tries to revert the permissions before the vocabulary is created it tries to save a NULL value for the vocabulary id and this causes an integrity constraint violation. The solution is to revert the vocabulary first, so in your update hook you would do something similar to this:

function mymodule_update_7057() {
  // Refresh the feature before reverting.
  features_get_features('myfeature', TRUE);

  // Make sure the vocabulary exists by reverting the taxonomy component.
  features_revert(array('myfeature' => array('taxonomy')));

  // Now you can safely revert the permissions without causing integrity constraint violations.
  features_revert(array('myfeature' => array('user_permission')));
}

Similarly, if you are trying to set permissions for a particular module you would first make sure the module is enabled before attempting to revert the permissions.

Here is a teaser of my feature :

dependencies[] = bean
features[bean_type][] = simple
features[ctools][] = bean_admin_ui:bean:5
features[user_permission][] = view any simple bean

This feature is included in an install profile as a dependency.
When installing drupal using this profile I got the following error :

WD php: PDOException: SQLSTATE[23000]: Integrity constraint 
 violation: 1048 Column 'module' cannot be null: INSERT INTO
{role_permission} (rid, permission, module) VALUES
(:db_insert_placeholder_0, :db_insert_placeholder_1,
:db_insert_placeholder_2); Array
(
    [:db_insert_placeholder_0] => 1
    [:db_insert_placeholder_1] => view any simple bean
    [:db_insert_placeholder_2] =>
)
 in user_role_grant_permissions() (line 3054 of [path to drupal]\modules\user\user.module).

It looks like if permissions were reverted before any other component (including dependencies)

@DuaelFr: I have fixed some problems with dependencies in install profiles in #1265168: Rebuild the file list properly when a feature is enabled or disabled. Could you perhaps try out the patch in comment #36 and see if this solves your issue?

Thank you Pieter but this had changed anything.

I tried to patch features to force user_permission to always be reverted at the end (by moving it in the components arrays) but it didn't work .

What is quite stunning is that I made some tests by putting traces in features.module at the line 889 and it seems that _features_restore is properly called for all my features then, after that everything is done, the error comes out.

Feel free to ask me whatever you need. I am a developper too so I can understand technical needs.

Ok I found something digging into all this stuff.

Features is calling the core to get the entire list of existing permissions so it will invoke all the hook_permissions.
My problem here is that Bean use drupal_static and get_cache to keep this data in memory (see bean_fetch_plugin_info).

I tried to find a good place to force it to reset using cache_clear_all, drupal_static_reset or even bean_reset but without success.

I posted a related issue in the Bean issue list hoping someone will find a solution. #1897834: Bean, Features and permissions (The Beauty and the Cache)
In parallel, I suggested a patch to the core to avoid this PDOException to be thrown because it prevents other processes to work properly. #737816-26: user_role_grant_permissions() throws PDOException when used with non-existent permissions (e.g. a permission for a disabled/uninstalled module)

the core patch linked in #63 prevents this error during the installation process for the Recruiter distribution, see #1905464: Upgrade failure - Integrity constraint violation: 1048 Column 'module' cannot be null

Hi,

I have used this patch to at least let me know what the offending permission is, which works better for me than throwing an Exception.

Cheers,

Albert

UPDATE: Wrote too soon, this didn't work. :/

I had a similar problem where I had an existing feature (version 1), I then added new Taxonomy vocabularies along with permissions settings for the vocabularies to the feature (version 2). I then tried to update the Feature on a deployment that was using version 1 of the feature. This produced the constraint violation error, I presume because Features was trying to apply permissions to the vocabularies that didn't exist yet, due to order execution??

Anyway, my solution was simply to uninstall the module for the feature, and then reinstall (using the Drupal module interface). Doing this appears to cause the feature to be updated in the correct execution order so that the constrain violation doesn't occur.

Worked for me anyway!

I've applied #65 patch on 7.x-2.0-dev, and it works well for me.
Very simple and elegant fix. Thank you!

#65 does not solve the issue, it only replaces the exception with a message. The permission is still not working.

The patch also wouldn't solve the fact that taxonomy permissions are based on the vid and not machine name. That causes a fatal error for me every time, because the vid could change depending on how you're utilizing Features.

Please everyone be clear about the scope and nonscope of this issue.
In my view:
* the scope is to prevent a site from breaking when a feature contains a nondefined permission

This is reached with #65 and reduces big PITA for many.

Everything else should be a followup issue.

In which case I agree #65 does prevent the fatal errors, and is a sensible change. Helped me get through Features development without stopping on a broken permission.

Still, it would be nice to have some kind of "best practice" in order to prevent these fatal errors, not only a catch.

As mentioned above: putting all permissions in one feature and making it dependant on the one which enables all modules, fields, etc. for these permissions might do the trick? Or doesn't this change the execution order when reverting?

there's an alternate patch for this issue over at #1975512: Installing user permission feature when module not available crashes build that checks if there is a module for the permission and sets an error if so. That seems like a better approach then catching a sql error.

+1 for #73

also that patch has a better understandable message which might save us some 100s of support requests...

I just realized that I am getting this error because Features is importing permissions before it has imported the entity bundles the permissions are for - in my case, Registration and RedHen.

Those modules are dependencies in the Feature and -do- get enabled. The problem comes when the permissions are set, the exported bundles I have in that Feature do not yet exist, and therefore, the permission is not able to be tied to a module and throws this error. That's because the bundles exist in code with hook_(entity hook_default_info() style definition hooks.

The only way around this was moving those permissions into the Feature installer for me in hook_enable().

@kevinquillen: Could you try #1983726: Implement hook_features_rebuild ?

Let me try that.

CNW per #73 & #74.

Also, not postponing for now, but we might want to wait and see whether Core leaves responsibility on the caller's side or takes it over to callee's side, see: does in #737816: user_role_grant_permissions() throws PDOException when used with non-existent permissions (e.g. a permission for a disabled/uninstalled module)...

I don't think we might wait for this issue to be solved because if the core patch is committed it will not really help us. In fact it would just avoid the Exception to be thrown but it would not add the permission.

I think permissions are a really special component and we should consider to force it to be the last one reverted/rebuilt.
What do you think about it ?

If a maintainer can take a look at this I don't understand why the tests are failing. Thanks.

There's other issues open for providing more robust way to specificy order; please see those issues.

care to elaborate? which other issues? did you mean #1975512: Installing user permission feature when module not available crashes build ?

I can confirm that the patch in #79 solves the issue for me .. i can try to fix the tests, if I know that this is not a waste of time. so please elaborate on whether the community is going to use this fix or some other. thanks.

Yes, #1975512: Installing user permission feature when module not available crashes build is the correct place for this. Only reopen this if the other issue patch doesn't fix the problem.

I'm experiencing an issue similar to @kevinquillen in #75. The patch in #1983726: Implement hook_features_rebuild did not resolve the issue and with it I still get warnings like Warning in features rebuild of sac_deal. No module defines permission "update sac_deals_industry entityqueue" on install and no permissions configured. The patch in #79 does partially resolve the issue as it forces the permissions to be rebuilt last. I say partially because instead of 2 copies of the warning for each permission I only see one with the patch applied.

It still seems to revolve around certain permissions being exported for modules that are either not getting enabled first, or not removed from the Feature itself. The patch used to help, but it still can happen.

In my instance, the issue was actually caused by the entityqueue module not properly clearing out some caches in hook_permission. See #2165095-1: "No module defines permission ..." warning when a feature is enabled that defines an entityqueue and its permissions

Re issue #79 - the rebuild function removes the 'user_permission' item out of the components array, and then adds $key (i.e. the key of the value, for example 99) back into the array. As a result, featurized permissions do not rebuild properly with that patch installed. A simple fix would be to add the string 'user_permission' back in, rather than $key.

https://drupal.org/node/1997412 seems to be a fork of this issue and it includes a patch which makes the change recommended in #89 https://drupal.org/files/issues/1997412-features-component-order.patch

This issue is a mess/long/back and fourthy, please look at the other related issues or open a new issue with what the exact problem... or at least update the issue title/summery to match the problem.