Logstash JDBC输出插件配置报错求助：未知配置项问题

问题描述 #

通过Logstash将MySQL数据同步至PostgreSQL，运行时持续报错Unknown setting 'jdbc_user' for jdbc等未知配置项错误，Pipeline启动失败，怀疑output段的JDBC插件配置有误。

原配置文件 #

input {
  jdbc {
    jdbc_driver_library => "C:/Users/pc/Desktop/mysql-conf/mysql-connector-j-8.2.0.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/write_db"
    jdbc_user => "root"
    jdbc_password => "root"
    statement => "SELECT * FROM write_db.customer_detail WHERE verificationStatus = 'VERIFIED'"
    schedule => "*/1 * * * *"
  }
}

filter {
  # Add your filter conditions here
}

output {
  jdbc {
    jdbc_driver_library => "C:/Users/pc/Desktop/postgresql-conf/postgresql-42.5.4.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    jdbc_connection_string => "jdbc:postgresql://localhost:5432/read_db"
    jdbc_user => "postgres"
    jdbc_password => "root"
    statement => "INSERT INTO read_db.customer_detail(address, firstname, gender, lastname, phonenumber, verificationstatus) VALUES (?, ?, ?, ?, ?, ?)"
  }
}

报错信息 #

"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_user' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_user' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_password' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_password' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_driver_library' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_driver_library' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_connection_string' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_connection_string' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_driver_class' for jdbc
"2023-11-06 14:42:19 [ERROR] [jdbc]: Unknown setting 'jdbc_driver_class' for jdbc
"2023-11-06 14:42:19 [ERROR] [agent]: Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"Java::JavaLang::IllegalStateException", :message=>"Unable to configure plugins: (ConfigurationError) Something is wrong with your configuration.", :backtrace=>["org.logstash.config.ir.CompiledPipeline.<init>(CompiledPipeline.java:120)", "org.logstash.execution.AbstractPipelineExt.initialize(AbstractPipelineExt.java:186)", "org.logstash.execution.AbstractPipelineExt$INVOKER$i$initialize.call(AbstractPipelineExt$INVOKER$i$initialize.gen)", "org.jruby.internal.runtime.methods.JavaMethod$JavaMethodN.call(JavaMethod.java:847)", "org.jruby.ir.runtime.IRRuntimeHelpers.instanceSuper(IRRuntimeHelpers.java:1318)", "org.jruby.ir.instructions.InstanceSuperInstr.interpret(InstanceSuperInstr.java:139)", "org.jruby.ir.interpreter.InterpreterEngine.processCall(InterpreterEngine.java:367)", "org.jruby.ir.interpreter.StartupInterpreterEngine.interpret(StartupInterpreterEngine.java:66)", "org.jruby.internal.runtime.methods.MixedModeIRMethod.INTERPRET_METHOD(MixedModeIRMethod.java:128)", "org.jruby.internal.runtime.methods.MixedModeIRMethod.call(MixedModeIRMethod.java:115)", "org.jruby.runtime.callsite.CachingCallSite.cacheAndCall(CachingCallSite.java:452)", "org.jruby.runtime.callsite.CachingCallSite.call(CachingCallSite.java:92)", "org.jruby.RubyClass.newInstance(RubyClass.java:931)", "org.jruby.RubyClass$INVOKER$i$newInstance.call(RubyClass$INVOKER$i$newInstance.gen)", "org.jruby.runtime.callsite.CachingCallSite.cacheAndCall(CachingCallSite.java:452)", "org.jruby.runtime.callsite.CachingCallSite.call(CachingCallSite.java:92)", "org.jruby.ir.instructions.CallBase.interpret(CallBase.java:561)", "org.jruby.ir.interpreter.InterpreterEngine.processCall(InterpreterEngine.java:367)", 
"org.jruby.ir.interpreter.StartupInterpreterEngine.interpret(StartupInterpreterEngine.java:66)", "org.jruby.ir.interpreter.InterpreterEngine.interpret(InterpreterEngine.java:88)", "org.jruby.internal.runtime.methods.MixedModeIRMethod.INTERPRET_METHOD(MixedModeIRMethod.java:238)", "org.jruby.internal.runtime.methods.MixedModeIRMethod.call(MixedModeIRMethod.java:225)", "org.jruby.internal.runtime.methods.DynamicMethod.call(DynamicMethod.java:228)", "org.jruby.runtime.callsite.CachingCallSite.cacheAndCall(CachingCallSite.java:516)", "org.jruby.runtime.callsite.CachingCallSite.call(CachingCallSite.java:293)", "org.jruby.ir.interpreter.InterpreterEngine.processCall(InterpreterEngine.java:328)", "org.jruby.ir.interpreter.StartupInterpreterEngine.interpret(StartupInterpreterEngine.java:66)", "org.jruby.ir.interpreter.Interpreter.INTERPRET_BLOCK(Interpreter.java:116)", "org.jruby.runtime.MixedModeIRBlockBody.commonYieldPath(MixedModeIRBlockBody.java:136)", "org.jruby.runtime.IRBlockBody.call(IRBlockBody.java:66)", "org.jruby.runtime.IRBlockBody.call(IRBlockBody.java:58)", "org.jruby.runtime.Block.call(Block.java:143)", "org.jruby.RubyProc.call(RubyProc.java:352)", "org.jruby.internal.runtime.RubyRunnable.run(RubyRunnable.java:110)", "java.base/java.lang.Thread.run(Thread.java:1623)"]} 

