时间戳

首页 > 解决方案 > 在 SCDF 仪表板中启动任务时违反“task_metadata_fk”中的外键约束

spring-cloud-dataflow - 在 SCDF 仪表板中启动任务时违反“task_metadata_fk”中的外键约束

问题描述

我正在尝试使用 SCDF 仪表板启动任务。

我使用 CockroachDB 作为底层持久层和 Hibernate ORM 版本 5.4.22.Final，SCDF 的 2.7.0 版本以及 2.8.0-SNAPSHOT 都支持它。

我能够使用 jar 的文件 URI 创建应用程序，并且能够创建任务。当我尝试使用程序参数启动任务时，我在 SCDF 服务器的控制台输出中收到此异常：

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO task_execution_metadata (id, task_execution_id, task_execution_manifest) VALUES (?, ?, ?)]; ERROR: insert on table "task_execution_metadata" violates foreign key constraint "task_metadata_fk"
  Detail: Key (task_execution_id)=(2) is not present in table "task_execution".; nested exception is org.postgresql.util.PSQLException: ERROR: insert on table "task_execution_metadata" violates foreign key constraint "task_metadata_fk"
  Detail: Key (task_execution_id)=(2) is not present in table "task_execution".
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:251)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:883)
        at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:321)
        at org.springframework.cloud.dataflow.server.repository.JdbcDataflowTaskExecutionMetadataDao.save(JdbcDataflowTaskExecutionMetadataDao.java:111)
        at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskExecutionService.executeTask(DefaultTaskExecutionService.java:340)
        at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskExecutionService$$FastClassBySpringCGLIB$$422cda43.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
        at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskExecutionService$$EnhancerBySpringCGLIB$$dbc35f76.executeTask(<generated>)
        at org.springframework.cloud.dataflow.server.controller.TaskExecutionController.launch(TaskExecutionController.java:177)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:103)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: insert on table "task_execution_metadata" violates foreign key constraint "task_metadata_fk"
  Detail: Key (task_execution_id)=(2) is not present in table "task_execution".
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
        at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
        ... 74 common frames omitted

查看“task_execution”表，我确实看到了任务及其执行 ID。所以在我看来，这里可能存在一些时间问题，其中插入到“task_execution_metadata”中是在插入到“task_execution”表之前执行的。

这发生在 2.7.0 和 2.8.0-SNAPSHOT 上。任何有关为什么会发生这种情况的帮助将不胜感激。

编辑：

在调试了更多之后，我得出的结论是，这要么是 postgresql 驱动程序发送查询的方式中的错误，要么是 CockroachDB 解释查询的方式。

在 CockroachDB 节点上的请求日志中，我可以看到查询以正确的顺序出现：

‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;SELECT taskdeploy0_.id AS id1_4_, taskdeploy0_.object_version AS object_v2_4_, taskdeploy0_.created_on AS created_3_4_, taskdeploy0_.platform_name AS platform4_4_, taskdeploy0_.task_definition_name AS task_def5_4_, taskdeploy0_.task_deployment_id AS task_dep6_4_ FROM task_deployment AS taskdeploy0_ WHERE taskdeploy0_.task_definition_name = $1 ORDER BY taskdeploy0_.created_on ASC LIMIT $2"› ‹{$1:"'run-batch-client'", $2:"1"}› 1.053 0 ‹&quot;"› 0
‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;SELECT taskdefini0_.definition_name AS definiti1_3_0_, taskdefini0_.description AS descript2_3_0_, taskdefini0_.definition AS definiti3_3_0_ FROM task_definitions AS taskdefini0_ WHERE taskdefini0_.definition_name = $1"› ‹{$1:"'run-batch-client'"}› 0.904 1 ‹&quot;"› 0
‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;SELECT appregistr0_.id AS id1_0_, appregistr0_.object_version AS object_v2_0_, appregistr0_.default_version AS default_3_0_, appregistr0_.metadata_uri AS metadata4_0_, appregistr0_.name AS name5_0_, appregistr0_.type AS type6_0_, appregistr0_.uri AS uri7_0_, appregistr0_.version AS version8_0_ FROM app_registration AS appregistr0_ WHERE ((appregistr0_.name = $1) AND (appregistr0_.type = $2)) AND (appregistr0_.default_version = true)"› ‹{$1:"'batch-client'", $2:"4"}› 0.953 1 ‹&quot;"› 0
‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;SELECT nextval('TASK_SEQ')"› ‹{}› 32.725 1 ‹&quot;"› 0
‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;INSERT INTO task_execution(task_execution_id, exit_code, start_time, task_name, last_updated, external_execution_id, parent_execution_id) VALUES ($1, $2, $3, $4, $5, $6, $7)"› ‹{$1:"14", $2:"NULL", $3:"NULL", $4:"'run-batch-client'", $5:"'2020-12-07 16:55:55.733'", $6:"NULL", $7:"NULL"}› 1.612 1 ‹&quot;"› 0
‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;SELECT task_execution_manifest FROM task_execution_metadata AS m INNER JOIN task_execution AS e ON m.task_execution_id = e.task_execution_id WHERE e.task_name = $1 ORDER BY e.task_execution_id DESC LIMIT 1 OFFSET 0"› ‹{$1:"'run-batch-client'"}› 2.138 1 ‹&quot;"› 0
‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;SELECT nextval('task_execution_metadata_seq')"› ‹{}› 3.576 1 ‹&quot;"› 0
‹exec› ‹&quot;PostgreSQL JDBC Driver"› ‹{}› ‹&quot;INSERT INTO task_execution_metadata(id, task_execution_id, task_execution_manifest) VALUES ($1, $2, $3)"› ‹{$1:"13", $2:"14", $3:"'{\"taskDeploymentRequest\":{\"definition\":{\"name\":\"run-batch-client\",\"properties\":{\"management.metrics.tags.service\":\"task-application\",\"spring.datasource.username\":\"SA\",\"spring.datasource.url\":\"jdbc:postgresql://localhost:26257/batchdb?ssl=false\",\"spring.datasource.driverClassName\":\"org.postgresql.Driver\", <SNIP>\"--spring.cloud.task.executionid=14\"]},\"platformName\":\"default\"}'"}› 1.007 1 ‹&quot;insert on table \"task_execution_metadata\" violates foreign key constraint \"task_metadata_fk\""› 0

从上面的日志可以看到postgresql驱动程序INSERT INTO task_execution_metadata(id, task_execution_id, task_execution_manifest) VALUES ($1, $2, $3)"› ‹{$1:"13", $2:"14", $3:"<BLAHBLAH>"}>在任务进入任务表后发送参数化查询。我认为 CockroachDB 将参数化任务 id 解释为字符串而不是长字符串，然后抛出外键约束违规。

因此，这要么是驱动程序发送无效查询的情况，要么是数据库错误地解释查询的情况。我认为是后者，我会在 CockroachDB 的论坛上开一个帖子

标签： spring-cloud-dataflowcockroachdbspring-cloud-taskjdbc-postgres

解决方案

问题似乎在于如何在Spring Cloud Task和中管理数据库事务Spring Cloud Task Dataflow。此处详细描述了该问题：https ://github.com/spring-cloud/spring-cloud-dataflow/issues/4286

推荐阅读