Java::ComSybaseJdbc3Jdbc::SybSQLException: ERROR=Attempt to insert duplicate row on line 1 of statement 'insert into netc.t2 values('NULL','NULL')': insert into netc.t2 values('NULL','NULL')

Elastic Stack Logstash
1

Hi..........
Here i am trying to insert data from Elasticsearch to sybase with the help of jdbc but i am getting following error

Java::ComSybaseJdbc3Jdbc::SybSQLException: ERROR=Attempt to insert duplicate row on line 1 of statement 'insert into netc.t2 values('NULL','NULL')': insert into netc.t2 values('NULL','NULL')
i made one of the column is prymary key and then i am trying to insert unique user names into that prymary key but

both the columns (c1,c2) are field with null values for the first event and for second event it is showing above error

here is my conf

input {
  elasticsearch {
    hosts => "192.168.1.23:9200"
    index => "titanic1"
    query => '{"_source": ["Name", "Gender"], "query": { "match": { "Gender": "female" } }, "sort": [ "_doc" ] }'
    size => 1000
    scroll => "5m"
    #docinfo => false
    #docinfo_fields => ["Gender","Age"]
    #enable_metric => false
    type => "ela"

  }
}
filter {
if [type] == "ela"
{
    jdbc {
       jdbc_driver_library => "/usr/share/java/jconn3.jar"
       jdbc_driver_class => "com.sybase.jdbc3.jdbc.SybDriver"
       jdbc_connection_string => "jdbc:sybase:Tds:192.168.1.32:4117/netc"
       jdbc_user => "root"
       jdbc_password => ""
       parameters => { "c5" => '%{Name}'
                        "c6" => '%{Gender}'
   }
       #statement => "insert into netc.t2 values(:c5,:c6)" #not worked
        #statement => 'insert into netc.t2 values("%{Name}","%{Gender}")' #not worked fetches null values

       statement => "insert into netc.t2 values(':c5',':c6')"
       target => "postgres"
   }
   }
}
output {
stdout {codec => rubydebug }
}

Thank you.

2

Wow, I did not think people would use this plugin to WRITE to a DB. Its meant for reading. Your target field postgres will receive the results of the insert but because its not a proper recordset - YMMV.

Oh well I'll try help.
Maybe this insert into t2 values(:c5, :c6)?
What does the debug log say? What does your Sybase logs say?

3

Show your stdout output.

4

If you are trying to prepare a DB with Elasticsearch data to then use this DB data to do enhancement on a different LS config, you should look at the elasticsearch filter

5

HI.......ALL
Now Somehow i am getting data into sybase
here is my conf

input {
  elasticsearch {
    hosts => "192.168.1.23:9200"
    index => "titanic1"
    query => '{"_source": ["PassengerId", "Gender"], "query": { "match": { "Gender": "female" } }, "sort": [ "_doc" ] }'
    size => 1000
    scroll => "5m"
    #docinfo => false
    #docinfo_fields => ["Gender","Age"]
    #enable_metric => false
    type => "ela"

  }
}
filter {
if [type] == "ela"
{
    jdbc {
       jdbc_driver_library => "/usr/share/java/jconn3.jar"
       jdbc_driver_class => "com.sybase.jdbc3.jdbc.SybDriver"
       jdbc_connection_string => "jdbc:sybase:Tds:192.168.1.32:4117/netc"
       jdbc_user => "root"
       jdbc_password => ""
       parameters => { "c5" => "PassengerId"
                        "c6" => "Gender"
   }
       #statement => "insert into netc.t2 values(:c5,:c6)"
       #statement => 'insert into netc.t2 values("%{Name}","%{Gender}")'
       ##statement => "insert into netc.t3(c1,c2) values(':c5',':c6')"
       statement => "insert into netc.t4 values(:c5,:c6)"
       target => "postgres"
   }
   }
   }
}
output {
stdout {codec => rubydebug }
}

with the above config i am able to push data into sybase but its giving
warning ,ERROR and _jdbcfailure in stdout
this is how stdout looks

