Problem retrieving data from MySQL Database using Database Reader node - SOLVED

KNIME Analytics Platform
1

Hi all,

This is my first post. I have just started to use KNIME to implement some decision tree workflows and I am trying to retrieve data from my database but running into the following error.

ERROR     DatabaseReaderConnection           SQL Exception reading Object of type "93":

The query returns the full resultset correctly in MySQL workbench. I have downloaded the latest MySQL drivers and imported them into KNIME but it still produces the same error. I'm using Windows 8.1 64-Bit install.

Any help would be welcome.

Simon

2

Hi Simon,

thanks for posting the solution. I bet this will save some people a lot of time.

Bye,

Tobias

3

I have just found the answer, it will teach me to look a bit longer before posting next time!

This will be useful for others so I thought I would capture the solution.

It turns out Java cannot deal with the MySQL date type when it contains a value of 0000-00-00. In order to deal with this issue you need to modify your JDBC connection string in the configuration of your DatabaseReader node, appending the following argument. 

?zeroDateTimeBehavior=convertToNull

e.g. jdbc:mysql://localhost:3306?zeroDateTimeBehavior=convertToNull

Full link to solution is here http://stackoverflow.com/questions/17195343/value-0000-00-00-can-not-be-represented-as-java-sql-date

Simon

1 Like
4

If you use the “MySQL Connector” node, simply add in “JDBC Parameters” the following
Name = zeroDateTimeBehavior
Value = convertToNull

3 Likes