Error Zero date value prohibited querying MySQL Database with MySQL snap
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-21-2021 08:12 AM
I am pulling a MySQL query that works when querying the database but I am getting the following error in SnapLogic.
Zero date value prohibited
Pipeline did not complete successfully
Resolution:
Fix the child pipeline errors and try again
Reason:
Snap errors: {ruuid=84507a5f-350c-4e73-9412-696d2af1199a, label=MySQL - Execute, failure=Failed to execute query, reason=SQL [SELECT * FROM my$table]; Zero date value prohibited, resolution=Please address reported issue.}
There are no Null dates in the data set that I can find.
And I am not converting these to a date field I am converting them to JSON.
Thanks for your help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-21-2021 08:35 AM
Some info I found:
Here are two simple and short solution which could resolve this error for you.
- You could possibly change your database schema, to allow NULL values.
UPDATE table SET datefield = NULL WHERE datefield = ‘0000-00-00 00:00:00’;
- In your datasource configuration, you can edit JDBC URL by setting a parameter called ‘zeroDateTimeBehavior’ to ‘convertToNull’.
jdbc:mysql://localhost:3306/yourMySqlDatabase?zeroDateTimeBehavior=convertToNull
Is there a way I can set that convertToNull piece into the call on the Snap?