sgpl@sgplelk5:~/logstash-5.0.1/bin$ ./logstash -f ../config/netcool3
Sending Logstash's logs to /home/sgpl/logstash-5.0.1/logs which is now configured via log4j2.properties
[2017-05-19T18:42:13,978][INFO ][logstash.pipeline        ] Starting pipeline {"id"=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>250}
[2017-05-19T18:42:13,995][INFO ][logstash.pipeline        ] Pipeline main started
[2017-05-19T18:42:14,066][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9602}
[2017-05-19T18:42:14,105][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('3','female')
[2017-05-19T18:42:14,115][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,118][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('120','female')
[2017-05-19T18:42:14,119][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('413','female')
[2017-05-19T18:42:14,122][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,124][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('152','female')
[2017-05-19T18:42:14,126][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,129][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('313','female')
[2017-05-19T18:42:14,132][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,136][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('15','female')
[2017-05-19T18:42:14,138][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,142][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('300','female')
[2017-05-19T18:42:14,149][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,151][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,154][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('330','female')
[2017-05-19T18:42:14,157][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('12','female')
[2017-05-19T18:42:14,155][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,158][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,160][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('129','female')
[2017-05-19T18:42:14,161][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('420','female')
[2017-05-19T18:42:14,162][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,163][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: JZ0R2: No result set for this query.>}
[2017-05-19T18:42:14,164][ERROR][logstash.filters.jdbc    ] Java::JavaSql::SQLException: JZ0R2: No result set for this query.: insert into netc.t4 values('424','female')
{
    "PassengerId" => "751",
     "@timestamp" => 2017-05-19T13:12:14.086Z,
       "@version" => "1",
         "Gender" => "female",
           "type" => "ela",
           "tags" => [
        [0] "_jdbcfailure"
    ]
}
{
    "PassengerId" => "650",
     "@timestamp" => 2017-05-19T13:12:15.262Z,
       "@version" => "1",
         "Gender" => "female",
           "type" => "ela",
           "tags" => [
        [0] "_jdbcfailure"
    ]
}
{
    "PassengerId" => "690",
     "@timestamp" => 2017-05-19T13:12:15.265Z,
       "@version" => "1",
         "Gender" => "female",
           "type" => "ela",
           "tags" => [
        [0] "_jdbcfailure"
    ]
}
6

this is my Sybase logs

2017-05-19T18:28:28: Error: E-OBX-102-017: Language command from root@sgplelk5 failed. Attempt to insert duplicate row on line 1 of statement 'insert into netc.t2 values('Swift, Mrs. Frederick Joel (Margaret Welles Barron)','female')'
2017-05-19T18:28:28: Error: E-OBX-102-019: Failed to execute parsed language command. (-950:Attempt to insert duplicate row)
2017-05-19T18:28:28: Error: E-OBX-102-017: Language command from root@sgplelk5 failed. Attempt to insert duplicate row on line 1 of statement 'insert into netc.t2 values('Najib, Miss. Adele Kiamie "Jane"','female')'
2017-05-19T18:28:28: Error: E-OBX-102-019: Failed to execute parsed language command. (-950:Attempt to insert duplicate row)
2017-05-19T18:28:28: Error: E-OBX-102-017: Language command from root@sgplelk5 failed. Attempt to insert duplicate row on line 1 of statement 'insert into netc.t2 values('Dahlberg, Miss. Gerda Ulrika','female')'
2017-05-19T18:32:44: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'use netc' at or near 'netc'
2017-05-19T18:32:44: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'use netc' at or near 'netc'
2017-05-19T18:32:44: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into netc.t3 values('O''Dwyer, Miss. Ellen "Nellie"','female')' at or near ''Dwyer, Miss. Ellen "Nellie"''
2017-05-19T18:32:44: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into netc.t3 values('O''Driscoll, Miss. Bridget','female')' at or near ''Driscoll, Miss. Bridget''
2017-05-19T18:32:44: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into netc.t3 values('O''Brien, Mrs. Thomas (Johanna "Hannah" Godfrey)','female')' at or near ''Brien, Mrs. Thomas (Johanna "Hannah" Go'
2017-05-19T18:32:44: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into netc.t3 values('O''Sullivan, Miss. Bridget Mary','female')' at or near ''Sullivan, Miss. Bridget Mary''
2017-05-19T18:32:45: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into netc.t3 values('O''Leary, Miss. Hanora "Norah"','female')' at or near ''Leary, Miss. Hanora "Norah"''
2017-05-19T18:39:53: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'use netc' at or near 'netc'
2017-05-19T18:42:50: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'use netc' at or near 'netc'
2017-05-19T18:42:50: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'use netc' at or near 'netc'
7

WARNING: You are trying to do somethings that the plugin was not designed to do.

The errors are because the query is not a real query.
Try adding a SELECT statement after.
statement => "insert into netc.t4 values(:c5,:c6); select * from net.t4 limit 1"

8

Hi..
here i am getting jdbc parser error like this

[2017-05-22T20:49:51,914][ERROR][logstash.filters.jdbc    ] Java::ComSybaseJdbc3Jdbc::SybSQLException: ERROR=Parse failure on line 1 of statement 'insert into alerts.status(Identifier,Summary) values('Impact:@pdc-prd-dbware01connectedMon May 15 22:14:31 2017', at or near ''': insert into alerts.status(Identifier,Summary) values('Impact:@pdc-prd-dbware01connectedMon May 15 22:14:31 2017','A Impact process  running on pdc-prd-dbware01 has connected as username root')
[2017-05-22T20:49:51,914][WARN ][logstash.filters.jdbc    ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComSybaseJdbc3Jdbc::SybSQLException: ERROR=Parse failure on line 1 of statement 'insert into alerts.status(Identifier,Summary) values('Impact:@pdc-prd-dbware01connectedMon May 15 22:14:31 2017', at or near '''>}

here is my conf

input {
  elasticsearch {
    hosts => "192.168.1.23:9200"
    index => "netcool-*"
    query => '{"_source": ["identifier", "summary"], "query": { "match_all": {} }, "sort": [ "_doc" ] }'
    size => 24
    scroll => "5m"
    type => "ela"

  }
}
filter {
if [type] == "ela"
{
    jdbc {
       jdbc_driver_library => "/usr/share/java/jconn3.jar"
       jdbc_driver_class => "com.sybase.jdbc3.jdbc.SybDriver"
       jdbc_connection_string => "jdbc:sybase:Tds:192.168.1.32:4117"
       jdbc_user => "root"
       jdbc_password => ""
       parameters => { "c5" => "identifier"
                        "c6" => "summary"
   }
       statement => "insert into alerts.status(Identifier,Summary) values(:c5,:c6)"
       target => "postgres"
   }
   }
}
output {
stdout { codec => rubydebug }
}

and this is the stdout that i am getting

{
       "summary" => "Last 5 mins alerts.details (inserts): 0\u0000",
    "identifier" => "OMNIbus ObjectServer : alerts.details DB operations stats for NCOMS_I:\u0000",
    "@timestamp" => 2017-05-22T15:19:51.688Z,
      "@version" => "1",
          "type" => "ela",
          "tags" => [
        [0] "_jdbcfailure"
    ]
}
{
       "summary" => "Disabling user impactadmin from host pdc-prd-dbware05 failure count exceeded\u0000",
    "identifier" => "Disabling user impactadmin from host pdc-prd-dbware05 failure count exceeded\u0000",
    "@timestamp" => 2017-05-22T15:19:51.688Z,
      "@version" => "1",
          "type" => "ela",
          "tags" => [
        [0] "_jdbcfailure"
    ]
}
{
       "summary" => "Last 5 mins alerts.journal (inserts): 1\u0000",
    "identifier" => "OMNIbus ObjectServer : alerts.journal DB operations stats for NCOMS_I:\u0000",
    "@timestamp" => 2017-05-22T15:19:51.688Z,
      "@version" => "1",
          "type" => "ela",
          "tags" => [
        [0] "_jdbcfailure"
    ]
}
{
       "summary" => "Attempt to login as smadmin from host pdc-prd-dbware05 failed\u0000",
    "identifier" => "Attempt to login as smadmin from host pdc-prd-dbware05 failed\u0000",
    "@timestamp" => 2017-05-22T15:19:51.688Z,
      "@version" => "1",
          "type" => "ela",
          "tags" => [
        [0] "_jdbcfailure"
    ]
}

problem is we dont have single quotes in identifier and summary fields as you can see it in stdout.

And, in sybase log file it is showing as ...,

2017-05-22T20:50:28: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into alerts.status(Identifier,Summary) values('e@C0A80120@::1:12.0:e@C0A80120@::1:12.0@pdc-prd-dbware05connectedTue May 16 03:22:55 2017' at or near '''
2017-05-22T20:50:28: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into alerts.status(Identifier,Summary) values('OMNIbus ObjectServer : alerts.status DB operations stats for NCOMS_I:' at or near '''
2017-05-22T20:50:28: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into alerts.status(Identifier,Summary) values('OMNIbus ObjectServer : Connections available for NCOMS_I:' at or near '''
2017-05-22T20:50:28: Error: E-OBX-102-016: Language command from root@sgplelk5 failed. Parse failure on line 1 of statement 'insert into alerts.status(Identifier,Summary) values('NCI:Impact:@pdc-prd-dbware01connectedMon May 15 23:23:15 2017' at or near '''

Thank you.

9

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