JDBC Input based on timestamp, concern regarding omitting an event

Elastic Stack Logstash
1

Hi Guys,
I'm currently looking into JDBC Input plugin for Oracle and I have one concern. Assuming my pipeline is timestamp based with the following format: yyyy-mm-dd hh:mm:ss. Let's say that there are two event that occurred at the same time and Logstash run was started at the exact same time when those two events occured. So the timeline woud look like this:

2019-04-04 09:40:02 -> DB entry 1
2019-04-04 09:40:02 -> Logstash starts
2019-04-04 09:40:02 -> DB entry 2
2019-04-04 09:40:05 -> DB entry 3
2019-04-04 09:40:06 -> Logstash starts

Since my pipeline is timestamp based, i have the following config snippet:
where timestamp > :sql_last_value

I assume that after first Logstash run at 2019-04-04 09:40:02 .logstash_jdbc_last_run will contain 2019-04-04 09:40:02 and store it as a reference. It means that second Logstash run will have the following where statement: where timestamp > 2019-04-04 09:40:02.
Does this mean that DB entry 2 will be omitted entirely by Logstash? Is that possible? Is there a time offset or a buffer that makes Logstash 'wait' few seconds to make sure that no such situation will happen?

2

My understanding is that there is always the possibility of missing rows when you use a time-based state. A sequence-based state should result in complete data.

3

The issue is there's no sequential value I could use. Timestamp is the only column that might used as a reference.

4

You need to be mindful of the UI representation of the timestamp value and the internal representation of that value.

Logstash should use the internal representation when creating the TIMESTAMP literal to inject into the statement (where `:sql_last_value is substituted)

The value received in the recordset before conversion should be a Java SQL Timestamp object created by the JDBC driver for the DB technology in use. This Java SQL Timestamp is converted to a Logstash Timestamp object instance with millisecond precision. I say should be, because it will as long as the column data type is a Timestamp.

For Oracle
https://docs.oracle.com/cd/B19306_01/java.102/b14188/datamap.htm
https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006050

5

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