This copy of Confluence is having a problem with anonymous users not getting to see any updates to the pages they have access to. This results in recent changes being empty for anonymous users, even when things that they can see and change have been changed. Logged in users however see a normal list of recent updates.

I believe this causes me problems with making it more difficult for Google to discover recent changes to to the wiki. Without the recent changes on the dashboard, the dashboard page does not appear to change very often.

We've tracked this down to a hibernate query that is returning zero records: (Actually the original returns space1_.spacekey, but I changed it to spaceID in preparation for eliminating the inner join, either way the query returns 0 records)

select distinct space1_.SPACEID as x0_0_ 
from SPACEPERMISSIONS spacepermi0_ 
inner join SPACES space1_ on spacepermi0_.SPACEID=space1_.SPACEID 
where 
((spacepermi0_.PERMUSERNAME is null ))and
((spacepermi0_.PERMGROUPNAME is null))and
((spacepermi0_.PERMTYPE='VIEWSPACE' ))

We noticed that there are in fact records in the database that have permusername=null and permusergroupname=null and permtype=viewspace. A simpler query, without the inner join, returns an expected set of about 50 records:

select distinct SPACEID from SPACEPERMISSIONS spacepermi0_ where ((spacepermi0_.PERMUSERNAME is null ))and((spacepermi0_.PERMGROUPNAME is null ))and((spacepermi0_.PERMTYPE='VIEWSPACE' ))

I noticed that changing the first query to get SPACEID from spaceperms (instead of spaces.spacekey) gets 46 records, same as second query (we tried without the inner join):

select distinct spacepermi0_.SPACEID as x0_0_ 
from SPACEPERMISSIONS spacepermi0_ 
inner join SPACES space1_ on spacepermi0_.SPACEID=space1_.SPACEID 
where 
 ((spacepermi0_.PERMUSERNAME is null )) and
 ((spacepermi0_.PERMGROUPNAME is null )) and
((spacepermi0_.PERMTYPE='VIEWSPACE' ))

So I decided to look at the estimated query plans to get an idea of what is going on.

Estimated query plan shows:

Two parallel operations:
- Clustered Index Scan dbo.Spacepermissions
  - predicate including all the conditions permtype=viewspace, permusername=null permgroupname=null)
  - object - spacepermissions.pk__spacepermissions_0cbae877 spacepermi0
  - outputlist: spacepermissions.spaceid
- Clustered index seek (scanning range of rows from clustered index)
  - object: dbo.spaces.pk_spaces_2c3394d0 space1_
  - Seek predicates dbo.spaces.spaceid = dbo.spacepermissions.spaceid as spacepermi0.spaceid
Both feeding into "Nested Loops" (Inner join)
- Output list: spacepermissions.spaceid
- outer references: spacepermissions.spaceid
Feeding into sort (Distinct)
- output list: spacepermissions.spaceid
- order by: spacepermsisions.spaceid ascending

Here again is the original query that only gets zero records:

select distinct space1_.SPACEID as x0_0_ 
from SPACEPERMISSIONS spacepermi0_ 
inner join SPACES space1_ on spacepermi0_.SPACEID=space1_.SPACEID 
where 
((spacepermi0_.PERMUSERNAME is null ))and
((spacepermi0_.PERMGROUPNAME is null))and
((spacepermi0_.PERMTYPE='VIEWSPACE' ))

Estimated query plan shows: (Very different)

* clustered index seek
** predicate:  [confluencebobsgear].[dbo].[_dta_mv_0].[_col_2]=[confluencebobsgear].[dbo].[_dta_mv_0].[_col_3] and [confluencebobsgear].[dbo].[_dta_mv_0].[_col_1]='VIEWSPACE'
** object: [confluencebobsgear].[dbo].[_dta_mv_0].[_dta_index__dta_mv_0_c_23_2005582183__k2_k3_k6]
** output list: dbo._dta_mv_0._col_1, dbo._dta_mv_0.col_2, dbo._dta_mv_0._col_3, dbo._dta_mv_0._col_4
* feeding into sort (distinct sort)
** output list: [confluencebobsgear].[dbo].[_dta_mv_0]._col_4
** order by:  [confluencebobsgear].[dbo].[_dta_mv_0]._col_4  ascending

I believe dta stands for database tuning advisor. Back in September, I used the DTA to try to improve slow running queries:
Investigating SQL 2005 Database For A Slow Confluence Installation

I did notice this problem for anonymous users after that point, but it was a few months later and I didn't connect this behavior with this event.

According to this article http://blog.sqlauthority.com/2009/10/15/sql-server-enable-automatic-statistic-update-on-database/ sometimes statistics updates might not be enabled on a table, and this can cause the system to use unoptimized queries (there was not a report of wrong operation in this article.)

I checked the database and found that auto create statistics and auto update statistics are true for this database.

Under spacepermissions table are several statistics:

_dta_stat_197575732_2_3_5
_dta_stat_197575732_2_5_4
_dta_stat_197575732_4_2_3_5
_dta_stat_197575732_5_4

There is a view on this database called _dta_mv_0:

SELECT     dbo.SPACEPERMISSIONS.PERMTYPE AS _col_1, dbo.SPACEPERMISSIONS.PERMUSERNAME AS _col_2, 
                      dbo.SPACEPERMISSIONS.PERMGROUPNAME AS _col_3, dbo.SPACES.SPACEID AS _col_4, dbo.SPACES.SPACEKEY AS _col_5, 
                      dbo.SPACEPERMISSIONS.PERMID AS _col_6
FROM         dbo.SPACEPERMISSIONS INNER JOIN
                      dbo.SPACES ON dbo.SPACEPERMISSIONS.SPACEID = dbo.SPACES.SPACEID

Automatically recompute statistics is off for this view (there is one index and one statistic associated with this view). I just used modify on it to turn rebuild index, and recompute statitics, and now the statistics associated with this view are saying they were just updated today. But the failing query is still failing.

The queryplan seems to have translated
Spacepermissions.permtype (col_1) = 'VIEWSPACE'
Spacepermissions.permusername (col_2) = null
Spacepermsisions.permgroupname (col_3) = null

To
_dta_mv_0.col_2=_dta_mv_0.col_3 and dtamv0.col_1='VIEWSPACE'

That doesn't look right at all. We want null in both those fields, not just the two fields being equal!

I was able to view the view, and the perms look right. I added

SELECT * from _dta_mv_0 where (_col_1='VIEWSPACE') and this looked ok (144 records)

First several records show _col_2 and _col_3 equal to null

SELECT * from _dta_mv_0 where (_col_1='VIEWSPACE') and (_col_2 IS NULL) and this looked ok (93 records)
SELECT * from _dta_mv_0 where (_col_1='VIEWSPACE') and (_col_3 IS NULL) and this looked ok (93 records)

SELECT * from _dta_mv_0 where (_col_1='VIEWSPACE') and (_col_2 IS NULL) and (_col_3 IS NULL) broke (0 records)

There is one index, and one statistic associated with this view.

Using the SQL for the view, I could restore it. So I am deleting the view. This fixed the problem, anonymous users now see a full list of recent changes on the dashboard.

Related resources

Query: automatic statistics updates
- Update statistics: http://msdn.microsoft.com/en-us/library/ms187348.aspx
Query: "database tuning advisor" incorrect query results
- Removing unused indexes: http://sqlserverperformance.idera.com/indexing/removing-unused-indexes/

Possible problem in Confluence queries caused by database tuning advisor

Related resources