Change hard Limit in Confluence dbView

Question

Hi Community,

I need to get DB query results greater than 1000 rows.
Is any way to change the rows limit returned by dbView? Or exists any upgrade in progress addressing this limitation? Any alternative?

I am using Confluence 7.8.3 (DataServer).

Thanks in advance!

Apperitivi !!! · Answer

Hi Hugo, thanks for your contribution! Really appreciate it. My confluence instance stays on an Oracle database,  so that solution is pretty feasible on it, also.

In any case, the query I need to run on dbView returns probably more than 60k-70k rows. So I guess it is not a good practice to run 60 o 70 queries every time I need that frequent report.

However, it is a good alternative when we need to run queries returning less than 10k rows.

Thanks Hugo. 😉

Hugo Navia · Answer

Hi mate,

Not sure if this works for you but I found a way (in MySQL) to pull the list I'm looking for by limiting the output and get the information in chunks:

Example:

SELECT DISTINCT sp.permgroupname, s.spacename
FROM SPACEPERMISSIONS sp
JOIN SPACES s ON sp.spaceid = s.spaceid
LEFT JOIN user_mapping um ON sp.permusername = um.user_key
WHERE sp.permgroupname IS NOT NULL order by sp.permgroupname
LIMIT 999, 2000;

In the example you can see that I asked for the row 999 to the row 2000. this did the trick for me.

Hope it works for you.