search

FusionAuth / fusionauth-issues

FusionAuth issue submission project
https://fusionauth.io
91 stars 12 forks source link

Unable to delete a tenant that has users #221

Closed johnmaia closed 5 years ago

johnmaia commented 5 years ago

Unable to delete a tenant that has users

Description

I'm currently using the @fusionauth/node-client version 1.7.3 and I'm unable to delete a tenant that has registered users.

When I call the following error is thrown when the deleteTenant method is called:

{ 
 statusCode: 500,
 errorResponse: { 
   generalErrors: [{ 
     code: '[Exception]',
     message: 'FusionAuth encountered an unexpected error. Please contact support for assistance.' 
    }] 
  },
  successResponse: null,
  exception: null 
}

For more details I've took a look at the database (postgres-10.6) and fusionauth-app (1.7.2) docker services logs:

database_1       | 2019-07-15 12:43:41.371 UTC [36] ERROR:  update or delete on table "tenants" violates foreign key constraint "users_fk_1" on table "users"
database_1       | 2019-07-15 12:43:41.371 UTC [36] DETAIL:  Key (id)=(0bf426e2-1a50-4291-9915-e70b5ebe3621) is still referenced from table "users".
database_1       | 2019-07-15 12:43:41.371 UTC [36] STATEMENT:  DELETE FROM tenants WHERE id = $1
fusionauth_1     | Jul 15, 2019 12:43:41.376 PM ERROR io.fusionauth.app.primeframework.error.ExceptionExceptionHandler - An unhandled exception was thrown
fusionauth_1     | org.apache.ibatis.exceptions.PersistenceException:
fusionauth_1     | ### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "tenants" violates foreign key constraint "users_fk_1" on table "users"
fusionauth_1     |   Detail: Key (id)=(0bf426e2-1a50-4291-9915-e70b5ebe3621) is still referenced from table "users".
fusionauth_1     | ### The error may involve defaultParameterMap
fusionauth_1     | ### The error occurred while setting parameters
fusionauth_1     | ### SQL: DELETE FROM tenants WHERE id = ?
fusionauth_1     | ### Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "tenants" violates foreign key constraint "users_fk_1" on table "users"
fusionauth_1     |   Detail: Key (id)=(0bf426e2-1a50-4291-9915-e70b5ebe3621) is still referenced from table "users".
fusionauth_1     |  at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
fusionauth_1     |  at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
fusionauth_1     |  at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:213)
fusionauth_1     |  at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
...

Please note that deleting the tenant via dashboard works flawlessly.

Steps to reproduce

Using the node sdk:

  1. Create a new tenant
  2. Add a new user into the previously created tenant
  3. Delete tenant
  4. Error: 
    { 
statusCode: 500,
errorResponse: { 
generalErrors: [{ 
 code: '[Exception]',
 message: 'FusionAuth encountered an unexpected error. Please contact support for assistance.' 
}] 
},
successResponse: null,
exception: null 
}

Workaround

This is a timing issue, the Elastic search index has not indexed the new user and the Elasticsearch index is being leveraged during the delete Tenant request.

To work around the issue, make a PUT request to the /api/user/search API to request a refresh. https://fusionauth.io/docs/v1/tech/apis/users#flush-the-search-engine

Expected behavior

Delete tenant with all of its users.

Platform

robotdan commented 5 years ago

Strange, our UI uses the API as well via the Java client, so it is odd that the Node client is different.

Do you have the full log, or a larger portion of that exception?

johnmaia commented 5 years ago

@robotdan Here's the full log:

fusionauth_1     | Jul 15, 2019 1:45:44.757 PM ERROR io.fusionauth.app.primeframework.error.ExceptionExceptionHandler - An unhandled exception was thrown
fusionauth_1     | org.apache.ibatis.exceptions.PersistenceException:
fusionauth_1     | ### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "tenants" violates foreign key constraint "users_fk_1" on table "users"
fusionauth_1     |   Detail: Key (id)=(0bf426e2-1a50-4291-9915-e70b5ebe3621) is still referenced from table "users".
fusionauth_1     | ### The error may involve defaultParameterMap
fusionauth_1     | ### The error occurred while setting parameters
fusionauth_1     | ### SQL: DELETE FROM tenants WHERE id = ?
fusionauth_1     | ### Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "tenants" violates foreign key constraint "users_fk_1" on table "users"
fusionauth_1     |   Detail: Key (id)=(0bf426e2-1a50-4291-9915-e70b5ebe3621) is still referenced from table "users".
fusionauth_1     |  at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
fusionauth_1     |  at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
fusionauth_1     |  at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:213)
fusionauth_1     |  at sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)
fusionauth_1     |  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
fusionauth_1     |  at java.lang.reflect.Method.invoke(Method.java:498)
fusionauth_1     |  at org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:350)
fusionauth_1     |  at com.sun.proxy.$Proxy44.delete(Unknown Source)
fusionauth_1     |  at org.apache.ibatis.session.SqlSessionManager.delete(SqlSessionManager.java:256)
fusionauth_1     |  at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68)
fusionauth_1     |  at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
fusionauth_1     |  at com.sun.proxy.$Proxy80.delete(Unknown Source)
fusionauth_1     |  at io.fusionauth.api.service.system.DefaultTenantService.delete(DefaultTenantService.java:81)
fusionauth_1     |  at org.mybatis.guice.transactional.TransactionalMethodInterceptor.invoke(TransactionalMethodInterceptor.java:96)
fusionauth_1     |  at org.mybatis.guice.transactional.TransactionalMethodInterceptor.invoke(TransactionalMethodInterceptor.java:96)
fusionauth_1     |  at org.mybatis.guice.transactional.TransactionalMethodInterceptor.invoke(TransactionalMethodInterceptor.java:96)
fusionauth_1     |  at org.mybatis.guice.transactional.TransactionalMethodInterceptor.invoke(TransactionalMethodInterceptor.java:96)
fusionauth_1     |  at io.fusionauth.app.action.api.TenantAction.delete(TenantAction.java:43)
fusionauth_1     |  at sun.reflect.GeneratedMethodAccessor75.invoke(Unknown Source)
fusionauth_1     |  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
fusionauth_1     |  at java.lang.reflect.Method.invoke(Method.java:498)
fusionauth_1     |  at org.primeframework.mvc.util.ReflectionUtils.invoke(ReflectionUtils.java:436)
fusionauth_1     |  at org.primeframework.mvc.action.DefaultActionInvocationWorkflow.execute(DefaultActionInvocationWorkflow.java:84)
fusionauth_1     |  at org.primeframework.mvc.action.DefaultActionInvocationWorkflow.perform(DefaultActionInvocationWorkflow.java:64)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.validation.DefaultValidationWorkflow.perform(DefaultValidationWorkflow.java:47)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.security.DefaultSecurityWorkflow.perform(DefaultSecurityWorkflow.java:81)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.parameter.DefaultPostParameterWorkflow.perform(DefaultPostParameterWorkflow.java:50)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.content.DefaultContentWorkflow.perform(DefaultContentWorkflow.java:52)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.parameter.DefaultParameterWorkflow.perform(DefaultParameterWorkflow.java:57)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.parameter.DefaultURIParameterWorkflow.perform(DefaultURIParameterWorkflow.java:102)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.scope.DefaultScopeRetrievalWorkflow.perform(DefaultScopeRetrievalWorkflow.java:58)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.message.DefaultMessageWorkflow.perform(DefaultMessageWorkflow.java:45)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.action.DefaultActionMappingWorkflow.perform(DefaultActionMappingWorkflow.java:126)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.workflow.StaticResourceWorkflow.perform(StaticResourceWorkflow.java:97)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.parameter.RequestBodyWorkflow.perform(RequestBodyWorkflow.java:89)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.security.DefaultSavedRequestWorkflow.perform(DefaultSavedRequestWorkflow.java:57)
fusionauth_1     |  at org.primeframework.mvc.workflow.SubWorkflowChain.continueWorkflow(SubWorkflowChain.java:43)
fusionauth_1     |  at org.primeframework.mvc.workflow.DefaultMVCWorkflow.perform(DefaultMVCWorkflow.java:91)
fusionauth_1     |  at org.primeframework.mvc.workflow.DefaultWorkflowChain.continueWorkflow(DefaultWorkflowChain.java:44)
fusionauth_1     |  at org.primeframework.mvc.servlet.FilterWorkflowChain.continueWorkflow(FilterWorkflowChain.java:50)
fusionauth_1     |  at org.primeframework.mvc.servlet.PrimeFilter.doFilter(PrimeFilter.java:84)
fusionauth_1     |  at com.inversoft.maintenance.servlet.MaintenanceModePrimeFilter.doFilter(MaintenanceModePrimeFilter.java:59)
fusionauth_1     |  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
fusionauth_1     |  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
fusionauth_1     |  at com.inversoft.servlet.CORSFilter.handleNonCORS(CORSFilter.java:748)
fusionauth_1     |  at com.inversoft.servlet.CORSFilter.doFilter(CORSFilter.java:646)
fusionauth_1     |  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
fusionauth_1     |  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
fusionauth_1     |  at com.inversoft.servlet.UTF8Filter.doFilter(UTF8Filter.java:27)
fusionauth_1     |  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
fusionauth_1     |  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
fusionauth_1     |  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
fusionauth_1     |  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
fusionauth_1     |  at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)
fusionauth_1     |  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
fusionauth_1     |  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
fusionauth_1     |  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
fusionauth_1     |  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
fusionauth_1     |  at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
fusionauth_1     |  at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
fusionauth_1     |  at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
fusionauth_1     |  at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1468)
fusionauth_1     |  at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
fusionauth_1     |  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
fusionauth_1     |  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
fusionauth_1     |  at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
fusionauth_1     |  at java.lang.Thread.run(Thread.java:748)
 Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "tenants" violates foreign key constraint "users_fk_1" on table "users"
