Hi fam,
I am using an Athena Federated Query to MySQL as database connection in Metabase. In MySQL we can have zero date. But in Metabase I get this error:
[Simba]AthenaJDBC An error has been thrown from the AWS Athena client. GENERIC_USER_ERROR: Encountered an exception[java.lang.RuntimeException] from your LambdaFunction[arn:aws:lambda:***] executed in context[S3SpillLocation{bucket='***', key='***', directory=true}] with message[java.sql.SQLException: Zero date value prohibited] [Execution ID: c8012456-ba01-45ad-8ab4-dcb2f8a5934e]
How can I fix this issue?
I tried the following in connection string:
opened 09:46AM - 18 Aug 22 UTC
closed 11:07AM - 18 Aug 22 UTC
Connector - AthenaMySQLConnector — version 2022.32.1
Error While trying to SE… LECT from MySQL table:
```2022-08-18 09:36:54 1c4b5f64-b580-4ae7-ab3f-2ed65abaf28c WARN CompositeHandler:107 - handleRequest: Completed with an exception.
java.lang.RuntimeException: java.sql.SQLException: Zero date value prohibited
at com.amazonaws.athena.connector.lambda.data.S3BlockSpiller.writeRows(S3BlockSpiller.java:186) ~[task/:?]
at com.amazonaws.athena.connectors.jdbc.manager.JdbcRecordHandler.readWithConstraint(JdbcRecordHandler.java:161) ~[task/:?]
at com.amazonaws.athena.connectors.jdbc.MultiplexingJdbcRecordHandler.readWithConstraint(MultiplexingJdbcRecordHandler.java:87) ~[task/:?]
at com.amazonaws.athena.connector.lambda.handlers.RecordHandler.doReadRecords(RecordHandler.java:192) ~[task/:?]
at com.amazonaws.athena.connector.lambda.handlers.RecordHandler.doHandleRequest(RecordHandler.java:158) ~[task/:?]
at com.amazonaws.athena.connector.lambda.handlers.CompositeHandler.handleRequest(CompositeHandler.java:138) ~[task/:?]
at com.amazonaws.athena.connector.lambda.handlers.CompositeHandler.handleRequest(CompositeHandler.java:103) [task/:?]
at lambdainternal.EventHandlerLoader$2.call(EventHandlerLoader.java:899) [aws-lambda-java-runtime-0.2.0.jar:?]
at lambdainternal.AWSLambda.startRuntime(AWSLambda.java:268) [aws-lambda-java-runtime-0.2.0.jar:?]
at lambdainternal.AWSLambda.startRuntime(AWSLambda.java:206) [aws-lambda-java-runtime-0.2.0.jar:?]
at lambdainternal.AWSLambda.main(AWSLambda.java:200) [aws-lambda-java-runtime-0.2.0.jar:?]
Caused by: java.sql.SQLException: Zero date value prohibited
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[task/:?]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[task/:?]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[task/:?]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[task/:?]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[task/:?]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:99) ~[task/:?]
at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:947) ~[task/:?]
at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:985) ~[task/:?]
at com.amazonaws.athena.connectors.jdbc.manager.JdbcRecordHandler.lambda$makeExtractor$13(JdbcRecordHandler.java:272) ~[task/:?]
at com.amazonaws.athena.connector.lambda.data.writers.fieldwriters.DateMilliFieldWriter.write(DateMilliFieldWriter.java:80) ~[task/:?]
at com.amazonaws.athena.connector.lambda.data.writers.GeneratedRowWriter.writeRow(GeneratedRowWriter.java:116) ~[task/:?]
at com.amazonaws.athena.connectors.jdbc.manager.JdbcRecordHandler.lambda$readWithConstraint$0(JdbcRecordHandler.java:161) ~[task/:?]
at com.amazonaws.athena.connector.lambda.data.S3BlockSpiller.writeRows(S3BlockSpiller.java:183) ~[task/:?]
... 10 more
Caused by: com.mysql.cj.exceptions.DataReadException: Zero date value prohibited
at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:143) ~[task/:?]
at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:51) ~[task/:?]
at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromTimestamp(AbstractDateTimeValueFactory.java:89) ~[task/:?]
at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTimestamp(MysqlTextValueDecoder.java:85) ~[task/:?]
at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:89) ~[task/:?]
at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:243) ~[task/:?]
at com.mysql.cj.protocol.a.result.TextBufferRow.getValue(TextBufferRow.java:132) ~[task/:?]
at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:947) ~[task/:?]
at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:985) ~[task/:?]
at com.amazonaws.athena.connectors.jdbc.manager.JdbcRecordHandler.lambda$makeExtractor$13(JdbcRecordHandler.java:272) ~[task/:?]
at com.amazonaws.athena.connector.lambda.data.writers.fieldwriters.DateMilliFieldWriter.write(DateMilliFieldWriter.java:80) ~[task/:?]
at com.amazonaws.athena.connector.lambda.data.writers.GeneratedRowWriter.writeRow(GeneratedRowWriter.java:116) ~[task/:?]
at com.amazonaws.athena.connectors.jdbc.manager.JdbcRecordHandler.lambda$readWithConstraint$0(JdbcRecordHandler.java:161) ~[task/:?]
at com.amazonaws.athena.connector.lambda.data.S3BlockSpiller.writeRows(S3BlockSpiller.java:183) ~[task/:?]
... 10 more
```
Tried using `zerodatetimebehavior=Converttonull` in JDBC connection string. Still the same error.
zeroDateTimeBehavior=convertToNull;autoReconnect=true;characterEncoding=UTF-8;characterSetResults=UTF-8
It did not fix the issue.