Just tried running a the first job for about 5m rows from My

Question

Just tried running a the first job for about 5m rows from MySQL to Postgres It was humming along and then I got this error ```pipe closed by peer or os write pipe data raised exception ``` Not sure what it means

ripe-musician-59933 · Answer

< wide salesclerk 68871> What Meltano version are you on

wide-salesclerk-68871 · Answer

1 63 0

ripe-musician-59933 · Answer

< wide salesclerk 68871> That explains it then this issue was fixed in v1 64 0 slightly smiling face

wide-salesclerk-68871 · Answer

ahhh cool

wide-salesclerk-68871 · Answer

I m guessing there isn t any reason not to just bump up to 1 65 0

ripe-musician-59933 · Answer

Definitely don t skip 1 64 1 <https meltano slack com archives CP8K1MXAN p1610154236066900 thread ts=1610064894 064300 amp cid=CP8K1MXAN>

ripe-musician-59933 · Answer

So yeah I suggest going straight to the latest

wide-salesclerk-68871 · Answer

Thanks

wide-salesclerk-68871 · Answer

I m using `LOG BASED` replication It was replicating one of 5 tables when it failed After upgrading I reran it and it skipped the table it was replicating when I it failed ```tap mysql | INFO LOG BASED stream fake schema table name requires full historical sync``` is the only mention of it in the run Not sure if this is expected or not I m planning on trying a ` full refresh` but the other tables worked great so I d like to avoid it if possible

wide-salesclerk-68871 · Answer

I m guessing this is an example where `plugin inheritance` can be pretty helpful I could seperate the large tables into their own plugins allowing me to full refresh individually if needed

wide-salesclerk-68871 · Answer

Actually now when I try running it without ` full refresh` again it s giving me this error ```tap mysql | CRITICAL NoneType object has no attribute settimeout ``` and ```tap mysql | pymysql err OperationalError 2013 Lost connection to MySQL server during query ```

ripe-musician-59933 · Answer

You can use ` full refresh` in combination with ` select= lt stream gt ` to only fully refresh a specific stream

wide-salesclerk-68871 · Answer

ohhhhh

wide-salesclerk-68871 · Answer

cool

wide-salesclerk-68871 · Answer

I ll play with that then Thanks

ripe-musician-59933 · Answer

` NoneType object has no attribute settimeout ` looks like a legitimate bug in tap mysql it would help to see the rest of the stack trace if there is any

wide-salesclerk-68871 · Answer

```tap mysql | CRITICAL NoneType object has no attribute settimeout tap mysql | Traceback most recent call last tap mysql | File opt meltano meltano extractors tap mysql venv lib python3 7 site packages tap mysql sync strategies full table py line 266 in sync table tap mysql | params tap mysql | File opt meltano meltano extractors tap mysql venv lib python3 7 site packages tap mysql sync strategies common py line 255 in sync query tap mysql | row = cursor fetchone tap mysql | File opt meltano meltano extractors tap mysql venv lib python3 7 site packages pymysql cursors py line 469 in fetchone tap mysql | row = self read next tap mysql | File opt meltano meltano extractors tap mysql venv lib python3 7 site packages pymysql cursors py line 464 in read next tap mysql | return self conv row self result read rowdata packet unbuffered tap mysql | File opt meltano rails meltano extractors tap mysql venv lib python3 7 site packages pymysql connections py line 1160 in read rowdata packet unbuffered tap mysql | packet = self connection read packet tap mysql | File opt meltano meltano extractors tap mysql venv lib python3 7 site packages pymysql connections py line 674 in read packet tap mysql | recv data = self read bytes bytes to read tap mysql | File opt meltano meltano extractors tap mysql venv lib python3 7 site packages pymysql connections py line 707 in read bytes tap mysql | CR CR SERVER LOST Lost connection to MySQL server during query tap mysql | pymysql err OperationalError 2013 Lost connection to MySQL server during query ```

ripe-musician-59933 · Answer

