Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

es的数据库表和es的映射yml文件有误，etlCondition按照官方文档配置出现:ES 数据导入异常 =>java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0) #3149

Open
2 tasks done
liuxuzxx opened this issue Nov 4, 2020 · 6 comments
Open
2 tasks done

es的数据库表和es的映射yml文件有误，etlCondition按照官方文档配置出现:ES 数据导入异常 =>java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0) #3149

liuxuzxx opened this issue Nov 4, 2020 · 6 comments

Comments

@liuxuzxx
Copy link

liuxuzxx commented Nov 4, 2020 •
edited
Loading

  • I have searched the issues of this repository and believe that this is not a duplicate.
  • I have checked the FAQ of this repository and believe that this is not a duplicate.

environment

  • canal version 1.1.5-SNAPSHOT
  • mysql version 8.0.22
  • ES version 7.9.2

Issue Description

当按照官放文档配置数据表和es的映射文件yml的时候，在进行全数据同步的时候出现sql解析错误

dataSourceKey: defaultDS        # 源数据源的key, 对应上面配置的srcDataSources中的值
outerAdapterKey: exampleKey     # 对应application.yml中es配置的key 
destination: example            # cannal的instance或者MQ的topic
groupId:                        # 对应MQ模式下的groupId, 只会同步对应groupId的数据
esMapping:
  ......省略了啊
  sql: "自己的SQL语句"
  etlCondition: "where a.c_time>='{0}'"     # etl 的条件参数 这种配置方式会报错
  commitBatch: 3000

Steps to reproduce

1.按照官方文档配置过滤的where条件语句
2.执行curl http://127.0.0.1:8081/etl/es7/gsms_short_link_1009.yml -X POST params="2020-10-10 12:25:00"

Expected behaviour

正常执行，数据正常插入

Actual behaviour

{
  "succeeded": false,
  "errorMessage": "ES 数据导入异常 =>java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)."
}

If there is an exception, please attach the exception trace:
后台的异常信息

java.lang.RuntimeException: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
	at com.alibaba.otter.canal.client.adapter.support.Util.sqlRS(Util.java:66) ~[classes/:na]
	at com.alibaba.otter.canal.client.adapter.support.AbstractEtlService.importData(AbstractEtlService.java:62) ~[classes/:na]
	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]
	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]
	at com.alibaba.otter.canal.adapter.launcher.rest.CommonRest.etl(CommonRest.java:100) [classes/:na]
	at com.alibaba.otter.canal.adapter.launcher.rest.CommonRest.etl(CommonRest.java:123) [classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_231]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_231]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_231]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_231]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) [spring-webmvc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-embed-websocket-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.5.RELEASE.jar:5.0.5.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_231]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_231]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.29.jar:8.5.29]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_231]
Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3382) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3367) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4083) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3586) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.mysql.jdbc.JDBC42PreparedStatement.setObject(JDBC42PreparedStatement.java:68) ~[mysql-connector-java-5.1.48.jar:5.1.48]
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.setObject(DruidPooledPreparedStatement.java:480) ~[druid-1.2.1.jar:1.2.1]
	at com.alibaba.otter.canal.client.adapter.support.Util.sqlRS(Util.java:57) ~[classes/:na]
	... 59 common frames omitted
@liuxuzxx
Copy link
Author

liuxuzxx commented Nov 4, 2020

跟踪了下代码，发现源码是这么处理的：

1.替换{}为?

for (String param : params) {
    etlCondition = etlCondition.replace("{}", "?");//{}替换成?,并不是{0}替换成?
    values.add(param);
}

2.利用PrepareStatement开始预编译装填参数

try (PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)) {
    pstmt.setFetchSize(Integer.MIN_VALUE);
    if (values != null) {
        for (int i = 0; i < values.size(); i++) {
            pstmt.setObject(i + 1, values.get(i));//这句话就是替换 ? 所站位的参数
        }
    }
    try (ResultSet rs = pstmt.executeQuery()) {
        return fun.apply(rs);
    }
}

顺便说一下，多个参数的解析方式:

在com.alibaba.otter.canal.adapter.launcher.rest.CommonRest类里面的

    @PostMapping("/etl/{type}/{key}/{task}")
    public EtlResult etl(@PathVariable String type, @PathVariable String key, @PathVariable String task,
                         @RequestParam(name = "params", required = false) String params) {
        .....处理其他进程正在导入的问题
        try {

           ...省略，主要是处理锁的问题
            try {
                List<String> paramArray = null;
                if (params != null) {
                    paramArray = Arrays.asList(params.trim().split(";"));//处理多个参数的时候，参数就是一个字符串，然后用 ;(英文分号) 分割
                }
                return adapter.etl(task, paramArray);
            } finally {
               ....省略，处理任务装填
            }
        } finally {
            etlLock.unlock(ETL_LOCK_ZK_NODE + type + "-" + lockKey);
        }
    }

总结下配置多个参数的形式

yml的配置:
dataSourceKey: defaultDS        # 源数据源的key, 对应上面配置的srcDataSources中的值
outerAdapterKey: exampleKey     # 对应application.yml中es配置的key 
destination: example            # cannal的instance或者MQ的topic
groupId:                        # 对应MQ模式下的groupId, 只会同步对应groupId的数据
esMapping:
  ......省略了啊
  sql: "自己的SQL语句"
  etlCondition: "where a.c_time>={} and xx = {} and {} {} {}" //注意{} 外面不要加 ''，官方文档的 '{0}'有问题，即使写成'{}'还是有问题，因为替换之后就变成了'?'，然后PrepareStatement会把'?'当做一个字符串，并不会替换    
  commitBatch: 3000 

curl请求访问：
curl http://127.0.0.1:8081/etl/hbase/mytest_person2.yml -X POST -d "params=参数1;参数2;参数3;参数4;参数5;............"

@nielizixiu
Copy link

感谢 liuxuzxx 解决了我的困惑~， 话说官方文档有这问题还真是头疼

@geekerstar
Copy link

感谢

@Mediterranean90
Copy link

请问，带etlCondition的yml文件，放到es7下面，也不会增量同步是吧

@liuxuzxx
Copy link
Author

liuxuzxx commented Nov 1, 2022

请问，带etlCondition的yml文件，放到es7下面，也不会增量同步是吧

应该可以正常同步，当时只是看到官方的说明文档有点问题！其实并不太影响功能的使用

@cynen
Copy link

cynen commented Jul 17, 2024

感谢,解决了卡我好久的问题.

@cynen cynen mentioned this issue Jul 17, 2024
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@Mediterranean90 @geekerstar @liuxuzxx @cynen @nielizixiu