4
responses

Hi i am trying to use the im_odbc module with nxlog-4.1.4016 running on a windows 2016 server. but it seems like i am in an loop. I looked at the documentation and took notice of the required id field, but the output is always the same ID, as if the position (savepos ) is not saved.

sqlserver : 14.0.2002.14

this is my minimalized input config

Module im_odbc
SavePos TRUE
ConnectionString DSN=dbserver;database=db;UID=sa;Pwd=password;
SQL select Id as id from table where id = ?
Exec $Level = "INFO";

this is my output config
Module om_file
File "D:\_Data\log_null_output.log"

this is the output of the file
2018-09-18 16:39:49 adss-dbserver INFO
id: 26335
2018-09-18 16:39:50 adss-dbserver INFO
id: 26335
2018-09-18 16:39:51 adss-dbserver INFO
id: 26335
2018-09-18 16:39:52 adss-dbserver INFO
id: 26335

debug logging nxlog
2018-09-18 17:26:14 DEBUG CONFIG: adss_sql
2018-09-18 17:26:14 DEBUG SQL: select Id as id from SigningTransactionLogs where id = ?
2018-09-18 17:26:14 DEBUG nx_expr_new_field: Level
2018-09-18 17:26:14 DEBUG field declared at line 9, character 12 in C:\Program Files\nxlog\conf\inputs\adss_sql.in
2018-09-18 17:26:14 DEBUG left_value expr
2018-09-18 17:26:14 DEBUG adding string [INFO]
2018-09-18 17:26:14 DEBUG string literal declared at line 9, character 21 in C:\Program Files\nxlog\conf\inputs\adss_sql.in
2018-09-18 17:26:14 DEBUG literal
2018-09-18 17:26:14 DEBUG assignment declared at line 9, character 22 in C:\Program Files\nxlog\conf\inputs\adss_sql.in
2018-09-18 17:26:14 DEBUG assignment: left_value = expr
2018-09-18 17:26:14 DEBUG statement: assignment
2018-09-18 17:26:14 DEBUG finished parsing statements
....
2018-09-18 17:26:16 DEBUG new event in event_thread [adss_sql:READ]
2018-09-18 17:26:16 DEBUG nx_event_to_jobqueue: READ (adss_sql)
2018-09-18 17:26:16 DEBUG event added to jobqueue
2018-09-18 17:26:16 DEBUG future event, event thread sleeping -697014233us in cond_timedwait
2018-09-18 17:26:16 DEBUG worker 0 got signal for new job
2018-09-18 17:26:16 DEBUG worker 0 processing event 0x164caf0
2018-09-18 17:26:16 DEBUG PROCESS_EVENT: READ (adss_sql)
2018-09-18 17:26:16 DEBUG odbc read
2018-09-18 17:26:16 DEBUG im_odbc_execute with last id 0
2018-09-18 17:26:16 DEBUG resultset column: id (bigint identity)
2018-09-18 17:26:16 DEBUG fetching from id 0
2018-09-18 17:26:16 DEBUG NO DATA
2018-09-18 17:26:16 DEBUG worker 0 waiting for new event
2018-09-18 17:26:16 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:16 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:16 DEBUG new event in event_thread [adss_sql:READ]
2018-09-18 17:26:16 DEBUG future event, event thread sleeping 1000000us in cond_timedwait
2018-09-18 17:26:17 DEBUG event thread wait time is up (after 1000000us)
2018-09-18 17:26:17 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:17 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:17 DEBUG new event in event_thread [adss_sql:READ]
2018-09-18 17:26:17 DEBUG nx_event_to_jobqueue: READ (adss_sql)
2018-09-18 17:26:17 DEBUG event added to jobqueue
2018-09-18 17:26:17 DEBUG future event, event thread sleeping -698029889us in cond_timedwait
2018-09-18 17:26:17 DEBUG worker 1 got signal for new job
2018-09-18 17:26:17 DEBUG worker 1 processing event 0x164c910
2018-09-18 17:26:17 DEBUG PROCESS_EVENT: READ (adss_sql)
2018-09-18 17:26:17 DEBUG odbc read
2018-09-18 17:26:17 DEBUG im_odbc_execute with last id 0
2018-09-18 17:26:17 DEBUG resultset column: id (bigint identity)
2018-09-18 17:26:17 DEBUG fetching from id 0
2018-09-18 17:26:17 DEBUG NO DATA

AskedSeptember 18, 2018 - 5:17pm

Answer (1)

Your query is SQL select Id as id from table where id = ?

It's pretty obvious that it starts with 0 and will keep returning 0 for the id column since this is what you are telling it to do via where id = ?.

Comments (3)

  • w.schmitt@evidos.nl's picture

    but the documentation states :

    Note that WHERE RecordNumber > ? is crucial: without this clause the module will read logs in an endless loop. The result set returned by the select must contain this id column which is then stored and used for the next query.

    so i expect the ? to be replaced by the value returned by the previous run.

    I've tried to exclude the where id = ? but this results in, the expected, loop. i,ve also tried where id = 20 wich is a existing record in the db, but the result is a loop with id =20

    i must be looking at this from the wrong angle because it looks pretty straight forward to me.

  • b0ti's picture
    (NXLog)

    where id = ? alway selects the same value. Since NXLog does not increment the id (because it might not be a serial) you end up with a loop.

    Instead new data is retrieved with the logic of select rows where id is greater than the last known id, i.e. you need >.