Facing Issues after Upgrading to 2.0.2

[Pranjal Shukla]

I am facing 2 issues as of now with the upgrade.

1. The select query is jumbling up the columns and is not in the order i requested.
   1. for example my query is below
   2. select TOP 5 RELATED_CI_HINT as 'Hostname', COUNT(RELATED_CI_HINT) as 'Breach Count' from ALL_EVENTS where CATEGORY like 'OEM' and TITLE like '%CPU%' and SEVERITY = 'CRITICAL' and ALL_EVENTS.TIME_CREATED between GETDATE() -7 and GETDATE() GROUP BY RELATED_CI_HINT ORDER BY 'Breach Count' DESC;
   3. I am getting first column as "Breach Count" and Second Column as "Hostname", ideally it should be other way round.
2. The chart macro which was drawing it properly with version 1.14.8 has now started failing.

I had to revert the plugin back to make my page work.

[Felix Grund (Scandio)]

P3:

Also, i have another requirement wherein i have to embed nearly 50 queries for 50 different charts. If i do this, Confluence would die. I see Caching has been introduced in new version but first time load would still take time. My questions:

1. If i Cache a Query for 3 days, if the resultant data set changes after 1 hour of last/initial load, will the output at next load (page load where macro is placed) be the updated result or the cached data from first load.
2. Can we have a background caching mechanism? Can that be scheduled like after business hours when load on system is low or say weekends?

1.) When the query is executed, the cache is searched for the last execution by the tuple (queryName, queryParameters). If a result is found, it is checked how old the result is. If the result is older than your limit set on the query, the cache is invalidated and the query will be executed newly. So to answer your question: the result will be the one from the cache (first load). There is technically no possibility to implement this otherwise. The cache would have to be invalidated anytime the database contents change, which is an event not known to PocketQuery/Confluence.

2.) This is basically what is done by the PocketQuery Index job. If you enable indexing for your query, all pages in your Confluence with PocketQuery macros will be searched and indexed. For those queries with caching enabled, caching will be used, so also in this case, the result will be taken from the cache if it's not too old. So if you want to make sure the queries actually get executed by the index job, you'll have to clear the PocketQuery cache completely by hand before the job starts (say in the evening if the job runs at night). But if you set your caches to something like 3 hours, the caches will most probably be stale any time the index job runs. There is, however, a very important detail: the index job will run without user context. So if you cache results with the index job, your queries cannot use the wildcards @username, @userfullname or @usermail.

Let me know if you have further questions on this!

[Pranjal Shukla]

Let me make sure whether my understanding is correct:

• Indexing will index the pages for content searching.
• Since pages with PQ Macro generally have dynamic content (result from query execution than normal static content), the index job will execute the queries and then store the result for searching.
• If a Query has caching enabled, it's cached content will be indexed. If the cache is stale then index job will execute the query, store the result in the cache and then store that cache in index for searching.

If it is correct then i have some points below:

Can we have a dedicated cron job/scheduler/service like we have for Backups for refreshing the cache?

As said, some of my queries are taking way too long to execute. Due to network hops, a normal page with 5-6 queries are taking up to a minute to execute. Sometimes they time out. During the query execution (i.e. during page load where 5-6 PQ macros are placed), Heap Utilization shoots up. I am fearing that if 2-3 people hit the same page simultaneously, Confluence might run out of memory and crash.

Hence, would be good if we can have a dedicated job and i can schedule that job (cache refresh) during night time when there are hardly any users on the system.

[Pranjal Shukla]

OK Felix,

Did some workaround and made it to work. I did not change the macro definition on the page but fiddled with the Template

Created a Custom Template with the following code and it is working fine:

## @param plain:true
$PocketQuery.template("default")

Something needs to be done in the generated HTML.

[Felix Grund (Scandio)]

You used this template for this query?

SELECT "Herring" AS "FishType", 9500 AS "2010", 8300 AS "2011"

Can you please send me the generated HTML if you use the query without the chart macro? You can use your browser's developer tools or "view source" functionality.

[Pranjal Shukla]

Here is the HTML of the query i am executing. One point here, the Meta Tag is not being closed and that what is the error we are getting

