Watchers Id's

[Evcgeniy]

Hello!

How to find issues's watchers Id's in the jira schema(https://developer.atlassian.com/server/jira/platform/attachments/jira-7-9-2-database-schema.pdf)?

What table contains this information?

Thanks.

[Gregory Kneller]

SELECT u.lower_username, u.user_key, p.pkey, issuenum FROM jiraissue i, userassociation a, app_user u, project p 

where association_type='WatchIssue' and a.source_name=u.user_key

and i.id=sink_node_id and i.project=p.id

[Nic Brough -Adaptavist-]

Please, move away from using SQL to read the database.  It's slow, complex, bypases all the security, is hard to understand, and can cause performance issues.  Most of the reporting I see based on SQL queries is also horribly horribly inaccurate and wrong.

The data you are looking for is in nodeassociation, but if you look at that, you'll find it's pretty horrible to use, and you really should be using the REST API for automated reporting.

[Gregory Kneller]

It is just an opinion. I use sql to read and change jira data since 2009. Fast, simple, and bypass security (I do not need it), no problems with performance

[Nic Brough -Adaptavist-]

And in 3 months time when something fails because you damaged your data?  Stop it.  It's the wrong thing to do.

[Gregory Kneller]

Nic, there is no REST API for many things. Sometimes database is the only way, of the fast shortcut.

Database changes shall be applied with care and responsibility. If you are not sure, check in a test instance first, and create a copy of changed table. And you must know SQL.

But nothing bad in select sql, RDBMS handles it much faster than java classes.

I have being doing it since more than 10 years, never ran into "in 3 months time when something fails because you damaged your data". 

Actually, the question is not whether one may use database, but how to find watchers there. 

[Nic Brough -Adaptavist-]

That does not change the fact that you should not be touching the database.  

As soon as you do it, you have lost support, and you have no way of knowing what you may have damaged.  Even if you've done it properly (stop Jira, take a full backup, restore and prove that somewhere, make your changes, restart and run a full re-index), you don't know when damage may crawl out. 

Someone messing with watchers in version four completely stuffed an upgrade from 5 to 6.  Adaptavist have had a lot of work born of "someone wrote to our database" and having to rescue systems from database mistakes.

Just don't do it.

[Gregory Kneller]

Again, I touch database continuously, because I perform migrations, clean-up and  restructuring of JIRA. Some things may be done with scripting (Script Runner or Power Script), but other could be done only in database, in reasonable time.

I had never "a lot of work from it", but it is my experience.

Having database dump prior to change is the safe way.  
 
There is no need in all this "stop Jira, ...restart and run a full re-index". 
Clearing Jira cache using built-in script of script runner, or admClearCache() in SIL code work perfect, if you do not touch groups and permissions (in this case you need stop-change-start sequence)