https://linen.dev logo
Join Slack
Powered by
Hey Guys `source: mysql 5.7` `target: big-query` `...
# troubleshooting
f
Hey Guys 
source: mysql 5.7
target: big-query
Meltano: V2.7.1
OS: ubuntu 20.04
Python: Python 3.8.10
Do you have any idea, how can I fix this error, because in all of tables I have this error and also I can’t update source database?
Copy code
CRITICAL 400 reason: invalid, errors: Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 4; errors: 1. Please look into the errors[] collection for more details.;Error while reading data, error message: JSON processing encountered too many errors, giving up. Rows: 4; errors: 1; max bad: 0; error percent: 0;Error while reading data, error message: JSON parsing error in row starting at position 83138: Couldn't convert value to timestamp: Day 0-0-0 does not exist for timestamp: 0000-00-00 00:00:00 Field: updated_at; Value: 0000-00-00 00:00:00
c
I ran into the same problem with mysql. Apparently timestamp `NULL`s in MySQL show up as 
0000-00-00 00:00:00
which causes some problems somewhere in Meltano (not sure where). What I did is, I just dropped any fields where this timestamp showed up in the MySQL stream like this:
Copy code
mappers:
    - name: transform-field
      variant: transferwise
      mappings:
        - name: transform-field-set-null
          config:
            transformations:
              - tap_stream_name: yourmysqlstreamname
                field_id: TheFieldName
                type: SET-NULL
                when:
                  - column: TheFieldName
                    equals: "0000-00-00 00:00:00"
Apparently this can be considered "broken" in the source database (at least when using a supported non-EOL version of MySQL) ... https://stackoverflow.com/a/41858394/6056177
f
Thank you, is there any documentation about available options?
and also can I keep tables name, because automatically Meltano add name of database to the tables name, I used the table name before
t
Older versions of MySQL actually stored that zero-date value in datetime columns if the column was declared NOT NULL 🤯
So it may not be a "nulls look like 0000-00-00" problem, the value in the field may actually be 0000-00-00. In our case we addressed that by fixing the data rather than mapping it. 🤷🏻
f
Hey Thomas, Thanks for you description, IDK why, but, yes, this true,
Could you explain a little bit more about your solution, if you can?
and also do you know, how can I keep the name of tables same as mysql, without database name?
t
In some cases the zero dates occurred in rows we decided were garbage and we just deleted them; in others we set them to some valid value based on other information from the record.
I think maybe you can use a mapper (e.g. meltano-map-transformer) to change the stream name from "schema-table" to just "table". I'm not sure though. We're using target-postgres which automatically ignores the "schema-" portion of the name so I've never actually had to deal with that problem.
f
Thank you, I’ll try this one, and then I’ll share my result
This works:
Copy code
- name: meltano-map-transformer
    variant: meltano
    pip_url: git+<https://github.com/MeltanoLabs/meltano-map-transform.git>
    mappings:
    - name: alias-tables
      config:
        stream_maps:
          schema-table:
            __alias__: table
3 Views
Open in Slack
PreviousNext
Powered by