问题原因 #

Logstash没有官方的JDBC Output插件，在output段使用的jdbc插件并不存在，导致所有jdbc_*配置项都被识别为无效设置，进而引发Pipeline启动失败。

解决方案 #

要实现数据写入PostgreSQL，有两种常用方案：

方案1：使用社区维护的logstash-output-jdbc插件 #

1. 先安装该插件：

bin/logstash-plugin install logstash-output-jdbc

2. 修改output段配置，该插件的配置项与input jdbc有差异，正确配置示例：

output {
  jdbc {
    driver_class => "org.postgresql.Driver"
    driver_jar_path => "C:/Users/pc/Desktop/postgresql-conf/postgresql-42.5.4.jar"
    connection_string => "jdbc:postgresql://localhost:5432/read_db?user=postgres&password=root"
    statement => "INSERT INTO read_db.customer_detail(address, firstname, gender, lastname, phonenumber, verificationstatus) VALUES (:address, :firstname, :gender, :lastname, :phonenumber, :verificationstatus)"
  }
}

• 注意：SQL语句中要用:字段名的方式引用事件中的字段，而非?占位符。
• 连接字符串需把用户名和密码直接拼在URL里，部分版本也支持单独使用username和password参数。

方案2：使用Ruby脚本输出（无需额外插件） #

如果不想安装第三方插件，可使用ruby输出插件直接编写JDBC写入逻辑：

output {
  ruby {
    code => '
      require "java"
      require "C:/Users/pc/Desktop/postgresql-conf/postgresql-42.5.4.jar"

      driver = Java::OrgPostgresql::Driver.new
      props = Java::JavaUtil::Properties.new
      props.setProperty("user", "postgres")
      props.setProperty("password", "root")
      conn = driver.connect("jdbc:postgresql://localhost:5432/read_db", props)

      stmt = conn.prepareStatement("INSERT INTO read_db.customer_detail(address, firstname, gender, lastname, phonenumber, verificationstatus) VALUES (?, ?, ?, ?, ?, ?)")
      stmt.setString(1, event.get("address"))
      stmt.setString(2, event.get("firstname"))
      stmt.setString(3, event.get("gender"))
      stmt.setString(4, event.get("lastname"))
      stmt.setString(5, event.get("phonenumber"))
      stmt.setString(6, event.get("verificationstatus"))
      stmt.executeUpdate()

      stmt.close()
      conn.close()
    '
  }
}

• 需确保字段名和事件中的字段完全匹配，避免null值引发错误。

额外注意事项 #

• 确保PostgreSQL驱动包路径正确，Logstash拥有读取权限。
• 如果是增量同步，建议在input的jdbc配置中添加use_column_value和tracking_column参数记录同步进度，避免重复同步数据。

内容的提问来源于stack exchange，提问作者Preet Bista