How to connect to cockroachDB using logstash jdbc output plugin?

[Archith09]

I am using logstash to create a pipeline from Postgres to CockroachDB. Below is the config.

input {
jdbc {
    jdbc_connection_string => "jdbc:postgresql://<host_ip>:5432/database"
    jdbc_user => "user"
    jdbc_password => "password"
    jdbc_driver_library => "/path/to/postgresql-9.4-1209.jdbc41.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    statement => "SELECT * FROM source_table"
    }
}
output {
    jdbc {
    connection_string => "jdbc:postgresql://<host_ip>:26257/database"
    driver_jar_path => "/path/to/postgresql-9.4-1209.jdbc41.jar"
    driver_class => "org.postgresql.Driver"
    driver_auto_commit => false
    statement => ["INSERT INTO dest_table(col1,col2) VALUES (?,?)","col1","col2"]
    }
}

The input plugin(source is postgres) is working fine. But I am unable to establish a connection in the output plugin(cockroachDB) using JDBC. I am facing the below error.

JDBC - Connection is not valid. Please check connection string or that your JDBC endpoint is available. {:level=>:error, :file=>"logstash/outputs/jdbc.rb", :line=>"154", :method=>"setup_and_test_pool!"}

Destination(cockroachDB) is open for connection at the specified ip and port.

Nmap scan report for <host>
Host is up (0.000070s latency).
PORT      STATE SERVICE
26257/tcp open  unknown

Nmap done: 1 IP address (1 host up) scanned in 0.05 seconds

As cockroachDB JDBC connection string is very similar to postgres, I tried the below connection strings, and still the same error.

jdbc:postgresql://host/database
jdbc:postgresql://host/database?sslmode=disable
jdbc:postgresql://host:port/database
jdbc:postgresql://host:port/database?sslmode=disable

[theangryangel]

What version are you using of the plugin?

Also can you try increasing the timeout (it's one of the options for the plugin).

The error you're getting is basically the connection pool tries to connect and make a test query within this timeout. If it fails during its setup the plugin fails with this message to prevent things from looking like they're fine

[knz]

Note that CockroachDB's story about authentication is limited. The password field for example is not used. Unless you created a user named "user" beforehand, chances are you need to use the user name "root" instead; otherwise the connection may be refused.

However it seems more likely here that your JDBC driver is not registered.

[theangryangel]

Thanks for you input @knz - typically if the JDBC driver failed to load I'd expect unable to get driver instance for.... thrown by Hikari before the connection is tested though :(

@Archith09 I may have sometime Wednesday to stand up a cockroach install. I'll take a look and see what I can figure out if you've not managed to figure it out before then

[Archith09]

@theangryangel Thanks for the inputs. I am using logstash 2.3.1 and latest version of jdbc output plugin. I tried with logstash 2.3.4 as well but does not seem to work. As suggested, I extended the connection pool timeout but in vain. And yes, it would be of great help if you could take a look at it but hopefully I figure it out by then.

@knz Thanks for the input. I am using an insecure cluster in cockroachdb and hence I am not using any authentication parameters in the output plugin. Also, could you please enlighten me on how to register the driver in logstash?

[knz]

I've never used logstash. For pure Java I put my answer here: http://stackoverflow.com/questions/39047933/how-do-i-connect-to-cockroachdb-through-jdbc-from-logstash-output-plugin

[Archith09]

@knz thank you!

[theangryangel]

@Archith09 It is logging an error that the connection is invalid, however it is working (inserting data) when I use this configuration (this screen capture shows it working):

input
{
    stdin { }
}
output {
    jdbc {
        driver_jar_path => '/opt/postgresql-9.4.1209.jre6.jar'
        connection_string => 'jdbc:postgresql://127.0.0.1:26257/test?user=root'
        statement => [ "INSERT INTO log (host, timestamp, message) VALUES(?, CAST (? AS timestamp), ?)", "host", "@timestamp", "message" ]
    }
}

The reason why it's logging the error is because cockroach isn't responding to the isValid call on this line, as it's expected.

I'll alter the message so that its a warning, and says that maybe the driver isn't responding as expected.

[theangryangel]

I've just pushed v0.3.1 to rubygems which adds the connection_test configuration parameter which suppresses this "error".

Edit:
@Archith09 I've closed this as I think there's not much I can do with this one. The plugin is working for me, and the error isn't really an error. If you're still having problems, let me know and I'll do what I can to help.

[Archith09]

@theangryangel It is working for me too with this configuration. Thanks for your help. Appreciate it!