https://linen.dev logo
Join Slack
Powered by
```i am trying to run tap-mysql to pipelinewise-ta...
# troubleshooting
m
Copy code
i am trying to run tap-mysql to pipelinewise-target-s3-scv, log based replication, locally on my mac using docker, and it get the following error:  Extraction failed (1): AttributeError: 'NoneType' object has no attribute 'settimeout'.  I tried adding in SQL Session variables to my meltano.yml, but that did not resolve the error.  Below is a copy of the tap-mysql config.  Any ideas on how to resolve this?  Thanks!


- name: tap-mysql
    variant: transferwise
    pip_url: pipelinewise-tap-mysql
    config:
      host: hostname
      user: hostuser
      export_batch_rows: 100000
      session_sqls:
      - SET @@session.wait_timeout=28800
      - SET @@session.net_read_timeout=3600
      - SET @@session.innodb_lock_wait_timeout=3600
      - SET @@session.time_zone='+0:00'
    select:
    - table name.*
    metadata:
      '*':
        replication-method: LOG_BASED
d
Sounds like you found https://github.com/transferwise/pipelinewise/issues/503, but adding those 
session_sqls
isn't resolving the issue yet. Do you get the error pretty much immediately, or only after some number of rows have been synced?
I think we should add a Troubleshooting section to https://hub.meltano.com/extractors/mysql for this specific error because you're not the first to run into it. Similar to the section we already have on https://hub.meltano.com/loaders/postgres#troubleshooting
Let me know if that solution with https://meltano.com/docs/settings.html#elt-buffer-size works. If so, we can document it
m
thanks, i didn't have the max exec time and net write timeout configured, so i will try that and let you know
just setting the max exec time and net write resolve the issue, i did not need to set the elt buffer size. thanks!
d
@mike_planting Great! Would you mind filing an issue in https://gitlab.com/meltano/hub/-/issues/ to add this to the docs for tap-mysql? šŸ™‚
m
Sure, so i am not sure which one of the sql sessions config fixed the issues, but should i just request that all seesion_sqls be configured?
d
Yeah if that's what the team behind the tap recommended in https://github.com/transferwise/pipelinewise/issues/503, that makes sense to me
m
i am actually running into the same issues, setting the sql session configs and the buffer size did not improve things
d
@mike_planting How big is the table you're pulling from? Could you try continuing to increase the buffer size?
m
265m
any recommendation on the buffer size
d
@mike_planting Hmm, I was hoping we'd be able to make the buffer size large enough to be able to hold all records in memory at once, so that the tap can load all rows in one go and doesn't need to resume once the target is done processing the rows. Since it's that resuming that's causing the issue: the connection with the database will have expired by the time the buffer is processed and the tap gets a chance to continue to pull new records
m
i have export batch row size set to 10000, shouldn't that throttle the process
d
Ah, good point, that could help
But if the buffer fills up halfway through the batch of 10000, the query tap-mysql was running would still be "paused" and resumed once all rows are processed by the target, at which point it may have expired
In the thread at https://meltano.slack.com/archives/CFG3C3C66/p1610729229014200?thread_ts=1610560265.206500&cid=CFG3C3C66 we also ended up tweaking the target buffer/batch size. Does the target you're using have that setting?
Note that all of these will be workarounds; the real fix will be for tap-mysql to recover from the expired cursor/connection
m
s3 is my target, and it looks like it is running into the timeout issue on the move to s3
Copy code
tap-mysql | File "/project/.meltano/extractors/tap-mysql/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_bytes
tap-mysql | AttributeError: 'NoneType' object has no attribute 'settimeout'
pipelinewise-target-s3-csv | time=2021-06-10 20:27:40 name=target_s3_csv level=INFO message=Uploading /tmp/clickfunnels-action_funnel_steps-20210610T202338.csv to bucket cf-bi-dashboard at di-meltano/clickfunnels-action_funnel_steps/di-meltano-clickfunnels-action_funnel_steps-20210610T202338.csv
meltano | Extraction failed (1): AttributeError: 'NoneType' object has no attribute 'settimeout'
meltano | ELT could not be completed: Extractor failed
meltano | /usr/local/lib/python3.6/site-packages/meltano/core/job/finder.py:65: SAWarning: TypeDecorator IntFlag() will not produce a cache key because the ``cache_ok`` flag is not set to True. Set this flag to True if this type object's state is safe to use in a cache key, or False to disable this warning.
meltano | .order_by(Job.ended_at.desc())
meltano | /usr/local/lib/python3.6/site-packages/meltano/core/plugin/singer/tap.py:198: SAWarning: TypeDecorator IntFlag() will not produce a cache key because the ``cache_ok`` flag is not set to True. Set this flag to True if this type object's state is safe to use in a cache key, or False to disable this warning.
a couple issues here... it seems like a historical pull should be chuncked out to multiple files, and that is not happening, the second issues is that there is not timeout setting that i can see on the pipelinewise-target-s3-csv target
d
Yeah it looks like the buffer fills up, the tap gets suspended while the target processes the buffer, the tap gets unsuspended, and the query/cursor has expired. Are you confident enough with Python to see if you can make the tap handle connection expiration better?
m
no i am not that skilled with python
d
@mike_planting OK. Unfortunately that's the only option I see right now. This is ultimately a bug in tap-mysql, and it looks like the workarounds that Meltano and some targets allow are not an option here
c
Hey folks, reviving this thread because it looks a lot like the error I'm getting. I have tap-mssql and target-snowflake and my two issues (that I think are related) are: 1. metadata has been set for my table (schema-table) to be replicated with FULL_TABLE and yet a 
meltano run
says that the table (table) declares no replication type. Wondering if the 'schema-' not showing is why this happens? 2. similarly, trying to archive created S3 files produces the error 
AttributeError: 'NoneType' object has no attribute 'encode'
Here is my meltano.yml:
Copy code
version: 1
default_environment: dev
project_id: #
plugins:
  extractors:
  - name: tap-mssql
    variant: wintersrd
    pip_url: tap-mssql
  loaders:
  - name: target-snowflake
    variant: transferwise
    pip_url: pipelinewise-target-snowflake
environments:
- name: dev
  config:
    plugins:
      extractors:
      - name: tap-mssql
        config:
          host: ###
          database: ###
          user: ###
          port: ###
          default_replication_method: FULL_TABLE
        select:
        - schema-table.*
        metadata:
          schema-table:
            replication-method: FULL_TABLE
      loaders:
      - name: target-snowflake
        config:
          account: ###
          dbname: ###
          user: ###
          add_metadata_columns: true
          warehouse: ###
          file_format: PARQUET
          default_target_schema: ###
          role: ###
          stage: ###
          s3_bucket: ###
          s3_key_prefix: ###
          s3_region_name: ###
          archive_load_files: true
          archive_load_files_s3_prefix: ###
          archive_load_files_s3_bucket: ###
- name: staging
- name: prod
d
@connor_lough Before trying anything else, can you move the 
metadata
and 
select
just below 
pip_url: tap-mssql
? Part of this may be explained by https://github.com/meltano/meltano/issues/3322, which was fixed in https://github.com/meltano/meltano/pull/6376 but not released yet
c
Ah, thank you @douwe_maan, this did fix the first issue I mentioned above. But not the second issue of AttributeError: NoneType; schema still isn't being read properly despite the <schema>-<table> existing 
/None/DBSyncHistory/meltano_fin_data_martpipelinewise_dbo-DBSyncHistory_20220714-152201-043485_batch_1qin3sdl.parquet.gz
d
@connor_lough Can you share the full stacktrace on that error message? Itā€™ll be useful to see exactly where it comes from
c
I don't know what a stack trace is. Do you mean my logs?
d
Yeah, the logs preceding the 
AttributeError: 'NoneType' object has no attribute 'encode'
error if there are any. The stack trace is the list of all the nested methods calls that led to that error, so that helps us find where it went wrong in the code. If you donā€™t see those, run again with 
metlano --log-level=debug ...
and they should show up
c
log.txt.cpp
log.txt.cpp
d
Can you please share the values of these settings?
Copy code
archive_load_files_s3_prefix: ###
          archive_load_files_s3_bucket: ###
Ah I found a matching issue on the target-snowflake repo: https://github.com/transferwise/pipelinewise-target-snowflake/issues/235
I want to be able to archive data into s3 when data is comming from taps that only send <<table_name>> on stream list instead of <<schema_name>>_<<table_name>>
So this may be an incompatibility with the tap youā€™re using
Hmm, but the tap youā€™re using was made for pipelinewise as well so it should be fine: https://github.com/wintersrd/pipelinewise-tap-mssql
And the stream name seems to be 
dbo-DBSyncHistory
, so it should already be fine
Are you comfortable opening up one of these python files and adding some more debug statements? Iā€™d like to see what 
s3_archive_metadata
contains just ahead of this line: https://github.com/transferwise/pipelinewise-target-snowflake/blob/master/target_snowflake/db_sync.py#L428
c
In about 30 min I'll be free from meetings and will add some lines
Maybe a silly question, but if I add some logger lines to the meltano_project/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/db_sync.py file... shouldn't it show up in my elt.log file?
d
@connor_lough Yeah that should work. Are you using Docker and mounting your project directory into it? And not accidentally overriding the modified plugin files with ā€œmeltano installā€?
c
I am using Docker, but I'm not currently using the meltano base image. I'm wondering if using python:3.8-slim is ruining this
d
@connor_lough Should be fine, as long as you're mounting your own project directory with the modifications into it. If you introduce an obvious bug like a syntax error, do you see the command fail, or does it keep running? Because that would imply it's not actually using your modified file
c
Okay it is using my edits, and logger registers now
Looks to be the 's3_archive_key'
Copy code
Showing variable: s3_archive_key as None/DBSyncHistory/meltano_fin_data_martpipelinewise_dbo-DBSyncHistory_20220714-190359-644623_batch_9v5trzgm.parquet.gz
going to keep chasing it upstream...
d
None of the values in that metadata dict are just ā€œNoneā€?
c
Copy code
Showing variable: s3_archive_metadata as {'tap': None, 'schema': 'dbo', 'table': 'DBSyncHistory', 'archived-by': 'pipelinewise_target_snowflake'}
d
Ah, interesting. I'm not sure where that property is supposed to come from but judging by that issue that may be the problem
c
For now, I can hardcode the value of 'tap' and it works šŸ˜„ I would love to find out where the s3_archive_metadata variable gets created another time. I'll keep searching.
Thank you so much for the assistance, @douwe_maan!
d
c
Hey Douwe, I was chasing this issue down (I have short term solution) and noticed that the debugging docs say that running meltano --log-level=debug elt will point me to the generated config.json file. I found the line that links to the location, but the file does not exist. Have you heard of this happening?
d
@connor_lough The config file is automatically cleaned up when the run completes, so the better way to get the contents of the file is with 
meltano invoke --dump=config tap-foo
4 Views
Open in Slack
PreviousNext
Powered by