7
responses

Hi. I am new in NXlog. I am using om_dbi module to execute some SQL to insert my logs to pqsql. But I have the following ERROR "om_dbi failed to execute SQL statement", and NXLOG is trying to repeat this SQL again and again. How to stop this loop SQL execution after the first failure.

AskedOctober 10, 2019 - 5:13pm

Comments (7)

  • Bohdan Lisovskyi's picture

    nxlog.conf:

    # The Begin

    # Defaults
    IgnoreErrors FALSE
    define NXLOG_CACHE_DIR /data
    define NXLOG_FILE_PARSE_FORMAT none
    define NXLOG_SERVER_IP 172.17.0.4
    define NXLOG_CLIENT_LOG_PORT 4444
    define NXLOG_LOG_DIR /var/log
    define NXLOG_LOG_LEVEL INFO
    define NXLOG_SPOOL_DIR /run/nxlog
    define NXLOG_TAG nxlog
    define VERBOSE 0
    define DB_HOST *****
    define DB_USERNAME ****
    define DB_PASSWORD ****
    define DB_NAME ****

    # Overrides
    Include /etc/nxlog.d/*.conf

    # Globals
    LogLevel %NXLOG_LOG_LEVEL%
    CacheDir %NXLOG_CACHE_DIR%
    SpoolDir %NXLOG_SPOOL_DIR%

    <Extension json>
    Module xm_json
    </Extension>

    <Input client_log_parse_json>
    Module im_tcp
    Host %NXLOG_SERVER_IP%
    Port %NXLOG_CLIENT_LOG_PORT%
    <Exec>
    parse_json();
    </Exec>
    </Input>

    <Processor check_session_start_command>
    Module pm_null
    <Exec>
    log_info("COMMAND: " + $command);
    if $command != "START" drop();
    </Exec>
    </Processor>

    # Session start
    <Output sql_session_start>
    Module om_dbi
    SQL INSERT INTO session_log(id, user_id) \
    VALUES (uuid_generate_v4(), $session_id)

    Driver pgsql
    Option host %DB_HOST%
    Option username %DB_USERNAME%
    Option password %DB_PASSWORD%
    Option dbname %DB_NAME%
    </Output>

    <Route session_start_to_db>
    Path client_log_parse_json => check_session_start_command => sql_session_start
    </Route>

    nxlog.log:

    2019-10-10 15:21:40 ERROR last message repeated 704 times
    2019-10-10 15:21:40 ERROR om_dbi failed to execute SQL statement "INSERT INTO session_log(id, user_id) VALUES (uuid_generate_v4(), 'a9b3815c-6c3e-4545-b0dd-32b5f3731a22')". 3505685: ERROR: duplicate key value violates unique constraint "session_log_pkey";DETAIL: Key (id)=(a9b3815c-6c3e-4545-b0dd-32b5f3731a22) already exists.;[errorcode: 3505685]
    2019-10-10 15:21:43 ERROR last message repeated 718 times
    2019-10-10 15:21:43 ERROR om_dbi failed to execute SQL statement "INSERT INTO session_log(id, user_id) VALUES (uuid_generate_v4(), 'a9b3815c-6c3e-4545-b0dd-32b5f3731a22')". 3505685: ERROR: duplicate key value violates unique constraint "session_log_pkey";DETAIL: Key (id)=(a9b3815c-6c3e-4545-b0dd-32b5f3731a22) already exists.;[errorcode: 3505685]
    2019-10-10 15:21:46 ERROR last message repeated 668 times
    2019-10-10 15:21:46 ERROR om_dbi failed to execute SQL statement "INSERT INTO session_log(id, user_id) VALUES (uuid_generate_v4(), 'a9b3815c-6c3e-4545-b0dd-32b5f3731a22')". 3505685: ERROR: duplicate key value violates unique constraint "session_log_pkey";DETAIL: Key (id)=(a9b3815c-6c3e-4545-b0dd-32b5f3731a22) already exists.;[errorcode: 3505685]

  • manuel.munoz's picture
    (NXLog)

    Hello Bohdan,

    Have you tried...? (from https://www.postgresql.org/docs/current/runtime-config-error-handling.html)

    exit_on_error (boolean)

    If on, any error will terminate the current session. By default, this is set to off, so that only FATAL errors will terminate the session.

    # Session start
    <Output sql_session_start>
    Module om_dbi
    SQL INSERT INTO session_log(id, user_id) \
    VALUES (uuid_generate_v4(), $session_id)
    
    Driver pgsql
    Option host %DB_HOST%
    Option username %DB_USERNAME%
    Option password %DB_PASSWORD%
    Option dbname %DB_NAME%
    Option exit_on_error true
    </Output>
    

  • Bohdan Lisovskyi's picture

    Hello,
    It didn't help me. "Failed to execute SQL statement" because of constraint, and nxlog is trying to execute this statement again and again repeatedly, and of course the SQL statement always fails. Maybe I can only log the first SQL error and do not repeat it?

  • manuel.munoz's picture
    (NXLog)

    Hi Bohdan,

    Sorry, I didn't have time to test the other day, I can see now "exit_on_error" does not work.
    Unfortunately, libdbi is not very good quality, so problems using it are quite common.

    We can suggest you two options:

    1. Using om_odbc in Enterprise Edition. I have tested it and it is not showing those thousands of errors for the failing INSERT's, and in general works much better than om_dbi.
    2. Collecting in destination server using om_python plus a python script that commits the INSERT's in the DB.

Answers (0)