<div class="pocketquery-view-container">
	 		<meta name="ajs-pq-OMI_TEST-allowgetparams" content="true">
	
<div class="pocketquery-result">
		<table class="aui confluenceTable pocketquery-table tablesorter tablesorter-default">
	<thead>
		<tr class="tablesorter-headerRow">
					<th class="col-1 tablesorter-header" data-column="0" tabindex="0" unselectable="on"><div class="tablesorter-header-inner">Hostname</div></th>
					<th class="col-2 tablesorter-header" data-column="1" tabindex="0" unselectable="on"><div class="tablesorter-header-inner">Breach Count</div></th>
				</tr>
	</thead>
	
	<tbody>
					<tr class="odd">
							<td class="col-1">Zone 5</td>
							<td class="col-2">15</td>
						</tr>
					<tr class="even">
							<td class="col-1">Zone 4</td>
							<td class="col-2">14</td>
						</tr>
					<tr class="odd">
							<td class="col-1">Zone 3</td>
							<td class="col-2">11</td>
						</tr>
					<tr class="even">
							<td class="col-1">Zone 2</td>
							<td class="col-2">11</td>
						</tr>
					<tr class="odd">
							<td class="col-1">Zone 1</td>
							<td class="col-2">9</td>
						</tr>
			</tbody>
</table>
	</div>
</div>

[Felix Grund (Scandio)]

Is this assumption correct:

• If you put this HTML code directly in your PQ template, the error occurs
• If you add </meta> after <meta name="ajs-pq-OMI_TEST-allowgetparams" content="true"> the error doesn't occur?

[Pranjal Shukla]

Since the above was causing problems i used another way of generating the charts. I used $PocketQuery.renderMacro() from the Velocity Helper in a custom template and it worked well for me. Since my query has Dynamic Parameters, i am able to give values (of Severity Field) on the fly and able to generate my charts. The parameters that are set in my macro definition are:

Load macro dynamically: TRUE

Include reload button: FALSE

Enable dynamic parameters: TRUE

Use change params template: TRUE

$PocketQuery.renderMacro("chart", {
  "page": $page,
  "parameters": { "title": "CPU Alerts (Threshold Breach)","subTitle":"Last 7 Days","width":"400","height":"400","dataOrientation":"vertical","legend":"true" },
  "body": "<table><thead><tr>#foreach ($column in $columns)<th>$!column</th>#end</tr></thead><tbody>#foreach ($row in $result)<tr>#foreach ($column in $row)<td>$!column</td>#end</tr>#end</tbody></table>"
})

Can you answer my question in this post? You can comeback to P3 and P4 after that.

[Felix Grund (Scandio)]

It is actually an important concern for me to fix this P2 first. Your solution is an interesting workaround. But I would definitely like to have this issue fixed properly (thanks for helping discover this, by the way!). Can you please reply on my last comment first?

[Pranjal Shukla]

Felix,

• If you put this HTML code directly in your PQ template, the error occurs (YES, THE ERROR STILL OCCOURS)
• If you add </meta> after <meta name="ajs-pq-OMI_TEST-allowgetparams" content="true"> the error doesn't occur? (YES, THE ERROR STILL OCCOURS)

[Pranjal Shukla]

Felix,

Here are the two outputs (HTML) when i use the Default Template and Custom Template with plain:true enabled

Default Template

<div class="pocketquery-view-container">
	 		<meta name="ajs-pq-OMI_TEST-allowgetparams" content="true">
	
		 
		
	
    	
	<div class="pocketquery-result">
		<table class="aui confluenceTable pocketquery-table tablesorter tablesorter-default">
	<thead>
		<tr class="tablesorter-headerRow">
					<th class="col-1 tablesorter-header" data-column="0" tabindex="0" unselectable="on"><div class="tablesorter-header-inner">Hostname</div></th>
					<th class="col-2 tablesorter-header" data-column="1" tabindex="0" unselectable="on"><div class="tablesorter-header-inner">Breach Count</div></th>
				</tr>
	</thead>
	
	<tbody>
					<tr class="odd">
							<td class="col-1">xl01cn02-z02</td>
							<td class="col-2">15</td>
						</tr>
					<tr class="even">
							<td class="col-1">N1VPAR2</td>
							<td class="col-2">14</td>
						</tr>
					<tr class="odd">
							<td class="col-1">xl01cn01-z02</td>
							<td class="col-2">11</td>
						</tr>
					<tr class="even">
							<td class="col-1">newp570LPAR4</td>
							<td class="col-2">10</td>
						</tr>
					<tr class="odd">
							<td class="col-1">IMASUNT54407-Zone1</td>
							<td class="col-2">9</td>
						</tr>
			</tbody>
