Hi all,

Just to close this thread, Gabi and I worked offline and looked at the underlying data and we found timestamp values "0000-00-00 00:00:00" in the database.

Kyle pointed us at:

The fix was to update all of those and set them to NULL values in MySQL itself.  E.g. issue the likes of the following:

UPDATE mytable SET field = NULL where field='0000-00-00 00:00:00';

We should look at handling this more gracefully, and perhaps providing better logging in the IPT of course, but there is a workaround for anyone stumbling on this.

Thanks,
Tim

On Oct 17, 2013, at 3:41 PM, Tim Robertson [GBIF] wrote:

Hi Gabe,

I will probably start with this:
  http://stackoverflow.com/questions/11133759/0000-00-00-000000-can-not-be-represented-as-java-sql-timestamp-error

E.g. adding null handling behavior to the JDBC connection string:


down voteaccepted

You can use this JDBC URL directly in your data source configuration:

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull


I'll get set up, but that might be something you could test quicker than me (add ?zeroDateTimeBehavior=convertToNull to the connection string for the DB).

Cheers,
Tim




On Oct 17, 2013, at 3:37 PM, Tim Robertson [GBIF] wrote:

Thanks Gabe,

Can you send some sample data that shows the issue please?  If you want to send me it privately that is also fine and I will use it only for this test and then delete it.

This looks somewhat suspicious from the logs:

java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)

I now suspect null handling issues (e.g. null dates are not handled) if all the content is sound.

Cheers,
Tim


On Oct 17, 2013, at 3:33 PM, Dröge, Gabriele wrote:

Hi Tim,
 
please find attached the log file, the last two entries are the critical ones.
 
Timespan error appears only when running the whole sql statement (with some joins). The other error (columns) only when running a short sql statement (without joins). In both cases no data can be provided via IPT.
 
The problem is not the database, since we have tested it with several other databases and different machines too and it didn’t work.
 
This is a small sql script that didn’t work: SELECT dnabanknumbers.ID_DNA, dnabanknumbers.DNA_Bank_Number, dnabanknumbers.GUID from dnabanknumbers
 
Attached is a small sample table.
 
I’ve changed all datetime-columns to timestamp now but the same error appears in the log.
 
Best,
Gabi
 
Von: Tim Robertson [GBIF] [mailto:trobertson@gbif.org] 
Gesendet: Donnerstag, 17. Oktober 2013 14:57
An: Dröge, Gabriele
Cc: ipt@lists.gbif.org
Betreff: Re: [IPT] IPT bug -> MySQL
 
Hi Gabi,
 
Thanks for reporting the issue.  To make it easy for us to help you as quickly as possible could you please provide the following?
  i) the actual log lines from the IPT logs
  ii) the SQL statement you are using
  iii) a small sample table so we can recreate the issue
 
This is a wild guess, but I suspect tat dateTime formats is not being handled gracefully.  If possible can you please try timestamp SQL column format instead of dateTime to see if that narrows down the cause? (I understand this is not a viable fix, but might help identify the root of the issue)
 
Thanks,
Tim
 
On Oct 17, 2013, at 2:44 PM, Dröge, Gabriele wrote:


Dear all,
 
I’ve downloaded the latest IPT version 2.0.5-r4398 and tried to map my data.
I want to use a MySQL database, but there seems to be a bug in IPT concerning MySQL.
IPT is not able to handle the SQL statement even though it is correct (tested on the MySQL server directly)
Log file says:
1.       Error in SQL (there is definitely no error in the sql statement)
2.       IPT expects one more column than the sql statement queries for (unclear why)
3.       IPT can’t handle datetime (unclear why)
 
A CSV file and a connection to a MS SQL Server did work.
 
Preview looks like this:
 
<image001.jpg>
 
 
It would be great if someone could fix those bugs.
Thanks in advance!
 
Best wishes,
Gabi
 
-----------------------------------------------------------------
Gabriele Droege
Coordinator - DNA Bank Network
Global Genome Biodiversity Network (GGBN)
Berlin-Dahlem DNA Bank
Deputy Women's Officer ZE BGBM
 
Botanic Garden and Botanical Museum Berlin-Dahlem
Freie Universität Berlin
Koenigin-Luise-Str. 6-8
14195 Berlin
Germany
 
+49 30 838 50 139
 
_______________________________________________
IPT mailing list
IPT@lists.gbif.org
http://lists.gbif.org/mailman/listinfo/ipt
 
<debug.log><dnabanknumbers.sql>_______________________________________________
IPT mailing list
IPT@lists.gbif.org
http://lists.gbif.org/mailman/listinfo/ipt

_______________________________________________
IPT mailing list
IPT@lists.gbif.org
http://lists.gbif.org/mailman/listinfo/ipt

_______________________________________________
IPT mailing list
IPT@lists.gbif.org
http://lists.gbif.org/mailman/listinfo/ipt