Query for "Active users who have not created any content (page, blog, or comment) since a specific date" responds with "Table 'confluence.content' doesn't exist"

Jeff M Johnson

This question is in reference to Atlassian Documentation: How to identify inactive users in Confluence

Running Confluence 5.9.7 with MySQL 5.5. I am trying to track down users that have not provided content in a certain amount of time so I'm running this query:

SELECT u.user_name
FROM cwd_user u join user_mapping m
on m.username = u.user_name
WHERE m.user_key not in (
SELECT creator FROM content
WHERE contenttype in ('PAGE', 'BLOGPOST', 'COMMENT')
and creationdate > '2007-01-01')
and u.active = 'T';

When I run that query, I receive the following error: "Table 'confluence.content' doesn't exist"

I've looked and I don't see a 'content' table but there is a 'CONTENT' table. If I use 'CONTENT' the query just spins and spins. Is there something I'm missing?

Jeff M Johnson

This appears to be a bug of some sort in MySQL, at least on MySQL 5.5 using the 'IN()' statement. I was able to work around it by using EXISTS instead:

SELECT u.user_name

FROM cwd_user u join user_mapping m

on m.username = u.user_name

WHERE not exists (

SELECT creator FROM CONTENT c

WHERE contenttype in ('PAGE', 'BLOGPOST', 'COMMENT')

and creationdate > '2015-02-16'

and m.user_key=c.creator)

and u.active = 'T';

That query completes successfully providing the necessary data.