Upper/Lower Case of Column Names and other Identifiers is not preserved in some Drivers

[PhaedrusTheGreek]

In some driver modes, such as MSSQL, the case of column names and other identifiers is not preserved. This results in output with the incorrect key names. i.e., ColumnOne is not the same as columnone.

The Sequel library has an API to change the default behavior, but the corresponding option is not available in Logstash configuration.

In my specific case, I was losing capitalization on my MSSQL Column Names. To workaround, I modified plugin_mixins/jdbc.rb, adding the following line:

@database.identifier_output_method = :to_s

under the existing Sequel.connect line.

[guyboertje]

I worked with Jay @PhaedrusTheGreek on this. The underlying problem is that Sequel has a downcase converter default that is applied to all field names when processing the resultset. An option is available on the Sequel class or the database connection to provide a different operation. The mechanism is .send(:symbol) so we can only use methods available on the String class.

Consider adding a config to give the user a way to control what case conversion is applied to the fields from the resultset, or perhaps the resulting keys in the event.

[monster-hkim]

@PhaedrusTheGreek and @guyboertje, I am using the LogStash 2.0 and modifying the plugin_mixins/jdbc.rb with the work around line added gives the following error.

 undefined method `identifier_output_method=' for nil:NilClass

[monster-hkim]

Ok, figured it out. Works like a charm!

Under the function "prepare_jdbc_connection", add the workaround like below.

@database = jdbc_connect()
@database.identifier_output_method = :to_s   <-- added

[gmoskovicz]

@guyboertje are we going to this this, or this is a jdbc mysql specific issue?

Looks like we can have an extra parameter which is identifier_output_method and add that as required. Does this makes sense? any other option on how to resolve this ?

[gmoskovicz]

According to the documentation this will happen in many different databases, identifiers will be lowercased, so we should fix this by setting to_s or nil.

[guyboertje]

@gmoskovicz - I was waiting for anyone to pick this up. I thought even Jay might create a PR.

Thinking about it though, the reason why Sequel makes it lowercase is to help with the datamapper and activerecord type frameworks that make the identifiers into Ruby method calls.

We don't do this. We make the identifiers into string Hash keys or LS Event keys. There is no reason why they need to be lowercase, is there?

Therefore we don't need a config option, we should always use to_s.

Proposal: if people create their tables with mixed case identifiers then we should reflect that in the Event that is generated.

[guyboertje]

@PhaedrusTheGreek, @gmoskovicz gimme a plus one if you agree with ^

[PhaedrusTheGreek]

+1 @guyboertje , that seems like a better default. As a workaround for any regression, i wonder if it would be possible to lowercase the identifiers in a LS filter if desired.

[gmoskovicz]

Proposal: if people create their tables with mixed case identifiers then we should reflect that in the Event that is generated.

👍

And also a 👍 for @PhaedrusTheGreek idea, the best option is to add a lowecarse_keys to deafult false. Does this makes sense?

[guyboertje]

@gmoskovicz - it makes sense to include a config option to force_lowercase_keys defaulting to false

[srinvas]

Where can I find the jdbc.rb file in logstash folder. In lib folder I have two folders bootstrap and pluginmanager but no jdbc.rb file in those folders. There is no folder like plugin_mixins.

[gmoskovicz]

@srinvas i think that this should be under .../logstash-2.0.0/vendor/bundle/jruby/1.9/gems/ inside the jdbc folder.

[guyboertje]

maybe ,,,/logstash-2.0.0/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc-2.1.0/lib/logstash/plugin_mixins/jdbc.rb

[srinvas]

Got it thanks @gmoskovicz @guyboertje