JIRA 7 bug JRA-59364 - Getting issue details... STATUS causes most 'Created vs. Resolved Chart' gadgets on dashboards to break:

Broken portlets can be fixed individually by editing them and setting Collection Operation to Cumulative:

However only the dashboard owner can do this, and it's not exactly intuitive.

This page describes an ugly, painful process whereby a luckless JIRA administrator can identify broken portlets, log in as each affected user and fix them. It requires root access to the JIRA installation, and the ability to run SQL against the JIRA database.

Step 0: what's going on?

Each gadget has key:value parameters stored in the database. The 'fix' process described above results in the portlet gaining a few parameters, notably operation = cumulative:

Since there's more than operation = cumulative being added, this doesn't look like something we can fix by hand with SQL. We'll have to go through JIRA.

Step 1: identify dashboards with broken portlets

In the JIRA database:

• The portalpage table stores users dashboard
• The portletconfiguration table stores individual portlets on dashboards
• The gadgetuserpreference table stores portlet configuration details

The bug affects portlet that have a user preference of isCumulative = true but don't have the preference operation = cumulative. So we could identify affected dashboards with the SQL (mysql CONCAT function used to join strings):

SELECT concat('https://jira.example.com/secure/Dashboard.jspa?selectPageId=', portalpage.id)
FROM portalpage
	JOIN portletconfiguration ON portalpage.id=portletconfiguration.portalpage
	JOIN gadgetuserpreference ON portletconfiguration.id=gadgetuserpreference.portletconfiguration
WHERE gadgetuserpreference.userprefkey='isCumulative' AND gadgetuserpreference.userprefvalue='true'
	AND NOT EXISTS (SELECT * FROM gadgetuserpreference g WHERE g.portletconfiguration=portletconfiguration.id AND userprefkey='operation' AND userprefvalue='cumulative') ;

This gives one a list of URLs of problematic dashboards:

...
| https://jira.example.com/secure/Dashboard.jspa?selectPageId=10050                     |
| https://jira.example.com/secure/Dashboard.jspa?selectPageId=10086                     |
| https://jira.example.com/secure/Dashboard.jspa?selectPageId=10087                     |
| https://jira.example.com/secure/Dashboard.jspa?selectPageId=10131                     |
...

That's all very well, but a) my JIRA username doesn't have access to see private dashboards, b) only the creator can edit a portlet to fix it.

Step 2: install a user switcher

To be able to fix the broken portlets we've identified, we need to be able to temporarily assume the identity of users.

To do so:

• download the user.jsp file and put it in your JIRA's /opt/atlassian/jira/atlassian-jira/ directory
• customize user.jsp and set the password to something random:
  
• fire up Chrome
• install the Chrome modheader extension (I'd love to use Firefox but don't know of an equivalent).Customize the modheader extension to send an X-JIRA-Userswitcher HTTP header with your password, when user.jsp (with any parameters) is requested:
  
• Visit user.jsp in Chrome (https://jira.example.com/user.jsp in this example). You should see the message No such user: null
• Test the user switcher by adding a user=... paramter. For instance to become admin, hit https://jira.example.com/user.jsp?user=admin
• Test adding a lastPage parameter to have user.jsp redirect you somewhere. E.g. https://jira.example.com/user.jsp?user=admin&lastPage=/secure/Dashboard.jspa
  
  

Step 2: Fix each portlet

We now have the means to log in as a user, view the broken dashboard and fix it.

Here is a variant of the SQL from step 1 which prints a user.jsp URL:

SELECT distinct concat('https://jira.example.com/user.jsp?lastPage=/secure/Dashboard.jspa?selectPageId=', portalpage.id, '&user=',  app_user.lower_user_name)
FROM gadgetuserpreference g JOIN portletconfiguration p ON g.PORTLETCONFIGURATION = p.ID
	JOIN portalpage on p.portalpage=portalpage.id
	JOIN cwd_user ON portalpage.username=cwd_user.user_name
	JOIN app_user ON cwd_user.user_name=app_user.user_key
WHERE p.GADGET_XML like '%createdvsresolved-gadget.xml' AND g.USERPREFKEY='isCumulative' AND g.USERPREFVALUE='true'
	AND NOT EXISTS (select * from gadgetuserpreference WHERE portletconfiguration=p.ID AND USERPREFKEY='operation' AND USERPREFVALUE='cumulative');

For each URL:

• Open in a browser
• Find the broken portlet:
  
• Click 'Edit' in the top-right
  
• Change the Collection Operation from 'count' to 'Cumulative':
  
  

You can re-run the SQL query, whose results should get shorter after each gadget is fixed.

Uninstall user.jsp

Log out (of whichever user you last became) and back in as yourself.

Finally, uninstall user.jsp, as it represents a cross-site-scripting attack vector to yourself.