Forum Discussion

Garrett's avatar
Garrett
New Contributor II
5 years ago

Error Zero date value prohibited querying MySQL Database with MySQL snap

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.

pipeline

1 Reply

  • Garrett's avatar
    Garrett
    New Contributor II
    5 years ago

    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?