CSV export results in empty file with no data

[noisydeadlines]

Describe the bug When using the CSV data export the resulting file is empty

To Reproduce Steps to reproduce the behavior:

1. Go to 'Settings'
2. Click on 'CSV Export'
3. Click on 'Download the File'
4. Open the downloaded file

Expected behavior CSV with data from the books

Screenshots

Instance BookWyrm Social

---

Desktop (please complete the following information): - OS: Windows 10 - Browser: Edge - Version 103.0.1264.37 (Official build) (64-bit)

[mouse-reeve]

I suspect this is happening because of a timeout -- it was reported earlier and self resolved without me doing anything.

[noisydeadlines]

Hi! I tried exporting it again twice, I still get the same empty file every time.

[Strubbl]

I can confirm this issue was happening with v0.4.3 and is still not working with v0.4.4. I was trying to export on the BookWyrm Social instance.

[cubicgarden]

I can confirm I'm getting the exact same thing too. Tired to export twice and spoke with @mouse-reeve who pointed to this issue

Desktop

• OS: Ubuntu 22.04.1
• Browser: Firefox 106.0.2 (64-bit)

[r3k2]

same here, is annoying only reason I wanted this is to migrate to another server, the one I am at is not been taken care off is more time down than up for many people, https://bookwyrm.social/ I keep getting issues for more than 9 months, I cant edit a group list some of us made, works when not logged in, but as soon I log in it breaks, because a nginx bad gateway that usually happens when the service is unresponsive or the container is low on memory unresponsive etc, so got tired of waiting for the admin to reply to us, I even offer to let me ssh so I can fix it, (systems engineer for 24 years) and to try to troubleshoot this even do I sent 2-3 times screenshots, I waited 9 months because I know this is not a job and voluntier but now and here comes to the point, I try to get the CSV to migrate to another instance and is empty :( I am on Arch GNU/Linux I tried with qutebrowser, firefox and chromium.

[mouse-reeve]

@r3k2 -- I just wanted to acknowledge how frustrated and unheard it sounds like you're feeling with this. It sounds like you've been impacted by bugs that interfere with basic usability, and you aren't getting a response that makes you feel like the problems you're encountering are being acknowledged and taken seriously. I imagine it's just as frustrating for everyone who's been reporting and +1'ing these as well!

When I get bug reports that I have trouble replicating and am not sure how to fix, I often find it overwhelming and I'm not always sure how to get outside help in fixing them. As a result, there are some bugs, like the ones you've encountered, that I've been aware of but pretty stuck about how to address, and as a result they languish in the issue tracker. The scale and demands of the project have gone way up and my capacity to fix bugs and bring in support from others hasn't kept pace. I find that really daunting and a little scary. So given that, it's super generous of you to have offered your expertise to help fix it, and I apologize for missing that you made that offer; although I try my best, I still fail to notice or forget things.

I believe that the source of this specific bug is that the server is overloaded and the query is timing out and returning an empty csv. I have been focusing (with some greatly appreciated help) for the last couple weeks to try to improve the performance situation overall, which is related but doesn't directly address this specific thing not working. I'd be really open to suggestions on how a data export can be compiled in a way that is robust to server load and timeouts.

It will also need some expanding to be an effective migration utility, but that's very doable.

[eldang]

I'm also frustrated by this bug, and I'm wondering if the solution is to make the export asynchronous. I have two examples in mind which might be useful as patterns:

LibraryThing: when I exported from there to import to Bookwyrm, clicking the "Export all books" button didn't immediately give me a link, instead it showed some kind of "in progress" feedback. Trying it now, I got a blue bar that says "nnn books processed", updating every 100 books. It took a few minutes to do my whole library, and then was replaced with a link in the format https://www.librarything.com/download_export_file.php?uniqueId=HexadecimalIDHere . Presumably that hex ID is a lookup in a table that connects to an actual file; clicking the link gets me a file with my username in the file name.

Allen Coral Atlas: I actually maintain the downloads system for this one. Downloads can take a couple of hours to assemble in the worst case, so we pre-package common requests and then have a fully asynchronous system for everything else. It goes like this:

1. User requests download, which starts a background job with a database row to track its progress internally.
2. User immediately gets an acknowledgement email so they know their request is in the works.
3. A whole set of tasks get kicked off behind the scenes, some in parallel, some polling for the output of others.
4. When the last one is done, the file is uploaded to a server, the database row gets the URL added to it, and the user gets an email with the link.

The ACA version is probably overkill here! But something like what LibraryThing does, perhaps also generating a notification that contains the link, seems like it could work.

[nein09]

Hi, I'm @eldang 's spouse and I told him that I would take a crack at fixing this bug as a Christmas gift. Luckily, I do a lot of work on Django sites, so it's like... actually realistic of me to offer this.