</table>
	</div>
</div>

Custom Template with the following Code

## @param plain:true

$PocketQuery.template("default")

<table class="aui confluenceTable pocketquery-table tablesorter tablesorter-default">
	<thead>
		<tr class="tablesorter-headerRow">
					<th class="col-1 sortableHeader" data-column="0" tabindex="0" unselectable="on"><div class="tablesorter-header-inner">Hostname</div></th>
					<th class="col-2 sortableHeader" data-column="1" tabindex="0" unselectable="on"><div class="tablesorter-header-inner">Breach Count</div></th>
				</tr>
	</thead>
	
	<tbody>
					<tr class="odd">
							<td class="col-1">xl01cn02-z02</td>
							<td class="col-2">15</td>
						</tr>
					<tr class="even">
							<td class="col-1">N1VPAR2</td>
							<td class="col-2">14</td>
						</tr>
					<tr class="odd">
							<td class="col-1">xl01cn01-z02</td>
							<td class="col-2">11</td>
						</tr>
					<tr class="even">
							<td class="col-1">newp570LPAR4</td>
							<td class="col-2">10</td>
						</tr>
					<tr class="odd">
							<td class="col-1">Zone1</td>
							<td class="col-2">9</td>
						</tr>
			</tbody>
</table>

The main difference is those additional DIV tags in which the Table is getting encapsulated. Hope this will help you in 

[Felix Grund (Scandio)]

Hi Pranjal, I just started a local Confluence 5.8.6 system and I could reproduce the problem. It seems like in Confluence 5.9.x the Chart macro accepts non-closed <meta> tags (which it should, because they are in the Confluence source code all over the place). That said, I still want the Chart macro to work with PocketQuery on Confluence < 5.9. Could you help to check if this PocketQuery version solves your issue?

[Pranjal Shukla]

Works now...

[Pranjal Shukla]

Hi Felix,

Can i now have my other queries answered, specifically on Dynamic Parameters. Then you can answer P3 and P4 as i'll certainly need them in my upcoming requirements.

[Pranjal Shukla]

5.8.6

BTW, how can i get you the underlying HTML?

[Felix Grund (Scandio)]

What is your Confluence version?

[Pranjal Shukla]

Felix,

Unfortunately it's not coming up, neither with PIE nor with BAR chart. Irony is when i restore the old version of plugin, it works.

[Felix Grund (Scandio)]

Hi Pranjal,

I tried to use the following to reproduce this, but the Chart macro works perfectly for this case. Please try if it works for you too. It's a dummy query:

SELECT "Herring" AS "FishType", 9500 AS "2010", 8300 AS "2011"

I put the PQ macro for this query in a Chart macro with type bar chart. Does this work for you too?

[Pranjal Shukla]

Felix,

Yes i understand that you are doing this for free and let me tell you that this is a remarkable product and helping lot many organizations.

Coming to the template, i do not have a template for this and i am using a default template. Below is how my macro definition looks like:

image2016-2-23 16:16:38.png

Since the output of the query is in table format and Chart Macro takes input as table, it works well embedding the PQ macro in Chart macro.

Certainly there is a change in the generated HTML from the PQ macro as the same setup was working in the previous version but not now. Here is the error i get now.

Error rendering macro 'chart' : org.dom4j.DocumentException: Error on line 54 of document : The element type "meta" must be terminated by the matching end-tag "</meta>". Nested exception: The element type "meta" must be terminated by the matching end-tag "</meta>".

I am not sure how to get you the resulting HTML of the query, Could you please let me know how to get that?

[Felix Grund (Scandio)]

