#canal 执行sql 报错:index_name etl failed! ==>null,如何解决?

知识问答作者:U大使日期:2021-01-24点击:16

话不多少,直接上问题,下面这段canal 执行 时 sql 报“ etl failed! ==>null”

SELECT c.creator as creator, c.creator_id as creatorId, date_format(c.create_time,'%Y-%m-%d %H:%i:%S') as createTime, c.updater as updater, c.updater_id as updaterId, date_format(c.update_time,'%Y-%m-%d %H:%i:%S') as updateTime, cast(enabled_flag as SIGNED) as enabledFlag, c.trace_id as traceId, c.company_id as companyId, c.driver_id as driverId, c.driver_name as driverName, c.driver_phone as driverPhone, c.driver_id_card as driverIdCard, c.id as _id, CONCAT('[',t4.json,']') as driverRefVehicles FROM company_driver c

LEFT JOIN (

    SELECT t1.company_driver_id,        GROUP_CONCAT(        JSON_OBJECT(         'companyId',t1.company_id,         'companyDriverId',t1.company_driver_id,         'companyVehicleId',t1.company_vehicle_id,         'oftenUse',t1.often_use,         'vehicleNumber',t2.vehicle_number,         'vehiclePlateColorCode',t2.vehicle_plate_color_code,         'vehicleLength',t2.vehicle_length,         'vehicleType',t2.vehicle_type         )    ) as json FROM company_driver_vehicle t1    LEFT JOIN company_vehicle t2 ON  t2.id = t1.company_vehicle_id    WHERE t1.enabled_flag=1 GROUP BY t1.company_driver_id) t4 ON c.id=t4.company_driver_id

经过一步步排查,初步原因是最里面的left join 问题,关联了这个就报错,不关联就不报,可以执行,难道不支持这样的查询,然后想建立一个视图,但是网上说建立视图,canal是根据mysql的biglog去检查的,视图好像不行,有没有大神回答一下,这个多left join 是不支持 还是有其他的解决方案,谢谢了

原代码错误行:

6:57:10.412 2020-11-12 16:57:05.766 [Thread-5] INFO c.a.o.c.client.adapter.es.core.monitor.ESConfigMonitor - Change a es mapping config: resource-vehicle-license.yml of canal adapter 16:58:05.413 2020-11-12 16:58:02.693 [http-nio-8083-exec-8] INFO c.a.otter.canal.client.adapter.es7x.etl.ESEtlService - start etl to import data to index: resource_company_driver_list 16:58:05.414 2020-11-12 16:58:02.697 [http-nio-8083-exec-8] ERROR c.a.otter.canal.client.adapter.es7x.etl.ESEtlService - null 16:58:05.414 java.lang.NullPointerException: null 16:58:05.414 at com.alibaba.otter.canal.client.adapter.es7x.etl.ESEtlService.lambda$executeSqlImport$1(ESEtlService.java:73) ~[na:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.support.Util.sqlRS(Util.java:60) ~[client-adapter.common-1.1.5-SNAPSHOT.jar:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.es7x.etl.ESEtlService.executeSqlImport(ESEtlService.java:64) ~[na:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.support.AbstractEtlService.importData(AbstractEtlService.java:105) ~[client-adapter.common-1.1.5-SNAPSHOT.jar:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.es7x.etl.ESEtlService.importData(ESEtlService.java:56) ~[na:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.es7x.ES7xAdapter.etl(ES7xAdapter.java:79) ~[na:na] 16:58:05.414 at com.alibaba.otter.canal.adapter.launcher.rest.CommonRest.etl(CommonRest.java:100) ~[client-adapter.launcher-1.1.5-SNAPSHOT.jar:na] 16:58:05.414 at com.alibaba.otter.canal.adapter.launcher.rest.CommonRest.etl(CommonRest.java:123) ~[client-adapter.launcher-1.1.5-SNAPSHOT.jar:na] 16:58:05.414 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_262] 16:58:05.414 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_262] 16:58:05.414 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_262] 16:58:05.414 at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_262] 16:58:05.414 at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) ~[spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_262] 16:58:05.414 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_262] 16:58:05.414 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.414 at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_262] 16:58:05.414 2020-11-12 16:58:02.697 [http-nio-8083-exec-8] ERROR com.alibaba.otter.canal.client.adapter.support.Util - sqlRs has error, sql: SELECT c.creator as creator, c.creator_id as creatorId, date_format(c.create_time,'%Y-%m-%d %H:%i:%S') as createTime, c.updater as updater, c.updater_id as updaterId, date_format(c.update_time,'%Y-%m-%d %H:%i:%S') as updateTime, cast(enabled_flag as SIGNED) as enabledFlag, c.trace_id as traceId, c.company_id as companyId, c.driver_id as driverId, c.driver_name as driverName, c.driver_phone as driverPhone, c.driver_id_card as driverIdCard, c.id as _id FROM company_driver c 16:58:05.414 LEFT JOIN ( SELECT t1.company_driver_id, GROUP_CONCAT( JSON_OBJECT( 'companyId',t1.company_id, 'companyDriverId',t1.company_driver_id, 'companyVehicleId',t1.company_vehicle_id, 'oftenUse',t1.often_use ) ) as json FROM company_driver_vehicle t1 LEFT JOIN company_vehicle t2 ON t2.id = t1.company_vehicle_id WHERE t1.enabled_flag=1 GROUP BY t1.company_driver_id 16:58:05.414 ) t4 ON c.id=t4.company_driver_id 16:58:05.414 2020-11-12 16:58:02.698 [http-nio-8083-exec-8] ERROR c.a.otter.canal.client.adapter.es7x.etl.ESEtlService - java.lang.RuntimeException: java.lang.NullPointerException 16:58:05.414 java.lang.RuntimeException: java.lang.RuntimeException: java.lang.NullPointerException 16:58:05.414 at com.alibaba.otter.canal.client.adapter.support.Util.sqlRS(Util.java:65) ~[client-adapter.common-1.1.5-SNAPSHOT.jar:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.es7x.etl.ESEtlService.executeSqlImport(ESEtlService.java:64) [client-adapter.es7x-1.1.5-SNAPSHOT-jar-with-dependencies.jar:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.support.AbstractEtlService.importData(AbstractEtlService.java:105) [client-adapter.common-1.1.5-SNAPSHOT.jar:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.es7x.etl.ESEtlService.importData(ESEtlService.java:56) [client-adapter.es7x-1.1.5-SNAPSHOT-jar-with-dependencies.jar:na] 16:58:05.414 at com.alibaba.otter.canal.client.adapter.es7x.ES7xAdapter.etl(ES7xAdapter.java:79) [client-adapter.es7x-1.1.5-SNAPSHOT-jar-with-dependencies.jar:na] 16:58:05.414 at com.alibaba.otter.canal.adapter.launcher.rest.CommonRest.etl(CommonRest.java:100) [client-adapter.launcher-1.1.5-SNAPSHOT.jar:na] 16:58:05.415 at com.alibaba.otter.canal.adapter.launcher.rest.CommonRest.etl(CommonRest.java:123) [client-adapter.launcher-1.1.5-SNAPSHOT.jar:na] 16:58:05.415 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_262] 16:58:05.415 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_262] 16:58:05.415 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_262] 16:58:05.415 at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_262] 16:58:05.415 at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) [tomcat-embed-core-8.5.29.jar:8.5.29] 16:58:05.415 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE] 16:58:05.415 at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [tomcat-embed-core-8.5.29.jar:8.5.29]

这排版瞬间没有看下去的欲望了

下一篇       上一篇