I haven't done a lot of digging here yet, so I might be way off-base, but something like https://pypi.org/project/django-import-export-celery/ might go a long way toward helping make this happen.

I think what I'm likely to do is start with something tagged as "good first bug" to get my feet wet in this repo, too.

So... hi! Hope I can help!

[mouse-reeve]

Thank you! Help on this ticket would be super appreciated, and I agree that using celery seems like the right approach. I'd be happy to work with you and provide whatever help/explanations of weird codebase things I can do

[todrobbins]

@nein09, any luck with django-import-export-celery? I'd love to learn about how you're approaching this challenge.

[nein09]

@todrobbins I decided to start with what looks like a smaller piece of work first (https://github.com/bookwyrm-social/bookwyrm/issues/1678) (and life has been getting in the way of even that), so I'm not quite here yet. So sadly, there isn't much to report yet though. But I haven't forgotten about it.

[phildini]

Hi! As a note on possible causes here, I was able to get an export from my own bookwyrm instance just fine, but the export I got from bookwyrm.social was blank

[WesleyAC]

I made some interesting progress on this today. I've been playing with our gunicorn setup (since I'm pretty sure it's responsible for many of the loading delays on bookwyrm.social recently), and I bumped the timeout up to 600 seconds to test this (also increasing the timeout on the nginx side).

This prevents the timeout, but there's still a problem — psycopg2 reports that the disk is full:

Long traceback

2023-03-07T21:12:33.957615255Z [2023-03-07 21:12:33 +0000] [34] [ERROR] Error handling request
2023-03-07T21:12:33.957816312Z Traceback (most recent call last):
2023-03-07T21:12:33.957824404Z   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
2023-03-07T21:12:33.957830447Z     return self.cursor.execute(sql, params)
2023-03-07T21:12:33.957838102Z psycopg2.errors.DiskFull: could not write to file "base/pgsql_tmp/pgsql_tmp258365.0.sharedfileset/o7of16.p2.0": No space left on device
2023-03-07T21:12:33.957866374Z CONTEXT:  parallel worker
2023-03-07T21:12:33.957871138Z 
2023-03-07T21:12:33.957875345Z 
2023-03-07T21:12:33.957879675Z The above exception was the direct cause of the following exception:
2023-03-07T21:12:33.957890816Z 
2023-03-07T21:12:33.957895746Z Traceback (most recent call last):
2023-03-07T21:12:33.957900217Z   File "/usr/local/lib/python3.9/site-packages/gunicorn/workers/base_async.py", line 113, in handle_request
2023-03-07T21:12:33.957905097Z     for item in respiter:
2023-03-07T21:12:33.957909616Z   File "/usr/local/lib/python3.9/site-packages/sentry_sdk/integrations/wsgi.py", line 269, in __iter__
2023-03-07T21:12:33.957914304Z     reraise(*_capture_exception(self._hub))
2023-03-07T21:12:33.957919300Z   File "/usr/local/lib/python3.9/site-packages/sentry_sdk/_compat.py", line 56, in reraise
2023-03-07T21:12:33.957924185Z     raise value
2023-03-07T21:12:33.957929101Z   File "/usr/local/lib/python3.9/site-packages/sentry_sdk/integrations/wsgi.py", line 265, in __iter__
2023-03-07T21:12:33.957933608Z     chunk = next(iterator)
2023-03-07T21:12:33.957938084Z   File "/app/bookwyrm/views/preferences/export.py", line 44, in <genexpr>
2023-03-07T21:12:33.974117970Z     (writer.writerow(row) for row in generator),
2023-03-07T21:12:33.974127943Z   File "/app/bookwyrm/views/preferences/export.py", line 65, in csv_row_generator
2023-03-07T21:12:33.974134306Z     for book in books:
2023-03-07T21:12:33.974139525Z   File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 280, in __iter__
2023-03-07T21:12:33.974144935Z     self._fetch_all()
2023-03-07T21:12:33.974149851Z   File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 1324, in _fetch_all
2023-03-07T21:12:33.974154775Z     self._result_cache = list(self._iterable_class(self))
2023-03-07T21:12:33.974159318Z   File "/usr/local/lib/python3.9/site-packages/model_utils/managers.py", line 38, in __iter__
2023-03-07T21:12:33.974163645Z     yield from iter
2023-03-07T21:12:33.974168584Z   File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 51, in __iter__
2023-03-07T21:12:33.974173790Z     results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
2023-03-07T21:12:33.974178793Z   File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
2023-03-07T21:12:33.974183543Z     cursor.execute(sql, params)
2023-03-07T21:12:33.974198530Z   File "/usr/local/lib/python3.9/site-packages/sentry_sdk/integrations/django/__init__.py", line 562, in execute
2023-03-07T21:12:33.974203863Z     return real_execute(self, sql, params)
2023-03-07T21:12:33.974208496Z   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
2023-03-07T21:12:33.974233721Z     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
2023-03-07T21:12:33.974239021Z   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
2023-03-07T21:12:33.974244133Z     return executor(sql, params, many, context)
2023-03-07T21:12:33.974249552Z   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
2023-03-07T21:12:33.974254546Z     return self.cursor.execute(sql, params)
2023-03-07T21:12:33.974259352Z   File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
2023-03-07T21:12:33.974264361Z     raise dj_exc_value.with_traceback(traceback) from exc_value
2023-03-07T21:12:33.974269235Z   File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
2023-03-07T21:12:33.974273780Z     return self.cursor.execute(sql, params)
2023-03-07T21:12:33.974278058Z django.db.utils.OperationalError: could not write to file "base/pgsql_tmp/pgsql_tmp258365.0.sharedfileset/o7of16.p2.0": No space left on device
2023-03-07T21:12:33.974282548Z CONTEXT:  parallel worker

And indeed, watch -n1 df -h while a CSV export is running shows that it quickly eats up all the remaining space on the server (which is promptly returned when the query fails).

Using the log_temp_files postgres setting, it seems that this is from creating many temporary files of around 200MB each, rather than a single enormous temporary file.

It looks to me like what's happening is that one of the queries in the for book in books: loop in the csv_row_generator is making a 200MB temp file, and the results of the query are being kept around until the generator is fully read (maybe? I don't have a strong picture of how generators work, nor the Django ORM).

