Can no longer upload spreadsheets after upgrading to 1.0.2

[Nolan Hicks]

So we've managed to run into a couple of major bugs when attempting to upload new spreadsheets to PANDA after upgrading to 1.0.2. 

When I attempted to upload an updated portion of the state's campaign finance database (about 3 million records ~ 600mb), the upload failed and a 502 -- Bad Gateway error appeared on the upload page. There was no additional information provided. 

When I attempted to a much smaller dataset (about 200k records and about 90mb), it appeared that the upload had worked, however I then got an email saying that it had failed. It provided the following traceback: 

  File "/usr/local/lib/python2.7/dist-packages/celery/execute/trace.py", line 181, in trace_task

    R = retval = fun(*args, **kwargs)

  File "/opt/panda/panda/tasks/import_csv.py", line 97, in run

    solr.add(settings.SOLR_DATA_CORE, add_buffer)

  File "/opt/panda/panda/solr.py", line 58, in add

    raise SolrError(response)

Guys have any ideas? 

[Christopher Groskopf]

Hey Nolan, it sounds like Solr didn't come back up after the upgrade. What's strange is that nothing in this upgrade affects Solr at all, and it's explicitly restarted after the changes are applied.

Can you try SSHing in and running:

$ service solr start

I'd also be really interested to see the contents of /var/log/panda-upgrade-1.0.2.log

Best,
Chris

> --
>  
>  

[Nolan Hicks]

Thanks for the help! I'll run home this afternoon and see if this fixes it. 

[Christopher Groskopf]

Oh quick follow-up, you will probably need to run that command with sudo:

sudo service solr start

Cheers,
Chris

--
 
 

[Nolan Hicks]

Sorry... this took me longer than I thought it would. I tried starting Solr and it said it was already running. So I tried restarting it and that didn't seem to work either. Importing a small dataset (about 20mb) resulted in the import fail error:

Problem accessing /solr/data/update. Reason:

    ERROR:unknown field 'data_upload_id'

The traceback was the same as before:

File "/usr/local/lib/python2.7/dist-packages/celery/execute/trace.py", line 181, in trace_task

R = retval = fun(*args, **kwargs)

File "/opt/panda/panda/tasks/import_csv.py", line 97, in run

solr.add(settings.SOLR_DATA_CORE, add_buffer)

File "/opt/panda/panda/solr.py", line 58, in add

raise SolrError(response)

Here's the log from the 1.0.2 update: 

Stopping nginx: nginx.

+ service uwsgi stop

uwsgi stop/waiting

+ service solr stop

solr stop/waiting

+ cd /opt/panda

+ git pull

From git://github.com/pandaproject/panda

 * [new branch]      1.0.2      -> origin/1.0.2

 * [new branch]      i18n       -> origin/i18n

   558b4a4..ba2cabe  master     -> origin/master

Already up-to-date.

+ git checkout 1.0.2

Switched to a new branch '1.0.2'

Branch 1.0.2 set up to track remote branch 1.0.2 from origin.

+ service solr start

solr start/running, process 22497

+ service uwsgi start

uwsgi start/running, process 22503

+ service nginx start

Starting nginx: nginx.

+ sudo service celeryd start

celeryd start/running, process 22521

+ echo 'PANDA upgrade complete.'

PANDA upgrade complete. 

[Christopher Groskopf]

Hey Nolan, this is really strange. The error your getting relates to a field missing from the solr configuration, but that field was added way back in 1.0. You had already upgraded to 1.0 and 1.0.1 before running this latest upgrade, right?

Can you send me the contents of:

/var/log/panda-upgrade-1.0.0.log

Best,

Chris

[Nolan Hicks]

Yeah. We've upgraded every step of the way. I'll post the log tonight.

[Nolan Hicks]

Well, this is bizarre. I don't have an upgrade log for when we moved to 1.0.0. 

Just 0.2.0 to 1.0.1 and 1.0.1 to 1.0.2. 

[Christopher Groskopf]

Okay, it looks like a migration got missed. We did release 1.0.0 and 1.0.1 in very quick succession. I checked the upgrade docs and they look right, so its more likely to have been miscommunication than an error in the process. Regardless, here's the situation:

* You've got all the latest code.

* You're missing some configuration for sure (thus the error).

* You also are likely missing the Ubuntu 12.04 upgrade and a some migrations. (You probably picked the latter up in a subsequent migration.)

The quick solution is to run the following commands to pick up the configuration I know you missed:

 Try running these commands with sudo:

service celeryd stop
service nginx stop
service uwsgi stop
service solr stop

cp setup_panda/data_schema.xml /opt/solr/panda/solr/pandadata/conf/schema.xml

cp setup_panda/english_names.txt /opt/solr/panda/solr/pandadata/conf/english_names.txt

cp setup_panda/datasets_schema.xml /opt/solr/panda/solr/pandadatasets/conf/schema.xml