fusionauth_1     |   Detail: Key (id)=(0bf426e2-1a50-4291-9915-e70b5ebe3621) is still referenced from table "users".
fusionauth_1     |  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
fusionauth_1     |  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
fusionauth_1     |  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
fusionauth_1     |  at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
fusionauth_1     |  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
fusionauth_1     |  at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
fusionauth_1     |  at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:157)
fusionauth_1     |  at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
fusionauth_1     |  at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
fusionauth_1     |  at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
fusionauth_1     |  at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
fusionauth_1     |  at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
fusionauth_1     |  at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
fusionauth_1     |  at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
fusionauth_1     |  at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
fusionauth_1     |  ... 77 common frames omitted
fusionauth_1     | 15-Jul-2019 14:01:12.993 INFO [http-nio-9011-exec-4] org.apache.coyote.http11.Http11Processor.service Error parsing HTTP request header
fusionauth_1     |  Note: further occurrences of HTTP header parsing errors will be logged at DEBUG level.
fusionauth_1     |  java.lang.IllegalArgumentException: Invalid character found in method name. HTTP method names must be tokens
fusionauth_1     |  at org.apache.coyote.http11.Http11InputBuffer.parseRequestLine(Http11InputBuffer.java:428)
fusionauth_1     |  at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:687)
fusionauth_1     |  at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
fusionauth_1     |  at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
fusionauth_1     |  at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1468)
fusionauth_1     |  at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
fusionauth_1     |  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
fusionauth_1     |  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
fusionauth_1     |  at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
fusionauth_1     |  at java.lang.Thread.run(Thread.java:748)
robotdan commented 5 years ago

I have one theory so far. When we delete a tenant, we leverage the Elasticsearch index to find all of the User Ids in the tenant, and then delete them by Id.

If you have a script (? not sure if you do) - and are doing this programmatically, perhaps the search index is not yet updated after creating the user so we hit a FK constraint.

To test this theory, prior to calling delete tenant, make a PUT request to the /api/user/search API, this will request a search index update.

I can also try to recreate this error your seeing, if I'm correct - we'll need to request a search index refresh in the delete code path to ensure we do not hit this issue.

Thanks!

johnmaia commented 5 years ago

@robotdan Ironically I did exactly that on the test script that I'm developing. To workaround this issue, I developed an extra step where I flushed the search database before removing the tenant users.

robotdan commented 5 years ago

Thanks @johnmaia - great minds think alike. Great catch, I'll fix this in the upcoming release so you should not need that extra step once resolved.

Thanks for letting us know about this issue!

mlyskawinski commented 5 months ago

Hi @robotdan

Which version should be the one that removed this issue? I'm still getting this error while doing something similar to the original author, except refreshing the index does not solve the issue

mooreds commented 5 months ago

@mlyskawinski please consider posting your question in the forum, as old, closed GH issues are not monitored.

https://fusionauth.io/community/forum/