Logstash-output-jdbc plugin: Problems getting mysql example to work

Elastic Stack Logstash
1

I'm playing around with diverting some documents to MySql.

I thought I'd try out the logstash-output-jdbc plugin,but I'm having issues.

The mysql example looks like this:

input
{
    stdin { }
}
output {
    jdbc {
        connection_string => "jdbc:mysql://HOSTNAME/DATABASE?user=USER&password=PASSWORD"
        statement => [ "INSERT INTO log (host, timestamp, message) VALUES(?, CAST (? AS timestamp), ?)", "host", "@timestamp", "message" ]
    }
}

It's described as tested and working... but it fails for me. I'm using a similar mysql jdbc driver (5.1.38) and Logstash 2.

My first observation is that CAST(? AS timestamp) isn't legal in mysql - probably means CAST(? AS DATETIME). It certainly doesn't run as such. But when I make that change, I get:

BatchUpdateException: Data truncation: Truncated incorrect datetime value: '2016-01-11T21:32:46Z'

Note that if I just run the assembled query against my database directly, it works fine. I'm assuming that the problem is that, when preparing the statement JDBC misses the subtlety of the CAST() and decides that the string type isn't appropriate for a DATETIME column?

I took the step of putting a procedure into mysql which takes (VARCHAR, VARCHAR, VARCHAR) and then just does the INSERT with CAST; JDBC is fine with calling that procedure and passing the three values, and the CAST works as expected.

So, two questions:

  • Is the original example "tested as working" for anyone else? and
  • Any suggestions about how to go from logstash's @timestamp to a TIMESTAMP or DATETIME column without building a stored procedure first?

Thanks,
Jeff