{"action":"created","issue":{"url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin/issues/221","repository_url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin","labels_url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin/issues/221/labels{/name}","comments_url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin/issues/221/comments","events_url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin/issues/221/events","html_url":"https://github.com/everit-org/jira-timetracker-plugin/issues/221","id":154293253,"number":221,"title":"Shadowed users cause the plugin to fail","user":{"login":"mickyr","id":11725555,"avatar_url":"https://avatars.githubusercontent.com/u/11725555?v=3","gravatar_id":"","url":"https://api.github.com/users/mickyr","html_url":"https://github.com/mickyr","followers_url":"https://api.github.com/users/mickyr/followers","following_url":"https://api.github.com/users/mickyr/following{/other_user}","gists_url":"https://api.github.com/users/mickyr/gists{/gist_id}","starred_url":"https://api.github.com/users/mickyr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mickyr/subscriptions","organizations_url":"https://api.github.com/users/mickyr/orgs","repos_url":"https://api.github.com/users/mickyr/repos","events_url":"https://api.github.com/users/mickyr/events{/privacy}","received_events_url":"https://api.github.com/users/mickyr/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin/labels/bug","name":"bug","color":"fc2929"}],"state":"open","locked":false,"assignee":{"login":"bordatesz","id":5459077,"avatar_url":"https://avatars.githubusercontent.com/u/5459077?v=3","gravatar_id":"","url":"https://api.github.com/users/bordatesz","html_url":"https://github.com/bordatesz","followers_url":"https://api.github.com/users/bordatesz/followers","following_url":"https://api.github.com/users/bordatesz/following{/other_user}","gists_url":"https://api.github.com/users/bordatesz/gists{/gist_id}","starred_url":"https://api.github.com/users/bordatesz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bordatesz/subscriptions","organizations_url":"https://api.github.com/users/bordatesz/orgs","repos_url":"https://api.github.com/users/bordatesz/repos","events_url":"https://api.github.com/users/bordatesz/events{/privacy}","received_events_url":"https://api.github.com/users/bordatesz/received_events","type":"User","site_admin":false},"milestone":null,"comments":6,"created_at":"2016-05-11T17:01:03Z","updated_at":"2016-05-26T08:41:04Z","closed_at":null,"body":"This should be applicable to the worklog query plugin as well.\r\n\r\n**Criticality is very high** as the reporting part of the plugin simply fails to work. \r\n\r\nProblem: If the jira installation has or had multiple user directories(jira, crowd, ldap etc.) with one or more users being present in multiple directories, the reporting part of the plugin fails(go to reporting and try generating report with any parameters) with the following exception:\r\n\r\n```\r\nSEVERE: Servlet.service() for servlet [action] in context with path [/jira] threw exception [com.querydsl.core.QueryException: Caught SQLException for select project.pname as projectName, project.pkey as projectKey, concat(concat(project.pkey, ?), cast(issue.issuenum as char)) as issueKey, issue.summary as issueSummary, issue.id as issueId, issuetype.pname as issueTypeName, issuetype.iconurl as issueTypeIconUrl, issuestatus.pname as issueStatusName, (select case when issueUser.display_name is not null then issueUser.display_name else issue.assignee end\r\nfrom cwd_user issueUser\r\nleft join app_user appUserForIssue\r\non issueUser.lower_user_name = appUserForIssue.lower_user_name\r\nwhere appUserForIssue.lower_user_name = issue.assignee) as issueAssignee, issue.timeoriginalestimate as issueOriginalEstimate, issue.timeestimate as issueRemainingEstimate, worklog.worklogbody as worklogBody, worklog.timeworked as worklogTimeWorked, project.description as projectDescription, priority.pname as priorityName, priority.iconurl as priorityIconUrl, (select case when issueUser.display_name is not null then issueUser.display_name else issue.reporter end\r\nfrom cwd_user issueUser\r\nleft join app_user appUserForIssue\r\non issueUser.lower_user_name = appUserForIssue.lower_user_name\r\nwhere appUserForIssue.lower_user_name = issue.reporter) as issueReporter, issue.created as issueCreated, issue.updated as issueUpdated, resolution.pname as resolutionName, worklog.startdate as worklogStartDate, worklog.created as worklogCreated, worklog.updated as worklogUpdated, (case when cwd_user.display_name is null then worklog.author else cwd_user.display_name end) as worklogUser\r\nfrom worklog worklog\r\njoin jiraissue issue\r\non worklog.issueid = issue.id\r\njoin project project\r\non issue.project = project.id\r\njoin issuetype issuetype\r\non issue.issuetype = issuetype.id\r\njoin issuestatus issuestatus\r\non issue.issuestatus = issuestatus.id\r\njoin priority priority\r\non issue.priority = priority.id\r\nleft join resolution resolution\r\non issue.resolution = resolution.id\r\nleft join app_user appuser\r\non appuser.user_key = worklog.author\r\nleft join cwd_user cwd_user\r\non cwd_user.lower_user_name = appuser.lower_user_name\r\nwhere 1 = 1 and project.id = ? and worklog.author in (select qWorklogAuthor.user_key\r\nfrom app_user qWorklogAuthor\r\nwhere qWorklogAuthor.lower_user_name = ?) and worklog.startdate >= ? and worklog.startdate < ?\r\norder by concat(concat(project.pkey, ?), cast(issue.issuenum as char)) asc, worklog.startdate asc\r\nlimit ?\r\noffset ?] with root cause\r\njava.sql.SQLException: Subquery returns more than 1 row\r\n        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)\r\n        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)\r\n        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)\r\n        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)\r\n        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)\r\n        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)\r\n        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)\r\n        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2324)\r\n        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)\r\n        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)\r\n        at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:432)\r\n        at org.everit.jira.reporting.plugin.query.WorklogDetailsReportQueryBuilder$2.call(WorklogDetailsReportQueryBuilder.java:172)\r\n        at org.everit.jira.reporting.plugin.query.WorklogDetailsReportQueryBuilder$2.call(WorklogDetailsReportQueryBuilder.java:156)\r\n        at org.everit.jira.querydsl.support.ri.QuerydslSupportImpl.execute(QuerydslSupportImpl.java:95)\r\n        at org.everit.jira.reporting.plugin.ReportingPluginImpl.getWorklogDetailsReport(ReportingPluginImpl.java:230)\r\n        at org.everit.jira.reporting.plugin.web.ReportingWebAction.createReport(ReportingWebAction.java:201)\r\n        at org.everit.jira.reporting.plugin.web.ReportingWebAction.doExecute(ReportingWebAction.java:303)\r\n```\r\n\r\nProbable cause is the subquery:\r\n```\r\nselect case when issueUser.display_name is not null then issueUser.display_name else issue.reporter end\r\nfrom cwd_user issueUser\r\nleft join app_user appUserForIssue\r\non issueUser.lower_user_name = appUserForIssue.lower_user_name\r\nwhere appUserForIssue.lower_user_name = issue.reporter\r\n```\r\n\r\nThe following query would return results in an affected jira installation:\r\n`select user_name, count(*) from cwd_user group by user_name having count(*) > 1;`\r\n\r\nAlso see: Shadowed Users under Atlassian documentation at: https://developer.atlassian.com/jiradev/jira-platform/jira-architecture/database-schema/database-user-and-group-tables\r\n"},"comment":{"url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin/issues/comments/221811846","html_url":"https://github.com/everit-org/jira-timetracker-plugin/issues/221#issuecomment-221811846","issue_url":"https://api.github.com/repos/everit-org/jira-timetracker-plugin/issues/221","id":221811846,"user":{"login":"bordatesz","id":5459077,"avatar_url":"https://avatars.githubusercontent.com/u/5459077?v=3","gravatar_id":"","url":"https://api.github.com/users/bordatesz","html_url":"https://github.com/bordatesz","followers_url":"https://api.github.com/users/bordatesz/followers","following_url":"https://api.github.com/users/bordatesz/following{/other_user}","gists_url":"https://api.github.com/users/bordatesz/gists{/gist_id}","starred_url":"https://api.github.com/users/bordatesz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bordatesz/subscriptions","organizations_url":"https://api.github.com/users/bordatesz/orgs","repos_url":"https://api.github.com/users/bordatesz/repos","events_url":"https://api.github.com/users/bordatesz/events{/privacy}","received_events_url":"https://api.github.com/users/bordatesz/received_events","type":"User","site_admin":false},"created_at":"2016-05-26T08:41:04Z","updated_at":"2016-05-26T08:41:04Z","body":"Hi @mickyr!\r\n\r\nDid you manage to try the fixes?"}}
{"actor":{"display_login":"bordatesz"}}