Bug 772661

Summary: upgrade from JON 2.4.1 to 3.0.0 will fail if there is no role with id==2
Product: [Other] RHQ Project Reporter: Tom Fonteyne <tfonteyn>
Component: Core ServerAssignee: RHQ Project Maintainer <rhq-maint>
Status: NEW --- QA Contact: Mike Foley <mfoley>
Severity: high Docs Contact:
Priority: medium    
Version: 4.2CC: hrupp
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: All   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:

Description Tom Fonteyne 2012-01-09 09:56:00 EST
Description of problem:
upgrade from JON 2.4.1 to 3.0.0 will fail if there is no role with id==2

Version-Release number of selected component (if applicable):


How reproducible:
always

Steps to Reproduce:
1. have standard JON 2.4.1 installation
2. Remove the role "All Resources Role" which in table RHQ_ROLE has an id==2
3. upgrade to JON 3.0.0
  
Actual results:

 Cause: /opt/jboss/jon-server-3.0.0.GA/logs/db-upgrade.xml:27: Failed to upgrade - error in spec version [2.94]. Cause: Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_DirectSQL] in schema spec version [2.94]. Cause: The schema spec task [DirectSQL] has encountered an error. Cause: org.postgresql.util.PSQLException: ERROR: insert or update on table "rhq_permission" violates foreign key constraint "rhq_permission_role_id_fkey"
  Detail: Key (role_id)=(2) is not present in table "rhq_role". 

Expected results:

that it works

Additional info:
modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml

3203   <schemaSpec version="2.94">
..
3214  <statement desc="Inserting MANAGE_EVENTS permission for 'All Resources' role">
3215       INSERT INTO RHQ_PERMISSION (ROLE_ID, OPERATION)
3216       VALUES (2, 14)
3217  </statement>

Will fail if the role with id==2 does not exist

This could/would also happen here:
2987     <schemaSpec version="2.86">
...
2992     <statement desc="Adding MANAGE_BUNDLE permission to superuser">
2993            INSERT INTO RHQ_PERMISSION VALUES(2, 12)
2994     </statement>
Comment 1 Tom Fonteyne 2012-01-09 12:01:15 EST
workaround: recreate the role with:

INSERT INTO rhq_role
(id, "name", description, fsystem)
VALUES
(2, 'All Resources Role', 'Provides full access to all resources in 
inventory. Cannot modify users, roles or server settings.', false);


-- Global: INVENTORY, BUNDLE
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 1);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 12);

--- Resource: All except for MANAGE_EVENTS, added in 2.94
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 3);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 4);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 5);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 6);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 7);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 8);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 9);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 10);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 11);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 13);
INSERT INTO rhq_permission (role_id, operation) VALUES (2, 16);
Comment 2 Mike Foley 2012-01-16 15:38:13 EST
consider making role read-only