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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.