hey All im trying to load mysql 5 1 table to postgres but go Meltano #troubleshooting

hey All, im trying to load mysql(5.1) table to pos...

farid

02/07/2023, 9:01 AM

hey All, im trying to load mysql(5.1) table to postgres but got below error

Copy code

psycopg2.errors.DatetimeFieldOverflow: date/time field value out of range: "0000-00-00 00:00:00"

I tried to infer schema like but did not work

Copy code

meltano invoke tap-mysql --properties target-tables-config.json  | meltano invoke target-postgres

Copy code

cat target-tables-config.json                                                                    
{
    "streams": [
      {
        "tap_stream_id": "harri3_beta-job_setting",
        "stream": "users",
        "schema": {
          "type": ["null", "object"],
          "additionalProperties": false,

    "properties": {
        "publish_date": { "type": [ "null", "string"]},
      ...
        "public_on": { "type": [ "null", "string"]},
...
        "updated": { "type": [ "null", "string"]}

}
}
      }
    ]
}

I tried to follow this but no luck yet https://github.com/singer-io/getting-started/blob/ca5c56f16e67e0c2da49e9bb94d6f52fb19c9de6/docs/DISCOVERY_MODE.md#schemas https://docs.meltano.com/concepts/plugins#schema-extra

anthony_g

02/07/2023, 1:02 PM

Hello, I'm having the same issue, so far, like you i haven't found a workaround

thomas_briggs

02/07/2023, 1:41 PM

@farid @anthony_g I think this thread has a solution (and an explanation...)

farid

02/07/2023, 2:02 PM

thanks @thomas_briggs will check it out

farid

02/07/2023, 2:49 PM

I follow the thread above but same issue

Copy code

plugins:
  extractors:
  - name: tap-mysql
    variant: transferwise
    pip_url: pipelinewise-tap-mysql
    config:
      ...
    metadata:
      '*':
        replication-method: INCREMENTAL
        replication-key: updated
    select:
    - '!!(h_beta-job_setting.*).*'
    - h_beta-job_setting.*   
  loaders:
  - name: target-postgres
    variant: transferwise
    pip_url: pipelinewise-target-postgres
    config:
      ...

  mappers:
  - name: transform-field
    variant: transferwise
    pip_url: pipelinewise-transform-field
    mappings:
    - name: transform-field-set-null
      config:
        transformations:
        - tap_stream_name: h_beta-job_setting
          field_id: public_on
          type: SET-NULL
          when:
          - column: public_on
            equals: "0000-00-00 00:00:00"

anthony_g

02/07/2023, 3:10 PM

Thanks @thomas_briggs for the thread, it's solved the issue on my side

anthony_g

02/07/2023, 3:11 PM

@farid Did you add the "transform-field-set-null" in the run command ?

meltano run tap-mysql transform-field-set-null target-postgres

farid

02/07/2023, 3:18 PM

thanks @anthony_g it worked I forget to add it in the run

farid

02/07/2023, 3:30 PM

but how can we add multiple columns not just in the same transformation

farid

02/07/2023, 3:37 PM

this works for multiple columns

Copy code

mappings:
        - name: transform-field-set-null
          config:
            transformations:
              - tap_stream_name: h_beta-job_setting
                field_id: public_on
                type: SET-NULL
                when:
                  - column: public_on
                    equals: '0000-00-00 00:00:00'
              - tap_stream_name: h_beta-job_setting
                field_id: switch_date
                type: SET-NULL
                when:
                  - column: switch_date
                    equals: '0000-00-00 00:00:00'

aaronsteers

02/07/2023, 6:21 PM

Nice. 👍 Is it worth opening an issue on the tap to request timestamps of

'0000-00-00 00:00:00'

to be exported as NULL?

farid

02/07/2023, 8:23 PM

for mysql timestamp type also this pattern occurs ‘0000-00-00 000000.000000’

christoph

02/07/2023, 8:55 PM

Is it worth opening an issue on the tap to request timestamps of
'0000-00-00 00:00:00'
to be exported as NULL?

That sounds like the correct thing to do. There also is this entry 😁 https://github.com/MeltanoLabs/Singer-Most-Wanted/issues/73

bubba

02/11/2023, 11:26 PM

Hey all, I ran into this issue as well and created Github issue here.

2 Views

Open in Slack

Previous Next