sudo sed -i 's/client_max_body_size 1G/client_max_body_size 0/g' /etc/nginx/sites-available/panda


service solr start 

service uwsgi start

service nginx start

sudo service celeryd start


However, this will still leave you on a version of Ubuntu we don't really support. I doubt it will cause any issues, but if you want to get into parity with release you're going to need to run:

  sudo do-release-upgrade -d -f DistUpgradeViewNonInteractive

This may take a while.

Of course, we're in unknown territory, so no matter what I'd backup your PANDA first.

Best,

Chris

--
 
 

[Nolan Hicks]

First off, thanks for all of your help. I really appreciate it and it seems to have worked! I've been able to upload small and medium size datasets without a problem.

However, with large datasets, it still acts like they're uploading in their entirety, only to end in a 502 bad gateway error. And unfortunately, it's not providing a more detailed error message. 

[Christopher Groskopf]

Hey Nolan,

Sorry for the delay. This is pretty tough one to guess at. Off the top of my head: is your hard drive full? (http://demo.pandaproject.net/#dashboard -> Disk Usage)

The other likely culprit would be something to do with the celery task queue, but it would probably take some debugging to suss out exactly what. I'm certainly willing to take a look, but my time right now is pretty tight, so it would probably have to be over the weekend.

You asked off list about spinning up a new PANDA. I can say with a pretty high level of confidence this issue is particular to your instance, so if you choose to spin up a new one that should work fine, though the problem solver in me will be sad if the effective solution to a PANDA problem is "reboot".

C

--
 
 

[Nolan Hicks]

It says that there are 2.8 GB's still remaining, and while the files we're pushing are pretty large (600mb, 700mb respectively), that should still leave plenty of room. So I guess the issue is in the celery task queue. Let me know if there's anything I can do to assist. I appreciate all the help!

nolan 

[Nolan Hicks]

Updating on our timeout problem:

So, spinning up a new PANDA instance still didn't fix the problem. When I tried to upload the either of the two campaign finance spreadsheets (again, about 600/700mb respectively), it will upload the entire file and then generate a 504 Gateway Timeout and ask you to try again. So, apparently the problem isn't solved with a reboot. It's especially frustrating because we were hoping to be able to use PANDA to help mine through campaign finance reports as the Lege session goes on. 

To quote Liz Lemon: Blerg.

nolan 

[Christopher Groskopf]

Hey Nolan, sorry about the problems. I know it's frustrating. It sounds like the problem isn't related to your PANDA, but to the dataset. Is there a place where I can download that dataset to test locally?

Best,

Chris

--
You received this message because you are subscribed to the Google Groups "PANDA Project Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to panda-project-u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Nolan Hicks]

I'll put it Drive and send you a link off list. Here's the strange thing, I grabbed an earlier version of the campaign finance data set that we uploaded without a problem when we were on an earlier version of PANDA and that generated an error as well on 1.0.2. Not sure what to make of that. 

[Christopher Groskopf]

Nolan, you really do win all the marbles for the quality (that is, terror-inducing quality) of the bugs you report. After much hair-pulling I think I've got this sorted. The issue in fact has absolutely nothing to do with the specific file you're uploading. And I can't even begin to tell you why it ever would have worked. As best I can tell, here is what is happening:

* You upload the file.

* PANDA starts buffering that while the upload is in progress, then streaming it to a file on disk.

* Your client-side upload completes.

* Because the file is enormous and EC2 disks are notoriously slow, PANDA isn't done streaming it to disk.

* Because the client has finished uploading, nginx switches into "waiting for a reply" mode.

* PANDA keeps debuffering data to disk, but this takes a long time, because the file is huge.

* It takes PANDA longer to write the remaining chunks to disk than nginx is willing to wait for a reply.

* Nginx serves a "naked" 503, assuming the server backend is done.

* But really the server is still chugging along and eventually finishes and returns, unaware nginx is no longer listening.

* Thus there are no errors in the logs.

Theoretically I've got this fixed, but increasing the `uwsgi_send_timeout` and `uwsgi_read_timeout` configuration of nginx to stupidly high values. The downside of this is that if the backend were down for "legitimate" reasons, nginx might just let your browser spin for 30 minutes before deciding to tell you about it. Given that, I don't know if we'll want to roll this in the base release, but it certainly deserves to be documented.

I'm currently running a test with the file you sent me on a real PANDA on EC2 in order to verify this is fixed, but given that it takes an hour to run the test, I thought I'd just write this message while the explanation was fresh in my mind. The configuration you need to change is:

/etc/nginx/sites-available/panda

Underneath `client_max_body_size 0;` add the following two lines:

uwsgi_read_timeout 1800;

uwsgi_send_timeout 1800;

I'll let you know when my test completes and verifies this is the solution, but I feel confident it will be. Sorry it's taken so long to figure it out.

Best,

Chris

On Wed, Feb 6, 2013 at 4:38 PM, Nolan Hicks <ndha...@gmail.com> wrote:

I'll put it Drive and send you a link off list. Here's the strange thing, I grabbed an earlier version of the campaign finance data set that we uploaded without a problem when we were on an earlier version of PANDA and that generated an error as well on 1.0.2. Not sure what to make of that. 

[Christopher Groskopf]

Oh, I forgot to mention, after changing those configuration values you will need to run:

sudo service nginx restart

To bounce the web server.

Cheers,

Chris

[Christopher Groskopf]

Sigh, test failed. (In a new way--progress!) Will continue to debug.

C

[wm higgins]

nolan, chris:

i ran into this problem loading a 528mb voter registration csv -- about 2.6 million rows,

the upload was taking about 5 minutes, so i tried bumping the timeout configs:

    uwsgi_read_timeout 600;

    uwsgi_send_timeout 600;

    uwsgi_connect_timeout 75;

(connect_timeout can't be set to anything greater than 75 -- http://wiki.nginx.org/HttpUwsgiModule)

before config changes, the error was 504 Gateway Time-out

after config fiddling, error was 502 Bad Gateway

in each case, the sequence was:

upload proceeds and returns 100% complete after 4 minutes

spinner continues for another minute, then the 502 error returns

no data set is created and the uwsgi process stays on 67% mem usage until i restart uwsgi.

i split the file in half and got a successful upload.

so failure point for me is somewhere between 264mb and 528mb 

 

To unsubscribe from this group and stop receiving emails from it, send an email to panda-project-users+unsub...@googlegroups.com.

[Nolan Hicks]

Good to know. I think we were having trouble uploading files in the ~250mb range (but I'll double check that when I get home).  

[Adam Playford]

Sorry for reviving an old thread, but wanted to ask: Did anyone ever find a solid solution to this issue? I'm getting the same thing when I try to upload a 280MB file.

Thanks!

Adam

[Daniel Pascucci]

Did anyone find a solution to this yet? I'm having this issue with a 120MB dataset with only 580,000 rows. Is there currently no way to upload a dataset of this size to PANDA?

[Adam Playford]

We don't have a fix-fix but we've been able to work around it. Below is an e-mail my colleague, Matt Clark, posted on NICAR-L when a similar question popped up there.

Adam

Newsday

---------- Forwarded message ----------
From: Matt Clark <mr.matth...@gmail.com>
Date: Tue, Oct 22, 2013 at 6:37 PM
Subject: Re: [NICAR-L] Searchable database inquiry (PANDA, etc)
To: NIC...@po.missouri.edu

The manual_upload script, which is in the repo here:

https://github.com/pandaproject/panda/blob/master/scripts/manual_upload.py

has been helpful.

The manual upload lets you circumvent having to upload through the web, via HTTP, which is definitely nice.

But, depending on the specs of your server and the size of your datasets, you will still have to break up large datasets into chunks of files, and I don't believe this works with the manual_upload script (at least after the first chunk is uploaded). I don't know the specific reason for needing to split them into chunks, but the indexing that occurs after upload appears to use a lot of RAM. If you upload too large a file the import will crash. I currently don't upload anything larger than 600 MB on our EC2 M1-SMALL. I split larger datasets into smaller files using SQL's LIMIT, OFFSET and ORDER BY.

So far, I have not had to use the manual upload script for datasets that I had to split into multiple chunks, because I just started using the script. But for one 600 MB file it is definitely more convenient. I get to upload all the data files I plan on importing via SFTP, which is faster and more reliable than HTTP. I run the script for the first file and walk away. After it finishes (PANDA sends me an email), I come back and run it for the next dataset I'm importing, and walk away, etc.

Hope that helps.

Cheers,

Matt

On Tue, Nov 12, 2013 at 6:18 PM, Daniel Pascucci <danpa...@gmail.com> wrote:

I'm now having this same issue with a 120MB file of only 580,000 rows. Did anyone find any solution for this? We really can't use this software if it can't handle a file of this size.

--
You received this message because you are subscribed to a topic in the Google Groups "PANDA Project Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/panda-project-users/3BxwlZGDb1g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to panda-project-u...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--

Adam Playford
adam.p...@gmail.com

[Christopher Groskopf]

Hi Daniel,

For lack of development time to implement a more perfect solution,
we're currently recommending using this manual upload script for files
that are too large for the upload UI:

https://github.com/pandaproject/panda/blob/master/scripts/manual_upload.py

Instructions are contained therein. We certainly want to fix this in a
better way, but it's a very complicated refactoring that needs to
happen and until I have a large chunk of time to work on it this is
currently the best solution.

Hope that helps,
Chris

> --
> You received this message because you are subscribed to the Google Groups
> "PANDA Project Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an