pgloader hangs/idles after create index on huge db load from mysql

[Necklaces]

To cut a long story short, I've got a mysql database that I need to load into postgres.
Built from git, around 0600 UTC 10.06.2016.

./pgloader -V
pgloader version "3.3.fa9f437"
compiled with SBCL 1.3.1.debian

ran
./pgloader --verbose script.lisp
script.lisp:

LOAD DATABASE
FROM mysql://usr:pass@localhost/database
INTO postgres://usr:pass@localhost/database;

last output I got was around 0815 UTC;
NOTICE CREATE UNIQUE INDEX idx_17180_primary ON sometable

After that there has not been anything happening on the server, htop shows nothing happening, iotop says 0/0. To me it seems like it finished indexing and is idling, however, after doing a

SELECT schemaname,relname,n_live_tup 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

I can report that only the first table in the postgres database has something in it, the others are empty (and according to mysql they are supposed to have some rows in them),
"sometable" is also the first table, both in mysql and in postgres. Confirming that it's not supposed to be finished yet. It is worth noting that "sometable" is around 220 million rows. Or 40GB if you will...
pgloader has created the tables though.

I haven't stopped pgloader yet, and if I have to I guess I could rewrite script.lisp to exclude that huge one. Think it'll start again by itself after some time?

Any ideas? ;w;

[dimitri]

Is it possible for you to share the database with me, e.g. making it available on an RDS instance or on some EC2 I could connect to, so that I can reproduce and fix the bug(s) here?

[Necklaces]

Really sorry; no can do. Mostly because it's company data, but also because I don't really have an instance, also; 40 gigabytes (took nearly a day to scp from one company server to another).

You might be able to reproduce with randomly generated data though, I could possibly generate it but I'd still have to share it. If you have an instance yourself I could set up reverse ssh port forwarding on one of your ports to one of my home computers.

Anyway, I got the database in by stopping pgloader, dropping the first large table in mysql and starting pgloader again - the first table is still there in postgresql. pgloader stopped another time on a table that was 10 million rows, and seemed to be doing something, but after two more hours I decided it really wasn't and quit it, dropped that table in mysql as well, and restarted -- that time it finally finished. Now the whole database is migrated.

My specific issue is fixed but I still think this is a flaw/bug. Seems like it has something to do with large, >10 million row tables in databases.

[dimitri]

I added the labels to the issue, one of them being “Sponsor needed”. I would like to have sponsoring to setup a large enough test setup and reproduce the bug, and I could also setup a cloud instance where you give you ssh access for the forwarding from your side. See http://pgloader.io/pgloader-moral-license.html for details about how to become a sponsor. Thanks!

[edjackson-wf]

I am experiencing a similar problem (and also, sorry to say, with private data). The last output line indicates the creation of an index. However, if I turn on debug output, it actually seems to be stuck on data transfer:

2016-07-25T18:03:29.870000Z DEBUG format-data-to-copy[3] 12626 row in 4.939000s
2016-07-25T18:03:33.571000Z DEBUG format-data-to-copy[3] 9260 row in 3.614000s
2016-07-25T18:03:46.435000Z DEBUG copy-batch[2] sometable 12626 rows in 16.433000s
2016-07-25T18:03:59.056000Z DEBUG copy-batch[2] sometable 9260 rows in 12.689000s

The table in question contains about 2.6M rows, and includes (in the MySQL) a LONGTEXT field. (The other two fields are INT, one as a primary key field). These two batches appear to be the first processed for this table.

All threads except one are sleeping, and that one is stuck at 100% CPU. I'm running with batch rows = 10000 and batch concurrency = 1, although I've experimented with a bunch of other batch settings with similar results, always on the same table.

pgloader version "3.3.7daee94"
compiled with SBCL 1.3.7

[dimitri]

Did you compile your version from sources? can you check about the build/quicklisp/local-projects/qmynd git version and freshness? your report looks like a bug I fixed in the MySQL driver already...

[edjackson-wf]

Looks like qmynd is on current master (cd6179c).

The problem tables in my case turned out to be very old data that was no longer needed, so we just dropped them.

[roadrunner2]

We've run into this several times too, and the underlying state/behaviour is consistent every times this happens: lsof shows the mysql socket is in CLOSE_WAIT (i.e. the server has closed the connection) and strace shows pgloader is looping on a read() call on that socket and getting back a return value of 0 (i.e. EOF). So the bug is in quite probably in qmynd not handling EOF properly at some place. There may be higher level bugs here too, but this one needs fixing in any case and that should help pinpoint any higher level bug.

[dimitri]

Thanks @roadrunner2 for useful hints! Is there a way to set a test case that I could then play with at home and fix?

[dimitri]

I am now playing with a test case where I can reproduce the bug and I see (Got an error writing communication packets) in the MySQL logs. Do some of you guys see that too?

[dimitri]

I can't reproduce the bug anymore with those patches in, please build from fresh sources and retry.

[ipanousis]

I've found the hanging to also happen when all threads have encountered some errors, and this is not immediately obvious.

[dimitri]

Any way to reproduce? which version are you using?

[ipanousis]

I'm using 3.3.2 installed via Debian repos. As for an example of how to reproduce, I am seeing this behavior when all threads have reported this issue https://github.com/dimitri/pgloader/issues/686

[dimitri]

Please try with a local build from a freshly updated source repository...

[mgttlinger]

Experiencing the same halting on/after index issue on a sqlite database of about 5GiB size
(using pgloader version "3.3.2"
compiled with SBCL 1.3.14.debian)

[dimitri]

@mgttlinger please consider sharing the SQLite source database with me, either by email or such, or by making it available on a cloud box somewhere where I can play with it to fix the bugs, then we can terminate the instance.

[mgttlinger]

@dimitri I will first need to discuss the sensitivity of the data with my colleagues or look into my possibilities for scrambling/anonymizing the data.

[mgttlinger]

@dimitri I'm sorry I can't give out the database

[dimitri]

Consider upgrading pgloader or compiling from sources, and then see if you can create a SQLite database that shows the same behaviour but contains none of your data (self-contained test case).