0

I have created a connector to database (MySQL). With a "On Table Row" and execute the project, I have this error:

ERROR 2021-06-10 12:46:59,830 [_pollingSource_LocalBD_FlowTest/executor.01] [event: ] org.mule.extension.db.internal.source.RowListener: Failed to query table 'myTable' for new rows. Value '0000-00-00' can not be represented as java.sql.Date
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
In data base there is a column "Date" with value 0000-00-00

I have installed other Driver (first the recommended (5.1.48), now the last version (8.0.25)), and other error appears:

ERROR 2021-06-10 12:52:33,153 [_pollingSource_LocalBD_FlowTest/executor.01] [event: ] org.mule.extension.db.internal.source.RowListener: Failed to query table 'myTable' for new rows. Zero date value prohibited
java.sql.SQLException: Zero date value prohibited

How can I solve it without having to make modifications to the database?

Thanks

Rodolfo
  • 5
  • 2

1 Answers1

0

That is a problem with MySQL handles dates. For some reason it allows to create a date field with a zero month which is not valid:

MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” In some cases, this is more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

MYSQL JDBC driver is correctly complaining about that, because it is not a valid date. The month can not be zero.

There is apparently a workaround, to add a parameter to the JDBC URL so the driver converts any 'zero dates' to a NULL: ?zeroDateTimeBehavior=convertToNull. This is mentioned in this previous answer.

I would recommend to avoid having that kind of zero date in the database at all but if you can't avoid it that parameter should resolve it.

aled
  • 21,330
  • 3
  • 27
  • 34
  • How can the parameter be added in Mulesoft (Anypoint Studio)? Thanks – Rodolfo Jun 11 '21 at 07:42
  • If you are using a database connection with an URL just add the string I shared to the end. If you are not using an URL, probably you need to add it as a connection property, with key name `zeroDateTimeBehavior` and value `convertToNull`. – aled Jun 14 '21 at 15:06