| Excerpt | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
JIRA 7 bug
Broken portlets can be fixed individually by editing them and setting
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
portalpagetable stores users dashboard - The
portletconfigurationtable stores individual portlets on dashboards - The
gadgetuserpreferencetable stores portlet configuration details
The bug affects portlet that have a user preference of isCumulative = true but don't have the newly introduced . So we could identify affected dashboards with the SQL (mysql operation parameterCONCAT function used to join strings):
a SQL fix to migrate all Created vs Resolved portlets to the new preference format. |
Table of Contents
| Table of Contents |
|---|
I haven't yet upgraded. Will I be affected?
If
| Jira | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Code Block | ||
|---|---|---|
jira=> SELECT DISTINCT portalpage.pagename AS "Dashboard",
concat('http | ||
| Code Block | ||
| sql | sql | SELECT concat('https://jira.example.com/secure/Dashboard.jspa?selectPageId=', portalpage.id) AS URL, app_user.lower_user_name AS "Owner" FROM portalpage JOIN portletconfiguration ON portalpage.id=portletconfiguration.portalpage JOIN gadgetuserpreferencecwd_user ON portletconfigurationportalpage.idusername=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') ; |
This gives one a list of URLs of problematic dashboards:
| Code Block |
|---|
... | https://jira.example.com/secure/Dashboard.jspa?selectPageId=10050cwd_user.user_name JOIN app_user ON cwd_user.user_name=app_user.user_key WHERE portletconfiguration.gadget_xml ~'createdvsresolved-gadget.xml'; ┌─────────────────────┬──────────────────────────────────────────────────────────────────┬─────────┐ │ Dashboard │ url | | https://jira.example.com/secure/Dashboard.jspa?selectPageId=10086 | | https://jira.example.com/secure/Dashboard.jspa?selectPageId=10087 │ Owner │ ├─────────────────────┼──────────────────────────────────────────────────────────────────┼─────────┤ │ My Custom Dashboard | | https│ http://jira.example.com/secure/Dashboard.jspa?selectPageId=1013110200 │ jturner | ... |
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:
...
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');│
└─────────────────────┴──────────────────────────────────────────────────────────────────┴─────────┘
(1 row) |
How do I fix it?
With JIRA shut down, ***BACK UP YOUR JIRA DATABASE***, and then run this SQL:
| Code Block |
|---|
CREATE TEMP SEQUENCE prefid;
SELECT setval('prefid', max(id)::integer + 1) FROM gadgetuserpreference;
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs),
newvals AS (select nextval('prefid') AS ID,
portletconfiguration,
t.userprefkey,
CASE t.userprefkey
WHEN 'operation' THEN (CASE prefs.isCumulative WHEN 'true' THEN 'cumulative' ELSE 'count' END)
WHEN 'id' THEN prefs.id
WHEN 'name' THEN CASE type WHEN 'project' THEN (SELECT pname FROM project WHERE id=prefs.id::integer) WHEN 'filter' THEN prefs.projectOrFilterId END
WHEN 'type' THEN prefs.type
END AS userprefvalue
FROM prefs,
(VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey))
INSERT INTO gadgetuserpreference SELECT * FROM newvals;
UPDATE sequence_value_item SET seq_id = (SELECT ceil(max(id)/100)*100 FROM gadgetuserpreference) WHERE seq_name='GadgetUserPreference'; |
| Warning |
|---|
There may be Postgres'isms in this SQL. Proceed with caution. |
What's going on?
Each gadget has a bunch of parameters:
These parameters are saved in the gadgetuserpreferences database table.
In JIRA 7.x, Atlassian reimplemented the Created vs Resolved gadget, and changed the parameters. Here is a before/after diff on the params for a filter-using portlet (see Using database diffs to see what JIRA is doing for details on the technique):
and a project-using portlet:
Specifically, they introduced 4 new parameters, type, id, name and operation, whose values are derived from the old parameters.
Atlassian should have written an 'upgrade task' to derive these new parameters from the old, and delete the old (to avoid confusing future generations). That is the topic of
| Jira | ||||||||
|---|---|---|---|---|---|---|---|---|
|
But we can derive the new params ourselves with a bit of SQL.
Deriving the SQL Fix
Background: database tables involved in JIRA portlets
In the JIRA database:
- The
portalpagetable stores user's dashboards, e.g. "My Custom Dashboard". - The
portletconfigurationtable stores individual portlets on dashboards. E.g. it records that there are N gadgets on "My Custom Dashboard", as well as their position and type. - The
gadgetuserpreferencetable stores portlet configuration details. E.g. for a "Created vs Resolved" portlet it stores the filter ID the portlet displays data from.
| Database table | Sample data | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| portalpage |
| ||||||||||||||
| portletconfiguration |
| ||||||||||||||
| gadgetuserpreference |
|
SQL part 1: collecting relevant old-portlet data
Let us look at the newly introduced preferences in more detail:
The new prefs are:
-
typeisprojectorfilter, depending on the portlet's data source -
idis the ID of the filter or project -
nameis a human-readable project or filter name, where:- if type is a project, then name should be the project name, and is actually displayed in the portlet.
- if type is a filter, it doesn't really matter what name is – JIRA set it to
filter-xxxxxand doesn't use it.
-
operationhas valuecumulativeorcount
Recreating the missing parameters
Notice that all of the 4 new parameter values can be derived just by looking at the old projectOrFilterId and isCumulative parameters.
So first we write some SQL to print the projectOrFilterId and isCumulative parameters for each Created vs Resolved portlet:
| Code Block |
|---|
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration))
SELECT * From relevantprefs;
┌──────────────────────┬───────────────────┬──────────────┐
│ portletconfiguration │ projectorfilterid │ iscumulative │
├──────────────────────┼───────────────────┼──────────────┤
│ 10801 │ filter-10601 │ true │
│ 10803 │ filter-10601 │ false │
│ 10900 │ project-10800 │ true │
└──────────────────────┴───────────────────┴──────────────┘
(3 rows) |
The same, but using regexp_replace() to break projectorfilterid into its constituent parts:
| Code Block |
|---|
jira=> WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs)
SELECT * from prefs;
┌──────────────────────┬───────────────────┬─────────┬───────┬──────────────┐
│ portletconfiguration │ projectorfilterid │ type │ id │ iscumulative │
├──────────────────────┼───────────────────┼─────────┼───────┼──────────────┤
│ 10801 │ filter-10601 │ filter │ 10601 │ true │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │
│ 10900 │ project-10800 │ project │ 10800 │ true │
└──────────────────────┴───────────────────┴─────────┴───────┴──────────────┘
(3 rows) |
SQL part 2: inserting new rows
The trick here is generating 4 new rows for each matching row above. We can create a 'fake' table to cross-join with our relevantprefs using a VALUES clause:
| Code Block |
|---|
jira=> select * from (VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey);
┌─────────────┐
│ userprefkey │
├─────────────┤
│ name │
│ operation │
│ id │
│ type │
└─────────────┘
(4 rows) |
Then 4 new rows for each of our portlets yields:
| Code Block |
|---|
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs)
SELECT * from prefs,
(VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey);
┌──────────────────────┬───────────────────┬─────────┬───────┬──────────────┬─────────────┐
│ portletconfiguration │ projectorfilterid │ type │ id │ iscumulative │ userprefkey │
├──────────────────────┼───────────────────┼─────────┼───────┼──────────────┼─────────────┤
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ name │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ operation │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ id │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ type │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ name │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ operation │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ id │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ type │
│ 10900 │ project-10800 │ project │ 10800 │ true │ name │
│ 10900 │ project-10800 │ project │ 10800 │ true │ operation │
│ 10900 │ project-10800 │ project │ 10800 │ true │ id │
│ 10900 │ project-10800 │ project │ 10800 │ true │ type │
└──────────────────────┴───────────────────┴─────────┴───────┴──────────────┴─────────────┘
(12 rows) |
We now have a row containing all relevant old params, plus the new parameter name ( name, operation, id and type). Now we just insert a SELECT clause to derive the new userprefvalue from the old params:
| Code Block |
|---|
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs)
SELECT *, CASE t.userprefkey
WHEN 'operation' THEN (CASE prefs.isCumulative WHEN 'true' THEN 'cumulative' ELSE 'count' END)
WHEN 'id' THEN prefs.id
WHEN 'name' THEN CASE type WHEN 'project' THEN (SELECT pname FROM project WHERE id=prefs.id::integer) WHEN 'filter' THEN prefs.projectOrFilterId END
WHEN 'type' THEN prefs.type
END AS userprefvalue
FROM prefs,
(VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey);
┌──────────────────────┬───────────────────┬─────────┬───────┬──────────────┬─────────────┬───────────────┐
│ portletconfiguration │ projectorfilterid │ type │ id │ iscumulative │ userprefkey │ userprefvalue │
├──────────────────────┼───────────────────┼─────────┼───────┼──────────────┼─────────────┼───────────────┤
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ name │ filter-10601 │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ operation │ cumulative │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ id │ 10601 │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ type │ filter │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ name │ filter-10601 │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ operation │ count │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ id │ 10601 │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ type │ filter │
│ 10900 │ project-10800 │ project │ 10800 │ true │ name │ CrowdFlower │
│ 10900 │ project-10800 │ project │ 10800 │ true │ operation │ cumulative │
│ 10900 │ project-10800 │ project │ 10800 │ true │ id │ 10800 │
│ 10900 │ project-10800 │ project │ 10800 │ true │ type │ project │
└──────────────────────┴───────────────────┴─────────┴───────┴──────────────┴─────────────┴───────────────┘
(12 rows) |
Inserting data: dealing with IDs
We've successfully derived the new preferences needed. Now we just need to insert it into the gadgetuserpreferences table.
The gadgetuserpreferences table has an ID field which must be unique:
| Code Block |
|---|
redradish_jira=> \d gadgetuserpreference
Table "public.gadgetuserpreference"
┌──────────────────────┬────────────────────────┬───────────┐
│ Column │ Type │ Modifiers │
├──────────────────────┼────────────────────────┼───────────┤
│ id │ numeric(18,0) │ not null │
│ portletconfiguration │ numeric(18,0) │ │
│ userprefkey │ character varying(255) │ │
│ userprefvalue │ text │ │
└──────────────────────┴────────────────────────┴───────────┘
Indexes:
"pk_gadgetuserpreference" PRIMARY KEY, btree (id)
"userpref_portletconfiguration" btree (portletconfiguration) |
So we need to generate a sequence of IDs, starting at 1 more than the current max. This is done with a Postgres sequence:
| Code Block |
|---|
CREATE TEMP SEQUENCE prefid;
SELECT setval('prefid', max(id)::integer + 1) FROM gadgetuserpreference;
┌────────┐
│ setval │
├────────┤
│ 12712 │
└────────┘
(1 row) |
Now we add a nextval('prefid') SELECT clause to our SQL, and trim down the results to match the gadgetuserprefs columns:
| Code Block |
|---|
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs),
newvals AS (select nextval('prefid') AS ID,
portletconfiguration,
t.userprefkey,
CASE t.userprefkey
WHEN 'operation' THEN (CASE prefs.isCumulative WHEN 'true' THEN 'cumulative' ELSE 'count' END)
WHEN 'id' THEN prefs.id
WHEN 'name' THEN CASE type WHEN 'project' THEN (SELECT pname FROM project WHERE id=prefs.id::integer) WHEN 'filter' THEN prefs.projectOrFilterId END
WHEN 'type' THEN prefs.type
END AS userprefvalue
FROM prefs,
(VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey))
SELECT * FROM newvals;
┌───────┬──────────────────────┬─────────────┬───────────────┐
│ id │ portletconfiguration │ userprefkey │ userprefvalue │
├───────┼──────────────────────┼─────────────┼───────────────┤
│ 12725 │ 10801 │ name │ filter-10601 │
│ 12726 │ 10801 │ operation │ cumulative │
│ 12727 │ 10801 │ id │ 10601 │
│ 12728 │ 10801 │ type │ filter │
│ 12729 │ 10803 │ name │ filter-10601 │
│ 12730 │ 10803 │ operation │ count │
│ 12731 │ 10803 │ id │ 10601 │
│ 12732 │ 10803 │ type │ filter │
│ 12733 │ 10900 │ name │ CrowdFlower │
│ 12734 │ 10900 │ operation │ cumulative │
│ 12735 │ 10900 │ id │ 10800 │
│ 12736 │ 10900 │ type │ project │
└───────┴──────────────────────┴─────────────┴───────────────┘
(12 rows) |
And there we have exactly what we want to INSERT:
| Code Block |
|---|
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs),
newvals AS (select nextval('prefid') AS ID,
portletconfiguration,
t.userprefkey,
CASE t.userprefkey
WHEN 'operation' THEN (CASE prefs.isCumulative WHEN 'true' THEN 'cumulative' ELSE 'count' END)
WHEN 'id' THEN prefs.id
WHEN 'name' THEN CASE type WHEN 'project' THEN (SELECT pname FROM project WHERE id=prefs.id::integer) WHEN 'filter' THEN prefs.projectOrFilterId END
WHEN 'type' THEN prefs.type
END AS userprefvalue
FROM prefs,
(VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey))
INSERT INTO gadgetuserpreference SELECT * FROM newvals;
INSERT 0 12
Time: 30.402 ms |
Updating the sequence_value_item table
JIRA's database library (Ofbiz) has an odd way of tracking the maximum ID of a table. It has a table called sequence_value_item which stores the max ID of each table, rounded up to the nearest 100:
| Code Block |
|---|
SELECT max(id) FROM gadgetuserpreference;
┌───────┐
│ max │
├───────┤
│ 12711 │
└───────┘
(1 row)
SELECT * from sequence_value_item WHERE seq_name='GadgetUserPreference';
┌──────────────────────┬────────┐
│ seq_name │ seq_id │
├──────────────────────┼────────┤
│ GadgetUserPreference │ 12800 │
└──────────────────────┴────────┘
(1 row) |
So after manually inserting rows in a JIRA table, one must always update sequence_value_item. The new value can be derived from max(id) by a divide-then-round-up-then-multiple trick:
| Code Block |
|---|
UPDATE sequence_value_item SET seq_id = (SELECT ceil(max(id)/100)*100 FROM gadgetuserpreference) WHERE seq_name='GadgetUserPreference' RETURNING seq_id;
┌────────┐
│ seq_id │
├────────┤
│ 12800 │
└────────┘
(1 row) |
Shameless Plug
My business, Red Radish Consulting, offers a quarterly JIRA/Confluence upgrade service. The fix described here was derived on-the-fly for a client. Save yourself the hassle of dealing with exciting bugs like this, and contact us for a quote!
For each URL:
...
You can re-run the SQL query, whose results should get shorter after each gadget is fixed.
Step 3: 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.