java.sql.SQLException: Zero date value prohibited

As we know, MySQL supports a "zero" value for date types, as stated in the documentation:

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 5.7 Reference Manual

However, some database drivers, such as JDBC in our case, do not support "zero" dates. This can result in exceptions being thrown, which can interrupt the TDK transformation process, such as the following: java.sql.SQLException: Zero date value prohibited.

To handle that case, add some additional JDBC parameters. For the input database, add this JDBC parameter: ?zeroDateTimeBehavior=round.

An example of URL for the input database:

jdbc:mysql://127.0.0.1:6000/sakila?zeroDateTimeBehavior=round

For the output database, add these JDBC parameters: ?sessionVariables=sql_mode=''&jdbcCompliantTruncation=false.

An example URL for the output database:

jdbc:mysql://127.0.0.1:6001/sakila?sessionVariables=sql_mode=''&jdbcCompliantTruncation=false

Here’s a full example of the command for starting TDK transformation with handling "zero" date:

tdk \
    -iu jdbc:mysql://127.0.0.1:6000/sakila?zeroDateTimeBehavior=ROUND&sessionVariables=sql_mode='' \
    -iU root -ip admin \
    -ou jdbc:mysql://127.0.0.1:6001/sakila?zeroDateTimeBehavior=ROUND&sessionVariables=sql_mode=''&jdbcCompliantTruncation=false \
    -oU root -op admin \
    -c config.tdk.yaml