Details
-
Bug
-
Resolution: Fixed
-
Low
-
9.11.0, 9.12.0, 9.12.1, 9.12.2, 9.12.3, 9.12.4, 9.12.5, 9.12.6, 9.12.7
-
9.11
-
3
-
Severity 3 - Minor
-
6
-
Description
Issue Summary
The "fix" of Integrity Checker to fix the workflow inconsistency on MySQL DB does not work and results in SQLException.
Steps to Reproduce
- Manually introduce inconsistency by setting OS_WFENTRY.STATE to 0. In the below example we are doing this for issue with ID 10000
update jira.OS_WFENTRY set STATE = 0 WHERE ID = 10000;
- Go to Administration -> System -> Integrity Checker
- Run "Check Workflow Integrity" by selecting checkbox next to it.
- Run "fix" for the issue on which STATE was set to 0 in step 1.
Expected Results
The Integrity Checker fixes the workflow inconsistency
Actual Results
Intergrity Checker will fail and take us to "Sorry, we had some technical problems during your last operation." Screen.,
The below SQL syntax exception is thrown in the atlassian-jira.log file:
2023-11-14 16:36:33,537+0000 JiraTaskExecutionThread-3 ERROR admin [c.a.jira.task.TaskManagerImpl] Task 'Fixing the integrity for 1 check.' failed. com.querydsl.core.QueryException: Caught SQLException for update OS_WFENTRY set STATE = ? where OS_WFENTRY.ID in (select OS_WFENTRY.ID from OS_WFENTRY OS_WFENTRY left join jiraissue ISSUE on OS_WFENTRY.ID = ISSUE.workflow_id left join project PROJECT on ISSUE.project = PROJECT.id where ISSUE.id is not null and (OS_WFENTRY.STATE is null or OS_WFENTRY.STATE = ?)) at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.querydsl.sql.dml.SQLUpdateClause.execute(SQLUpdateClause.java:217) at com.atlassian.jira.appconsistency.integrity.check.WorkflowStateCheck.lambda$correctFaultyWorkflows$0(WorkflowStateCheck.java:57) at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$execute$1(DefaultQueryDslAccessor.java:70) at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:108) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:77) at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:103) at com.atlassian.jira.database.DefaultQueryDslAccessor.execute(DefaultQueryDslAccessor.java:69) at com.atlassian.jira.appconsistency.integrity.check.WorkflowStateCheck.correctFaultyWorkflows(WorkflowStateCheck.java:54) at com.atlassian.jira.appconsistency.integrity.check.WorkflowStateCheck.doCheck(WorkflowStateCheck.java:48) at com.atlassian.jira.appconsistency.integrity.check.WorkflowStateCheck.correct(WorkflowStateCheck.java:34) at com.atlassian.jira.appconsistency.integrity.IntegrityChecker.correct(IntegrityChecker.java:158) at com.atlassian.jira.appconsistency.integrity.IntegrityChecker.loggedCorrect(IntegrityChecker.java:131) at com.atlassian.jira.appconsistency.integrity.IntegrityChecker.correctWithIdsWithProgress(IntegrityChecker.java:116) at com.atlassian.jira.appconsistency.integrity.integritycheck.FixIntegrityOperation.perform(FixIntegrityOperation.java:43) at com.atlassian.jira.appconsistency.integrity.integritycheck.IntegrityCheckerCallable.call(IntegrityCheckerCallable.java:51) at com.atlassian.jira.appconsistency.integrity.integritycheck.IntegrityCheckerCallable.call(IntegrityCheckerCallable.java:18) at com.atlassian.jira.task.TaskManagerImpl$TaskCallableDecorator.call(TaskManagerImpl.java:528) at com.atlassian.jira.task.TaskManagerImpl$TaskCallableDecorator.call(TaskManagerImpl.java:486) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at com.atlassian.jira.task.ForkedThreadExecutor$ForkedRunnableDecorator.run(ForkedThreadExecutor.java:216) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: java.sql.SQLException: You can't specify target table 'OS_WFENTRY' for update in FROM clause at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040) at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1350) at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:47) at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeUpdate$7(DiagnosticPreparedStatement.java:69) at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:91) at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:62) at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeUpdate(DiagnosticPreparedStatement.java:69) at com.querydsl.sql.dml.SQLUpdateClause.execute(SQLUpdateClause.java:203) ... 22 more
Workaround
We can run the MySQL equivalent to manually fix it.
UPDATE os_wfentry as o1
inner join (
(SELECT o2.id
FROM jiraissue as ji
JOIN os_wfentry as o2
ON ji.workflow_id = o2.id
WHERE o2.state IS NULL OR o2.state = 0)
) AS o3 on o1.id = o3.id
SET o1.state = 1;
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.