That could be caused by the extractor being blocked for a while while the loader is working through the buffered messages with the connection having expired by the time the extractor is unblocked Increasing the buffer size may help <https meltano com docs settings html elt buffer size>

ripe-musician-59933 · Answer

Ideally the tap would handle the connection failure better by attempting to reconnect but that d need to be contributed to the tap not much Meltano can do to help

wide-salesclerk-68871 · Answer

ok cool Thanks It s interesting because I didn t have this issue on the initial run Only the subsequent one

ripe-musician-59933 · Answer

The exact timing of the extractor and loader won t be the same each time of course so it s possible that in second run the delay just happened to be slightly over the mysql timeout while it was slightly under before

wide-salesclerk-68871 · Answer

ahhhh that makes sense I ll try adjusting the buffer

wide-salesclerk-68871 · Answer

I tried updating the buffer to `52428800` and got the same error I was planning on going a bit higher but it seemed like x5 would be enough

ripe-musician-59933 · Answer

< wide salesclerk 68871> How many records are you syncing in this run You ll want to make the buffer large enough to fit the entire batch of extracted records so that the tap can finish in one go without being blocked when the buffer is full since it s that block that creates the delay that leads to the connection expiring

ripe-musician-59933 · Answer

A more durable fix would be to have the tap attempt to retry the connection but that would need to be done in a contribution to <https github com transferwise pipelinewise tap mysql>

wide-salesclerk-68871 · Answer

gotcha I m actually not using the piplinewise tap I m using the singer version

ripe-musician-59933 · Answer

Ah all right Same deal though slightly smiling face

wide-salesclerk-68871 · Answer

but that fits with what I was thinking I think The table is ~5 million rows It failed after about 1 1 million had synced So it would need to be able to hold the 1 1 million rows Right Also this is because it failed partway through the run right I didn t have this issue on the initial run of another table that was a similar size

ripe-musician-59933 · Answer

if it failed after about 1 1 million rows that suggests that 1 1 million rows fit into the buffer and then when the buffer was mostly depleted the tap was given back control to start querying for new rows again at which point it found that the connection had expired So to prevent the tap from ever getting back control after a delay the buffer would need to be big enough to fit all 5 million rows so that the tap can run and run and run until it s done

ripe-musician-59933 · Answer

I don t think it d be related to being partway through the run 2 runs don t typically impact each other in that way although it s possible that the tap uses different mechanism for the initial full table load and subsequent incremental loads which could change the picture

ripe-musician-59933 · Answer

Are the tables of similar size of similar width column count + per row data size as well

ripe-musician-59933 · Answer

A same sized buffer can contain many more narrow records than wide ones of course so it s possible that 5M of one table fit while only 1M of another do

ripe-musician-59933 · Answer

So with the narrow table tap mysql could get all of its work done in one run while with the wider table it would need to continue after the buffer was depleted hitting the connection timeout

ripe-musician-59933 · Answer

Increasing the buffer size in Meltano is a workaround to prevent that timeout but the real fix would be better connection handling in the tap as mentioned slightly smiling face

wide-salesclerk-68871 · Answer

Totally I m sorry I should be clear I m pretty sure it failed due to the bug in `1 63 0` the first time the beginning of this thread I do think it s a pretty wide table though so that might be it It looked like the tap was chunking rows about 100 000 at a time I would assume that would be help control the size of the buffer That s why I thought it weird that it was able to handle 1 1m the first time 11 chunks but won t even run a single chunk the second time But maybe I m missing something basic

ripe-musician-59933 · Answer

When you say the first time was that on 1 63 0

wide-salesclerk-68871 · Answer

yes

ripe-musician-59933 · Answer

That explains it then pre 1 64 0 the buffer size limit wasn t enforced at all so the tap could keep going and the buffer would keep growing until the target would die usually for OOM reasons and Meltano would fail with the `pipe closed by peer or os write pipe data raised exception ` error you saw So I wouldn t read too much into the 1 1M on the first run 100k on subsequent runs thing

