Pages

Google Ads

Tuesday, March 11, 2014

PDI 5.0.1 Error: Oracle / PostgreSQL / MySQL date / timestamp problem using "Dimension lookup/update"

Solution:

 DB Connection properties->Advanced->Uncheck "Supports the timestamp datatype" option.




Log error:
Timestamp : There was a data type error: the data type of java.util.Date
 
 Problem:

 This is a reproducible issue tested in the following conditions:

 OS: Mac OS X Mavericks
 MySQL: 5.5.29
 PDI: 5.0.1

 The Dimension Lookup/Update Step fails when trying to update a row. It will work fine on initial load and if there are no changes found in the target dimension table. As soon as there is any update, an error is thrown with the following text:

 Caused by: java.lang.RuntimeException: date_from Timestamp : There was a data type error: the data type of java.util.Date object [Mon Jan 01 00:00:00 EST 1900] does not correspond to value meta [Timestamp]

 Sample transformation attached

 Solution:

 Found the fix for this problem (thanks mainly to kgdeck on the forums):

 I had to go under the DB Connection properties->Advanced->Uncheck "Supports the timestamp datatype" option. All fixed.

 That being said, I'm leaving this issue open to essentially ask the question of why that was enabled by default. I don't know much about other MySQL versions or if this is a global option for all DB connection types but in my case it should have been switched off by default. At the very least, I think it might be worth noting in the Dimension Lookup/Update step docs so that others understand why they get this error. Just my two cents..

Links:

 http://forums.pentaho.com/showthread.php?156107-Oracle-date-timestamp-problem-using-quot-Dimension-lookup-update-quot

 Dimension Lookup/Update Step Fails with MySQL
 http://jira.pentaho.com/browse/PDI-11353?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

No comments: