:sql_last_value doesn't reiterate with multiple jdbc inputs

Elastic Stack Logstash
#1

I hope my title wasn't confusing -

we have 2 jdbc inputs in our logstash config, one that pulls a commentid and one that pulls a messageid from our application.

the messageid does not get added to logstash unless I put the id into logstash.conf directly; after running logstash in debug mode and sending the output to a file, the ouput shows that the messageid used by :sql_last_value is equal to the commentid, not the messagid.

What appears to be happening is, like a variable, logstash is using the first value of :sql_last_value, instead of 'updating' it on the next sql command/jdbc input.

I should still have this ouput saved, i just havent run logstash in debug mode in a while because it is in our production environment; dev doesn't have enough usable data.

I can include this output if need be, or re-run logstash in debug mode.

I hope this was clear...

#2

Did you set the last_run_metadata_path option to unique values for each input so that Logstash doesn't read/write to the same file for both inputs?

#3

Hi there,

I've encountered the same issue as @wmedlen. I have two jdbc inputs, two distinct tables with their own ID-s and only 1 sql_last_value parameter gets stored in the .logstash_jdbc_last_run file. From your comment @magnusbaeck I realize in this case we should have two distinct metadata paths specified by which every statement will use it's own value, so thanks for clarifying. I am pretty sure that most people with multiple jdbc inputs will run into the same problem, since all the solutions on stackoverflow etc. mention adding multiple inputs but none of them mentions how to deal with the sql_last_value and the last_run_metadata_path in this case.

The funny thing is that everything's been working fine for quite a a while, apparently because it was always the lower ID from the two tables that was being saved. Only after a recent server restart have the things "turned around" and I realized that something was fishy, since recent records from one table were missing.

Although it (now :slight_smile:) makes total sense and there is an obvious workaround, I would suggest to
a) either update the docs to include some instructions regarding handling multiple inputs in one file or
b) change the behaviour of the plugin to keep separate per-input sql_last_value parameters.

If you think a) is enough I could go ahead and prepare a PR for the docs part. Let me know what you think.

Thanks in advance!

#4

Implementing b) requires some care and would probably not be backwards compatible so starting with a) sounds very reasonable.

#5

This is perfect. I simply set two separate directories with each file. By the way, simply setting the path did not work; I had to specify the filename in the logstash.conf to point to the actual file. For example,

last_run_metadata_path => "/path/to/directory"

produced this error:

:message=>"Pipeline aborted due to error", :exception=>#<Errno::EISDIR: Is a directory

but

last_run_metadata_path => "/path/to/directory/.logstash_jdbc_last_run"

worked like a charm.

I think that @dzondo is correct; while i did read over the documentation (carefully, I thought), adding a bit in there about multiple, separate paths for each jdbc input would be extremely helpful.

Thanks for all of your help guys. These forums have really helped us out a lot. I work on a very small team and have to wear many hats. We use the Elasticstack to keep logs for government mandated security procedures and this has literally saved us tons of time and money. Thanks again.

#6

Hi Kyle,

Yes, the "path" in there kind of implies that it is a directory, but as you've found out it actually points to a file. So you don't even need to keep two separate directories, if you don't want to. You can have for example:

(in query 1): last_run_metadata_path => "/path/to/directory/my_great_last_run_info_for_query_1.txt"
(in query 2): last_run_metadata_path => "/path/to/directory/my_great_last_run_info_for_query_2.txt"

Not that it really matters though :slight_smile:

Magnus,

thanks for your feedback. I'll put together a PR for the doc fix in the next day or two.

#7

Yes, the "path" in there kind of implies that it is a directory

A "path" describes the location of a resource. Nothing is said about the kind of resource.

#8

Hi, I've prepared a PR for the doc update. Hope it saves somebody troubleshooting time in the future.

#9