ripe-musician-59933 · Answer

100K rows appears to be what currently fits into your configured buffer size

wide-salesclerk-68871 · Answer

Cool So for now raising the buffer size is the way to go Makes sense Makes me a bit nervous for the initial run on some even larger tables we have though

wide-salesclerk-68871 · Answer

I guess I can just keep making the buffer bigger and bigger for now

ripe-musician-59933 · Answer

Yeah or rip off the bandaid and contribute reconnection logic wink

wide-salesclerk-68871 · Answer

Made a note to take a look I m happy to try but it might take me a minute to figure out where to start If you have any ideas of where to start searching feel free to point me in the right direction slightly smiling face

wide-salesclerk-68871 · Answer

I want to make sure I understand the problem correctly The `tap` writes the data to a buffer The `target` reads from the buffer too slowly The `tap s` connection times out while it waits for the `target` to finish

ripe-musician-59933 · Answer

The stacktrace in points at `cursor fetchone ` at as the call that triggers the `pymysql err OperationalError` so that s where you d likely want to add a `try except` block to handle the error by attempting to reconnect That `sync query` method is called here wrapped by contextmanagers that create the connection and cursor so you d want to have `sync query` communicate to its parent method that it needs to run the whole thing again starting past the row it did manage to get to successfully Fortunately the replication key value of that row would already have been stored in state so just letting the logic get back to and try the whole thing again may be enough for it to pick up where it left off That means that you may want to rescue from the `OperationError` with the correct disconnect error ID around instead of inside `sync query` ensure that `iterate limit` is not set to `False` and let the loop continue as normal

ripe-musician-59933 · Answer

`sync query` is also called from where you d want to do something similar handle the error and make it go back to `with connect with backoff` and makes sure you get into the `perform resumable sync` branch with the correct pk clause matching how far the sync got the last time

ripe-musician-59933 · Answer

gt I want to make sure I understand the problem correctly gt The `tap` writes the data to a buffer gt The `target` reads from the buffer too slowly The buffer gets filled up completely The tap is blocked on its `write` call until half the buffer is depleted gt The `tap s` connection times out while it waits for the `target` to finish More accurately while it waits for target to work through half the buffer and for Meltano to unblock the tap s `write` call

ripe-musician-59933 · Answer

As long as it takes the target to work through half the buffer is as long as the tap will be blocked on the write call

ripe-musician-59933 · Answer

As for contributing better disconnection handling to tap mysql I suggest contributing to pipelinewise tap mysql instead of the singer io variant since it s much more actively maintained

wide-salesclerk-68871 · Answer

Fair The problem is that we can t use that tap until it respects column selection properly the other thread we ve been pinging back and forth in

ripe-musician-59933 · Answer

Right Fortunately pipelinewise tap mysql is based on singer io tap mysql so your disconnection fix in the latter may be easily portable to the former

wide-salesclerk-68871 · Answer

rad Thanks again for all your help

ripe-musician-59933 · Answer

My pleasure

wide-salesclerk-68871 · Answer

Hi again Got this working but then it blew up again I think I just need to up the `BUFFER` again but I wanted to get set up locally to test where I should set it I got set up and got a different error For whatever reason when I run this on my local docker images `meltano` doesn t seem to be respecting the `BUFFER` The RAM usage just keeps going up until the containers crash and then it fails ``` File usr local lib python3 7 asyncio streams py line 202 in drain helper raise ConnectionResetError Connection lost ``` I can see this happening if I watch the `docker stats` Anyway I realize this is likely an issue with my local setup but if you have any other ideas please let me know Thanks

ripe-musician-59933 · Answer

< wide salesclerk 68871> Hmm we saw that error in 1 64 0 and I thought I fixed the root cause in 1 64 1 I assume you re still on the latest version Have you pulled the latest `meltano meltano latest python3 7` Docker image as well

wide-salesclerk-68871 · Answer

