I'm trying to use the jdbc_static
filter in my logstash pipeline, but I am getting the following error:
[2018-12-04T10:07:38,952][ERROR][logstash.filters.jdbc.readwritedatabase] Exception when filling lookup db from loader remote-interfaces, query results, original exception: Sequel::DatabaseError, original message: Java::JavaSql::SQLException: Import error on line 1 of file C:/Windows/TEMP/logstash/jdbc_static/import_data/interfaces: Invalid character string format for type INTEGER. {:backtrace=>["org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.Util.seeNextException(org/apache/derby/impl/jdbc/Util)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.EmbedConnection.handleException(org/apache/derby/impl/jdbc/EmbedConnection)", "org.apache.derby.impl.jdbc.ConnectionChild.handleException(org/apache/derby/impl/jdbc/ConnectionChild)", "org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:453)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:314)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.adapters.jdbc.block in execute(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/adapters/jdbc.rb:248)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.database.logging.log_connection_yield(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/database/logging.rb:38)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.adapters.jdbc.block in execute(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/adapters/jdbc.rb:248)"]}
the loader it is complaining about has this config:
{
id => "remote-interfaces"
query => "select id, name, device_id, virtual_machine_id from public.dcim_interface order by id"
local_table => "interfaces"
}
and the local db object has this config
{
name => "interfaces"
index_columns => [ "id", "device_id", "virtual_machine_id" ]
columns => [
[ "id", "integer" ],
[ "name", "varchar(64)" ],
[ "device_id", "integer" ],
[ "virtual_machine_id", "integer" ]
]
}
on the postgresql database, the same query returns this:
netbox=# select id, name, device_id, virtual_machine_id from public.dcim_interface order by id;
id | name | device_id | virtual_machine_id
----+----------+-----------+--------------------
1 | a1 | 1 |
2 | Ethernet | | 1
(2 rows)
I think the issue is that the integer fields are returning NULL
and so the apache derby db is complaining about it. What do I have to do to allow NULL values? Thanks