Atlassian analytics fails with a SQL error

XMLWordPrintable

    • Type: Bug
    • Resolution: Fixed
    • Priority: Low
    • None
    • Affects Version/s: 10.2.0, 10.3.0
    • Component/s: User Management - Others
    • None
    • 10.02
    • 1
    • Severity 3 - Minor

      Issue Summary

      When Analytics is enabled on Jira, it periodically throws a warning related to a failing SQL query.

      Steps to Reproduce

      1. On Jira, ensure that analytics is enabled on Administration (⚙ icon) > System > Analytics
      1. This issue can easily be reproduced by setting the `-Dembedded.crowd.analytics.debug.schedule=true` system property - this will cause the collector to run around every minute or so. I believe the issue is in how the query's converted. The query gets converted to a ConvertedExpression of `DIRECTORY.type = INTERNAL && DIRECTORY.active = true` and the underlying column type for the active flag is numeric where 1 means active and 0 inactive.

      Expected Results

      No warnings are populated in the application logs

      Actual Results

      The below exception is thrown in the xxxxxxx.log file:

      2024-12-02 23:00:00,119+0000 Caesium-1-3 WARN ServiceRunner     [c.a.c.e.a.analytics.scheduler.AbstractStatisticsCollectionService] Failed to collect analytics for com.atlassian.crowd.embedded.admin.analytics.collector.PasswordPolicyEcStatisticsCollector@3b6ce445
com.querydsl.core.QueryException: Caught PSQLException for select DIRECTORY.id, DIRECTORY.directory_name, DIRECTORY.lower_directory_name, DIRECTORY.created_date, DIRECTORY.updated_date, DIRECTORY.active, DIRECTORY.description, DIRECTORY.impl_class, DIRECTORY.lower_impl_class, DIRECTORY.directory_type, DIRECTORY.directory_position
from public.cwd_directory DIRECTORY
where DIRECTORY.directory_type = ? and DIRECTORY.active = ?
order by DIRECTORY.directory_position asc
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
Full stack trace
2024-12-06 09:33:30,023+0100 Caesium-1-3 WARN      [c.a.c.e.a.analytics.scheduler.AbstractStatisticsCollectionService] Failed to collect analytics for com.atlassian.crowd.embedded.admin.analytics.collector.PasswordPolicyEcStatisticsCollector@38d3cda4
com.querydsl.core.QueryException: Caught PSQLException for select DIRECTORY.id, DIRECTORY.directory_name, DIRECTORY.lower_directory_name, DIRECTORY.created_date, DIRECTORY.updated_date, DIRECTORY.active, DIRECTORY.description, DIRECTORY.impl_class, DIRECTORY.lower_impl_class, DIRECTORY.directory_type, DIRECTORY.directory_position
from public.cwd_directory DIRECTORY
where DIRECTORY.directory_type = ? and DIRECTORY.active = ?
order by DIRECTORY.directory_position asc
    at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
    at com.querydsl.sql.Configuration.translate(Configuration.java:507)
    at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:485)
    at com.atlassian.jira.crowd.embedded.ofbiz.OfBizDirectoryDao.lambda$search$5(OfBizDirectoryDao.java:409)
    at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:63)
    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.executeQuery(DefaultQueryDslAccessor.java:62)
    at com.atlassian.jira.crowd.embedded.ofbiz.OfBizDirectoryDao.search(OfBizDirectoryDao.java:403)
    at com.atlassian.crowd.crypto.EncryptingDirectoryDAO.search(EncryptingDirectoryDAO.java:68)
    at com.atlassian.crowd.manager.directory.DirectoryManagerGeneric.searchDirectories(DirectoryManagerGeneric.java:188)
    at com.atlassian.crowd.manager.recovery.RecoveryModeAwareDirectoryManager.searchDirectories(RecoveryModeAwareDirectoryManager.java:72)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
    at jdk.proxy3/jdk.proxy3.$Proxy398.searchDirectories(Unknown Source)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241)
    at jdk.proxy210/jdk.proxy210.$Proxy4237.searchDirectories(Unknown Source)
    at com.atlassian.crowd.embedded.analytics.collector.PasswordPolicyStatisticsCollector.getActiveInternalDirectories(PasswordPolicyStatisticsCollector.java:75)
    at com.atlassian.crowd.embedded.analytics.collector.PasswordPolicyStatisticsCollector.collect(PasswordPolicyStatisticsCollector.java:60)
    at com.atlassian.crowd.embedded.admin.analytics.scheduler.AbstractStatisticsCollectionService.publishEvents(AbstractStatisticsCollectionService.java:113)
    at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:423)
    at com.atlassian.crowd.embedded.admin.analytics.scheduler.AbstractStatisticsCollectionService.runJob(AbstractStatisticsCollectionService.java:73)
    at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134)
    at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106)
    at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90)
    at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:500)
    at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:495)
    at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:519)
    at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:415)
    at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66)
    at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60)
    at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35)
    at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 367
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:123)
    at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:41)
    at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeQuery$5(DiagnosticPreparedStatement.java:59)
    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.executeQuery(DiagnosticPreparedStatement.java:59)
    at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:445)
    ... 53 more

      Workaround

      Currently there is no known workaround for this behavior. A workaround will be added here when available

              Assignee:
              Unassigned
              Reporter:
              Natalia Wroblewska
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: