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

**URL:** https://discuss.elastic.co/t/logstash-output-jdbc-plugin-problems-getting-mysql-example-to-work/38921
**Category:** Logstash
**Created:** [January 11, 2016, 9:39pm UTC](https://discuss.elastic.co/t/logstash-output-jdbc-plugin-problems-getting-mysql-example-to-work/38921 "2016-01-11T21:39:16Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![jelliott](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jelliott/32/5899_2.png) [@jelliott](https://discuss.elastic.co/u/jelliott)
#### Post date: [January 11, 2016, 9:39pm UTC](https://discuss.elastic.co/t/logstash-output-jdbc-plugin-problems-getting-mysql-example-to-work/38921/1 "2016-01-11T21:39:16Z")

</div>

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](https://github.com/theangryangel/logstash-output-jdbc/blob/master/examples/mysql.md) looks like this:

```auto
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:

```auto
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

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 5:16am UTC](https://discuss.elastic.co/t/logstash-output-jdbc-plugin-problems-getting-mysql-example-to-work/38921/2 "2017-07-06T05:16:06Z")

</div>