🐛 Source MySQL: java.sql.SQLException: DAY_OF_MONTH

[marcosmarxm]

This Github issue is synchronized with Zendesk:

Ticket ID: #1724
Priority: normal
Group: Community Assistance Engineer
Assignee: Nataly Merezhuk

Original ticket description:

• Is this your first time deploying Airbyte?: Yes
• Memory / Disk / CPU: 16Gb ram, 50gb, 8core / 16 logical processor
• Deployment: Docker Desktop
• Airbyte Version: 0.39.37-alpha
• Source name/version: MySQL 0.5.6
• Destination name/version: Postgres 14
• Step: Issue is happening on sync
• Description: I have two connectors with mysql and both keep failing either with sql exception of MONTH or YEAR for the either.

logs-13.txt (1.0 MB)

[Discourse post]

[marcosmarxm]

Comment made from Zendesk by Marcos Marx on 2022-07-27 at 08:41:

I did additional tests by creating a view from the original table without date related fields (strings) and sync was successful, there seems to be an issue with one of the date columns regarding to the data maybe.

Not sure how and what to find.

[Discourse post]

[marcosmarxm]

Comment made from Zendesk by Nataly Merezhuk on 2022-07-27 at 13:19:

Hello and welcome to the Airbyte community, @antonapua! It looks like you may be passing the wrong format in date related fields. Could you try using an instance of LocalDate instead of a string?

[marcosmarxm]

Comment made from Zendesk by Marcos Marx on 2022-07-28 at 04:20:

Hi @natalyjazzviolin,

I am not sure what LocalDate you’re referring to, is that Java? I don’t have access to change the existing mysql table not control over what data goes into it.

I did create a mysql view table and tried to apply CAST as DATETIME() however, I still encountered the same issue.

[Discourse post]

[marcosmarxm]

Comment made from Zendesk by Marcos Marx on 2022-07-28 at 07:38:

Issue seems to be related to Source MySQL: Connector fails if source contains "tricky" date values · Issue #3931 · airbytehq/airbyte · GitHub however I thought it’s fixed already.

[Discourse post]

[marcosmarxm]

Comment made from Zendesk by Marcos Marx on 2022-07-28 at 08:33:

applied zerodatetimebehavior=Converttonull still having the same issue
logs-99.txt (2.6 MB)

[Discourse post]

[marcosmarxm]

Comment made from Zendesk by Nataly Merezhuk on 2022-08-01 at 15:29:

From the latest logs it looks like the problem is now in DAY_OF_MONTH. I found another related issue that's been resolved:
#5070

I think the best course of action is to escalate this to GitHub, so I'lll do that next!

[marcosmarxm]

Comment made from Zendesk by Nataly Merezhuk on 2022-08-01 at 15:32:

Please feel free to add any details here:
#15161

[antonapua]

Identified issue where date value had 00 as day. wish this was ignored or converted to null.

[grishick]

@marcosmarxm this was likely fixed fairly recently. Can we ask the user to retry?

[marcosmarxm]

Comment made from Zendesk by Nataly Merezhuk on 2022-08-30 at 18:53:

Hello again, @antonapua. Could you retry - looks like the issue might have been fixed!

[akashkulk]

Closing this out for now, since no issues were reported by the user after the fix

[octav-s]

Still getting the issue in August 2023. I believe it is related to some dates being null. I've triead any and all parameters for the JDBC connector, it made no difference. I've checked the worker log and besides the DAY_OF_MONTH error, it is also showing another error, "c.n.s.DateTimeValidator(tryParse):82 - Invalid date-time: No zone offset information found". I've chacked all date and datetime fields, the values are either valid dates or NULL values.
worker_log.txt
f7199418_cea5_41e1_a66c_3e72df231d5f_job_8_attempt_3.txt
f7199418_cea5_41e1_a66c_3e72df231d5f_job_8_attempt_2.txt
f7199418_cea5_41e1_a66c_3e72df231d5f_job_8_attempt_1.txt

[octav-s]

PS: I've been getting the same error, DAY_OF_MONTH, in AWS Glue as well. I have not been able to figure out why it happens.