UR schedule report issue

Document ID : KB000091856
Last Modified Date : 04/05/2018
Show Technical Document Details
Issue:
No problem in creating the existing report. but when we do the schedule report. Received an error as below when click on Next in 'Set the Parameter Values' stage

The error is 

org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole#8]
Environment:
UIM version 8.51
UMP version 8.51
UR version 8.51 (upgraded from 8.50)
Cause:
There are caused by the process when upgrading UR from 8.50 to 8.51 that table JIRole is wrongly truncated and rows in table JIObjectPermission are left with orphaned data in column recipientobjectid.

Verify this by running
Q1:
select * from JIObjectPermission op 
where not exists ( 
select 1 from JIRole r where r.id = op.recipientobjectid 
); 

this returns a list of recipientobjectid in table JIObjectPermission and these should have been role ids in table JIRole . In this case 'com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole#8' indicates that '8' is a role id that should have been in table JIRole.

 
Resolution:
0. Make sure you have valid full backup of db unifiedreporter

1. Deactivate jasperserver-pro webapp
- Open AC as administrator
- Configure wasp probe on UMP server
- Select 'Stop Webapp' from context button of jasperserver-pro
- Wait the jasperserver-pro changes to stop icon

2. run below query in SSMS or in SLM portlet to update permission that caused this error, below is an example:

USE <UR_DB>
update JIObjectPermission set recipientobjectid = 2, permissionMask = 30 where id IN (32,44,57,10069,20100,30100);
delete from JIObjectPermission where id in (10070);

-- Replace list of id in where clause of above update statement with your values, the list of values are ids of rows to be updated and removed in table JIObjectPermission. You get this list from Q1 for orphaned role id in your database.

-- '2 'represents the role id for 'ROLE_USER' in table JIRole, this means we want to update permissions for users with role 'ROLE_USER' on selected folder.

-- '30' represents the permission of 'Read+Write+Delete', this permission will be granted to user with role 'ROLE_USER' on the selected folder.

-- you can remove unnecessary permissions(rows) from table JIObjectPermission

3. Start jasperserver-pro
- Select 'Start Webapp' from context button of jasperserver-pro
- Wait the jasperserver-pro changes to start icon
- Click Discard button to discard any changes made in Wasp.

4. The issue can be fixed if Q1 returns an empty result set after update/delete. After that take another db backup and customer may need to adjust user permissions on folders with user data in UR if necessary.
Additional Information:
This issue also happened when navigating to a specific repository folder with corrupt permission data.