cancel
Showing results for 
Search instead for 
Did you mean: 

Error Zero date value prohibited querying MySQL Database with MySQL snap

Garrett
New Contributor II

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.

1 REPLY 1

Garrett
New Contributor II

Some info I found:

Here are two simple and short solution which could resolve this error for you.

  1. You could possibly change your database schema, to allow NULL values.

UPDATE table SET datefield = NULL WHERE datefield = ‘0000-00-00 00:00:00’;

  1. 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?