I m on `1 65 0` We re running our own docker image mono repo using the `apache airflow 1 10 11 python3 7` image For what s it s work the `job` table in the metadata db is showing a failed run

wide-salesclerk-68871 · Answer

Also I don t think this was happening on Production which seems weird

ripe-musician-59933 · Answer

< wide salesclerk 68871> Wanna jump on a quick Zoom call so I can see this for myself and help debug

wide-salesclerk-68871 · Answer

sure

ripe-musician-59933 · Answer

ripe-musician-59933 · Answer

ripe-musician-59933 · Answer

The issue turned out to be that `target postgres` s `bath size rows` setting <https meltano com plugins loaders postgres transferwise html batch size rows> was set to the default value of 100 000 meaning that it would keep that many rows in memory before flushing to Postgres Because the rows were relatively wide that ended up quickly exceeding the 2GB memory limit on the Docker container resulting in the process being killed and `meltano elt` dying along with it Dropping `batch size rows` down to 1000 resulted in maximum target memory usage of about 500MB and the issue disappeared

wide-salesclerk-68871 · Answer

Hi I m back wink No more ram issues but the `batch size rows` setting is causing another problem It s not syncing the last batch of rows that is less than 10k For example `batch size rows = 10000` source table row count = 10 500 After elt the target table will have 10 000 rows I m guessing this is an issue with the pipelinewise target

ripe-musician-59933 · Answer

< wide salesclerk 68871> Hmm there s logic to flush the last batch here

wide-salesclerk-68871 · Answer

Interesting I ll do some digging but it s pretty consistent In fact if the table is smaller than the batch size nothing gets synced

ripe-musician-59933 · Answer

Well that s not right I wonder if Meltano is not letting the target finish once the tap is finished for some reason

ripe-musician-59933 · Answer

Wanna jump on another call

wide-salesclerk-68871 · Answer

Can t right now Will also need to set up some dummy data if we re going to do a call again per my security team grimacing

wide-salesclerk-68871 · Answer

Dummy schema more accurately

wide-salesclerk-68871 · Answer

the data was dummy data

ripe-musician-59933 · Answer

Hehe all right

ripe-musician-59933 · Answer

Can you run `meltano elt` again with the `PYTHONASYNCIODEBUG=1` env var I d like to see what it prints just before the pipeline completes when presumably the tap finishes first and then some time later the target It may tell us whether the target is getting killed early for some reason

wide-salesclerk-68871 · Answer

thumbsup

ripe-musician-59933 · Answer

If the tap finishes before the target Meltano will flush all remaining buffered tap output close the target s stdin wait for the target to finish generating output and then wait for its process to finish When the target s stdin is closed it will know it s not going to receive any more input and break out of this loop once it s worked through its own input buffer That shouldn t cause the target to immediately quit though since it should fall through to that logic I just linked to

ripe-musician-59933 · Answer

I m not sure what would cause the target to not make it to that point at all

wide-salesclerk-68871 · Answer

Nevermind I got it

wide-salesclerk-68871 · Answer

It flushes all of the tables at the end of the run

wide-salesclerk-68871 · Answer

it looks good now

ripe-musician-59933 · Answer

Ah all right I m glad to hear that Was the thing that tripped you up the fact that it would already move to a different table before finishing up the previous one s

ripe-musician-59933 · Answer

You may want to enable the `flush all streams` setting so that it flushes all streams when one batch is full <https meltano com plugins loaders postgres transferwise html flush all streams>

ripe-musician-59933 · Answer

I didn t realize it keeps a batch per stream

ripe-musician-59933 · Answer

Right now memory usage could theoretically build up to ` lt number of streams gt lt batch size rows 1 gt `

wide-salesclerk-68871 · Answer

gt Ah all right I m glad to hear that Was the thing that tripped you up the fact that it would already move to a different table before finishing up the previous one s Yep Taking a look at the `flush all streams` setting Thanks

ripe-musician-59933 · Answer

I ve created a new issue for the `ConnectionResetError` you saw <https gitlab com meltano meltano issues 2526>