Hi Pranjal, I think we should fix the issues one by one, so I'll only come to P3 when we solved P2. Please remember that I'm doing this for free . Regarding that it worked with PQ 1.x and not with PQ 2.x suggests that some change in the PQ result HTML code changed in a way that the Chart Macro won't process it properly. The issue with the Chart macro is in general in the Chart macro itself and not in PQ. But I'll gladly try to help you and find the cause of this and work around it. For that, I'd need to know what the resulting HTML looks like. Do you have a custom template for this? I guess so. Could you somehow send me this template? I basically just need the structure so I can reproduce the issue with the Chart macro.

[Pranjal Shukla]

Felix,

It's not the preview mode, I am getting the same after saving the page. I am also trying to identify as to why i was not getting this error in the previous version of the plugin but getting it now. Strange.....if it doesn't work then i'll have to change at lot many places. The advantage with Confluence's chart macro is that many things like color, title, sub-title can be defined in the macro body. However, if i have to go with the PQ's template of Google Charts, i'll have to make multiple templates. An then managing templates would be difficult because i'd then have many many to manage.

If the case of requiring internet access still persists for google charts then i am afraid many people wouldn't see charts as many people do not have access to internet. The sole reason is we are a Bank and internet access is not available to everybody.

Could you also answer P3?

[Felix Grund (Scandio)]

P2 seems to be the same issue as we had before: see here. What kind of chart do you want to display? I would highly recommend to use the charting abilities of PQ itself over the chart macro.

[Pranjal Shukla]

Felix,

P1:

The P1 seems to be solved. I executed the query you gave and it gave me the right order. Then i executed my query which was not coming proper earlier also started coming as desired, hence it can be deemed as resolved.

---

P2:

I am still getting the below error on chart Macro:

Error rendering macro 'chart' : org.dom4j.DocumentException: Error on line 54 of document : The element type "meta" must be terminated by the matching end-tag "</meta>". Nested exception: The element type "meta" must be terminated by the matching end-tag "</meta>".

[Felix Grund (Scandio)]

Hi Pranjal!

So let me realign this a little. There are currently two problems (P1 and P2) with PQ 2.x and that's why you're still stuck on 1.x:

P1: column order in result is not as in Query SQL
P2: chart macro fails to draw the chart

Then, there is your issue with 50 queries in one page (P3). I think we should try to solve these issues step by step. Otherwise it gets too complicated.

So let's start with P1 here. I cannot reproduce this with a similar query on my side. You'll have to simplify your query as far as possible such that the issue still occurs. In the first step, I would get rid of all WHERE clauses and try if the issue still occurs. What happens if you run this? Are the columns still not ordered correctly?

TOP 5 RELATED_CI_HINT as 'Hostname',
COUNT(RELATED_CI_HINT) as 'Breach Count'
FROM ALL_EVENTS 
GROUP BY RELATED_CI_HINT 
ORDER BY 'Breach Count' DESC;

Regards, Felix

[Pranjal Shukla]

Felix,

It'd be difficult for me to get the export as data is sensitive. Populating it with test data is a pain. I am suspecting the behavior is due to aggregation functions like Group By and Order BY. Could you verify at your end?

Also, i have another requirement wherein i have to embed nearly 50 queries for 50 different charts. If i do this, Confluence would die. I see Caching has been introduced in new version but first time load would still take time. My questions:

1. If i Cache a Query for 3 days, if the resultant data set changes after 1 hour of last/initial load, will the output at next load (page load where macro is placed) be the updated result or the cached data from first load.
2. Can we have a background caching mechanism? Can that be scheduled like after business hours when load on system is low or say weekends?

[Felix Grund (Scandio)]

Interesting. I'd be glad to help you with this, but for that I'd have to reproduce this. Would you be willing to provide some kind of data export of this table? You could put some test data in it of course. I'd reproduce your issue on my local system and try to help.

[Pranjal Shukla]

Felix,

It occurs with both 2.0.1 and 2.0.3 however works well with 1.14.8.

[Felix Grund (Scandio)]

Hi Pranjal! Thanks for reporting this! For analysis, could you try first if this issue also occurs with PocketQuery 2.0.1?