Can't DROP 'fk_ao_f58fca_sync_status_msg_data_ao_sync_status_id'; check that column/key exists

Question

Hello everyone,

In my JSM, the SAML Single Sign-On Plugin Configuration Page is encountering a 500 error. Upon checking the logs, it indicates that the system keeps attempting to delete a non-existent foreign key (FK). However, after investigation, I couldn’t locate this FK anywhere. Despite this, the system continues to try deleting it, which is causing the page error.

I’m not sure which part I should look into to resolve this issue.

Below is my error message:

Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:

Database:

- name:MySQL

- version:5.7.35

- minor version:7

- major version:5

- name:MySQL Connector/J

- version:mysql-connector-java-8.0.30 (Revision: 1de2fe873fe26189564c030a34388501a) java.sql.SQLSyntaxErrorException: Can't DROP 'fk_ao_f58fca_sync_status_msg_data_ao_sync_status_id'; check that column/key exists at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:54) at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.lambda$create$0(AbstractActiveObjectsFactory.java:73) at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21) at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:50) ... 2 filtered at java.lang.reflect.Method.invoke(Method.java:498) at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26) at com.sun.proxy.$Proxy544.doInTransaction(Unknown Source) ... 2 filtered

Does anyone have any suggestions or need more information?
Thank you all!

Elara · Answer

Hi Radek Dostál,

Thank you for your reply. As far as I know, no upgrades have been made.

Here is the full log:

2024-12-11 11:20:40,393+0800 http-nio-8080-exec-14 ERROR anonymous 680x163022x1 - 0:0:0:0:0:0:0:1 /plugins/servlet/samlsso [c.r.a.samlsso.responseidstore.ActiveObjectsResponseIdStore] Checking ActiveObjectsResponseIdStore for id https://xxxxxxx/sso/idp_response__4217012345258710551  failed, returning false
com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.resolution.atlasplugins.samlsso-common]
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:95)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:86)
at com.atlassian.sal.core.executor.ThreadLocalDelegateCallable.call(ThreadLocalDelegateCallable.java:38)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:MySQL
- version:5.7.35
- minor version:7
- major version:5
- name:MySQL Connector/J
- version:mysql-connector-java-8.0.30 (Revision: 1de2fe873fe26189564c030a343885011412976a)
java.sql.SQLSyntaxErrorException: Can't DROP 'fk_ao_f58fca_sync_status_msg_data_ao_sync_status_id'; check that column/key exists
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:54)
at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.lambda$create$0(AbstractActiveObjectsFactory.java:73)
at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:21)
at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:50)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
at com.sun.proxy.$Proxy544.doInTransaction(Unknown Source)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:131)
at com.sun.proxy.$Proxy544.doInTransaction(Unknown Source)
at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:18)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
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:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
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:185)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy1164.execute(Unknown Source)
at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:70)
at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:32)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects$1$1$1.call(TenantAwareActiveObjects.java:91)
... 6 more
Caused by: java.sql.SQLSyntaxErrorException: Can't DROP 'fk_ao_f58fca_sync_status_msg_data_ao_sync_status_id'; check that column/key exists
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1334)
at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2084)
at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234)
at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2354)
at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2410)
at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2382)
at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:91)
at net.java.ao.EntityManager.migrate(EntityManager.java:131)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:52)
... 43 more

Radek Dostál · Answer

Have you done an upgrade recently? I'm somewhat suspicious of seeing `EntityManagedActiveObjects#migrate()` as the offender, because as far as I know, migrate should be called pretty much only during db data upgrades (e.g. new plugin version migrating from old data model to new one).

Aaaanyway, do you have the full stack trace? From what you pasted I only see native/atlassian classes. So by seeing the full stack trace we can determine any 3rd party plugin that may be at play here.

As a workaround you could probably create that foreign key by hand, and then let Jira drop it (at which point it should not fail) - but that would be a pretty dirty workaround that we don't know might not cause any other or further problems.

I still suspect that you might have done some upgrades and either to versions that contain this bug (and other versions might do better), or maybe skipped something during an upgrade path. Either way, I don't think that this has just manifested all by it's own.