While building this export in Celery is clearly the best long-term solution, I do think that there's potential to rewrite the export code to fix this in the short term. I'm not sure why a generator was initially used (probably it saves on memory by avoiding storing the entire CSV in memory?), but unrolling it into a loop that generates the CSV in-memory, rather than keeping the Query objects around and converting them to CSV one line at a time would probably get exports working on bookwyrm.social for the moment.

[WesleyAC]

Looks like #2713 didn't have exactly the effect I was hoping for — somehow the same disk space problem is triggered. Not sure if it's that I don't understand when Django QuerySets are cleaned up, when Postgres temp files are cleaned up, or something else. Will continue investigating.

[mouse-reeve]

The fact that we're seeing a 500 error rather than an empty file is an improvement, I think!

[WesleyAC]

Yeah, that's true :)

My guess is that what's happening is the books query is trying to materialize tables that are larger than work_mem (there is a Materialize in the EXPLAIN output), and those thus get written to disk. Looking at the EXPLAIN output for the books query:

        books = (
            models.Edition.viewer_aware_objects(request.user)
            .filter(
                Q(shelves__user=request.user)
                | Q(readthrough__user=request.user)
                | Q(review__user=request.user)
                | Q(comment__user=request.user)
                | Q(quotation__user=request.user)
            )
            .distinct()
        )

It seems like the culprit and solution will likely be similar to #2725 / #2726, although presumably more complicated since we'll need to figure out how to handle duplicates. If we were writing raw SQL it would be pretty easy to use CTEs to select all five of those as individual queries then DISTINCT them at the end, but it looks like Django can't generate CTEs without using libraries like django-cte, which I'm not totally sure is worth the effort, versus making five queries then sticking them in a set or something.

[WesleyAC]

Just FYI, #2741 hasn't been deployed to bookwyrm.social yet, so CSV exports will still fail there until that's deployed — I'll comment in this issue when it is, so that anyone following this will get a notification.

[WesleyAC]

This should now be fixed on bookwyrm.social! I was able to export my own CSV history, albeit a small one. Please open a new issue if you have any problems with the CSV export.

@Strubbl @todrobbins FYI

[todrobbins]

Thank you for your work on this, @WesleyAC! I'll test a bookwyrm.social export right now.

[eldang]

@WesleyAC Hooray! Thank you for fixing this. Export from bookwyrm.social just worked smoothly (and fairly quickly) for me, and it looks like importing that to books.theunseen.city (which is on v0.6.0) is also working.

[todrobbins]

Export worked perfectly and pretty quick (~3s/43.1 KB/363 rows):

[cubicgarden]

I can confirm it worked for me too, thank you so much!

On 04/04/2023 18:56, Tod Robbins wrote:

Export worked perfectly and pretty quick (~3s/43KB/363 rows):

image https://user-images.githubusercontent.com/158590/229877801-491d0175-0c53-44fe-85db-28aa2c96fa0d.png

— Reply to this email directly, view it on GitHub https://github.com/bookwyrm-social/bookwyrm/issues/2157#issuecomment-1496375505, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAMS2ASPCIXET6M3IXBZ2BTW7ROFZANCNFSM52DOWXMQ. You are receiving this because you commented.Message ID: @.***>