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 Server	Assignee:	Nobody <nobody>
Status:	NEW ---	QA Contact:
Severity:	high	Docs Contact:
Priority:	medium
Version:	4.2	CC:	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:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Tom Fonteyne 2012-01-09 14:56:00 UTC

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 17:01:15 UTC

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 20:38:13 UTC

consider making role read-only