Jdbc input for oracle blob field error

Dear experts:

I'm using the jdbc input to load data from a oracle table to ES. There is a blob field in the table. If the blob field only have text content, it can transfer to ES. But when this filed have some binary data, such as PDF file, it will failed. The error log as below:

[2018-07-12T17:56:54,720][ERROR][logstash.pipeline ] A plugin had an unrecoverable error. Will restart this plugin.
Pipeline_id:main
Plugin: <LogStash::Inputs::Jdbc jdbc_connection_string=>"jdbc:oracle:thin:@192.168.0.1:1521:oracle", jdbc_user=>"oracle", jdbc_password=>, jdbc_driver_library=>"/data/logstash/ojdbc7.jar", jdbc_driver_class=>"Java::oracle.jdbc.driver.OracleDriver", columns_charset=>{"msg_bytes"=>"UTF-8"}, record_last_run=>true, use_column_value=>false, tracking_column=>"LOG_TIME", last_run_metadata_path=>"/data/logstash/logstash-6.3.1/last_run", clean_run=>false, jdbc_paging_enabled=>true, jdbc_page_size=>50000, jdbc_fetch_size=>5000, jdbc_validate_connection=>true, connection_retry_attempts=>5, statement_filepath=>"/data/logstash/jdbc_test.sql", add_field=>{"key_id"=>"%{msg_id}%-%{direction}-%{log_location}", "log_bytes"=>""}, type=>"jdbc", id=>"cbe228beb0a0f45de90c1aa4976decf0effd7c4a323fe0f7f209ffd03f0d1d2e", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_4678de54-517f-4429-ae4f-2e6f34b5cc74", enable_metric=>true, charset=>"UTF-8">, jdbc_validation_timeout=>3600, jdbc_pool_timeout=>5, sql_log_level=>"info", connection_retry_attempts_wait_time=>0.5, parameters=>{"sql_last_value"=>2018-07-12 17:51:16 +0800}, tracking_column_type=>"numeric", lowercase_column_names=>true>
Error: private method warn' called for nil:NilClass Exception: NoMethodError Stack: /data/logstash/logstash-6.3.1/logstash-core/lib/logstash/util/charset.rb:28:inblock in convert'
org/jruby/RubyKernel.java:1741:in tap' /data/logstash/logstash-6.3.1/logstash-core/lib/logstash/util/charset.rb:27:inconvert'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/inputs/jdbc.rb:288:in convert' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/inputs/jdbc.rb:267:inblock in execute_query'
org/jruby/RubyHash.java:1343:in each' org/jruby/RubyEnumerable.java:830:inmap'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/inputs/jdbc.rb:267:in block in execute_query' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/plugin_mixins/jdbc.rb:231:inblock in execute_statement'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/plugin_mixins/jdbc.rb:253:in block in perform_query' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/dataset/actions.rb:151:inblock in each'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/adapters/jdbc.rb:795:in process_result_set' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/adapters/jdbc.rb:726:inblock in fetch_rows'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/adapters/jdbc.rb:244:in block in execute' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/adapters/jdbc.rb:675:instatement'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/adapters/jdbc.rb:239:in block in execute' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/connection_pool/threaded.rb:91:inhold'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/database/connecting.rb:270:in synchronize' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/adapters/jdbc.rb:238:inexecute'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/dataset/actions.rb:1082:in execute' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/adapters/jdbc.rb:726:infetch_rows'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/dataset/actions.rb:151:in each' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/plugin_mixins/jdbc.rb:252:inblock in perform_query'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/extensions/pagination.rb:60:in block in each_page' org/jruby/RubyRange.java:485:ineach'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/sequel-5.9.0/lib/sequel/extensions/pagination.rb:60:in each_page' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/plugin_mixins/jdbc.rb:251:inperform_query'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/plugin_mixins/jdbc.rb:229:in execute_statement' /data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/inputs/jdbc.rb:264:inexecute_query'
/data/logstash/logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9/lib/logstash/inputs/jdbc.rb:250:in run' /data/logstash/logstash-6.3.1/logstash-core/lib/logstash/pipeline.rb:512:ininputworker'
/data/logstash/logstash-6.3.1/logstash-core/lib/logstash/pipeline.rb:505:in `block in start_input'

Who can kindly help me to solve this problem?
Or who can tell me how to configure it so that it can continue on error, not retry on error.

Who can help me?

who can give me some suggestion?

Argh. I've seen this before, and I'll dig into it in the morning. IIRC, the charset converter needs to be connected to the plugin's logger, and can fail in this way if not implemented correctly in the plugin. Are you using the latest jdbc plugin?

${LOGSTASH_HOME}/bin/logstash-plugin update logstash-input-jdbc

Hi, yaauie:

Thank you for your help. I will run the update command and test it again. But I remember it is the latest.
I don't know ruby language, but from the error message, i find it seems that the jdbc plugin call the "charset.rb" method "convert", maybe this raise the error. That mean is not because of the jdbc plugin, it because of the logstash core code.

Yosen

Hi,yaauie:

when I update the jdbc plugin, there is an error:
Updating logstash-input-jdbc
Error Bundler::InstallError, retrying 1/10
An error occurred while installing manticore (0.6.4), and Bundler cannot continue.
Make sure that gem install manticore -v '0.6.4' succeeds before bundling.
No plugin updated

Is it because of logstash version? My logstash version is 6.3.1
And I found the latest jdbc plugin version is 4.3.9, I don't know how to get the plugin version in my logstash. But In the path /logstash-6.3.1/vendor/bundle/jruby/2.3.0/gems, I can find a folder logstash-input-jdbc-4.3.9, so i think i use the latest version.

I've pushed a fix to the codec, but am waiting for a teammate to review before we publish the updated Gem.

If you want, you can edit your local gem to apply the patch manually, which will circumvent the bundler issues (we can chase those down in a separate thread if they continue to get in the way).

  • change into the current plugin's directory, likely something like:
    cd ${LOGSTASH_HOME}/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.9
    
  • download the .patch from logstash-plugins/logstash-input-jdbc#291:
    wget https://patch-diff.githubusercontent.com/raw/logstash-plugins/logstash-input-jdbc/pull/291.patch
    
  • inspect the patch that you downloaded, to make sure it's what you expect:
    cat 291.patch
    
  • apply the patch, stripping the git prefix and backing up the original file:
    patch --strip 1 --backup < 291.patch
    

Hi, yaauie:

Thank you very much. I have test it, it works. Thank you very much.
In fact ,before you update it, I have modify the jdbc.rb file, I only add a rescue statement to catch the exception. When the exception happen, it will return a message which will tell me the conversion is failed. It works, too. I find in some plugins, it provide some failure tag function. I think it is a good idea, which can let the end user know which data has some problems. So I think maybe this jdbc plugin can implement it. It just a suggestion for you and your team .

Thank you for your help again.

Yosen

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.