search

ODM2 / WOFpy

A server-side implementation of CUAHSI's Water One Flow service stack in Python.
http://odm2.github.io/WOFpy/
9 stars 9 forks source link

Adapting ODM2 timeseries DAO to Luquillo CZO ODM2 PostgreSQL DB #73

Closed emiliom closed 7 years ago

emiliom commented 7 years ago

From @miguelcleon:

using my postgresql LCZO database. I see now what your talking about with Sites, Emilio. I added in a Sites record related to a Stream gage sampling feature and a URL like the below pulled back the expected site information. http://127.0.0.1:8080/odm2timeseries/rest/1_1/GetSiteInfo??site=odm2timeseries:Rio%20Icacos%20Trib-IO

The below did not work and yielded the attached ‘WofPyGetValuesError.xml’ file. Maybe there are some other database records that are expected but which I haven’t populated. http://127.0.0.1:8080/odm2timeseries/rest/1_1/GetValuesForASite?site=odm2timeseries:Rio%20Icacos%20Trib-IO&startDate=2016-10-01T00:00:00&endDate=2016-10-03T02:30:00

(Regarding use of Site SamplingFeatures vs other SamplingFeature types)

Yes, I’m using Weather station, Observation well, and Stream gage sampling features although I do have at least one sampling feature defined as a site with a number of time series associated with it (http://odm2admin.cuahsi.org/LCZO/LCZO/graphfa/dataset=5/resultidu=16695/startdate=2014-11-14/enddate=2016-02-24/ ).

Is there any example of any setup DAOs?

examples/flask/odm2/timeseries, developed using the sample LBR ODM2 timeseries MySQL DB, is effectively the example DAO setup. For the LCZO (and other ODM2 DB's with timeseries result types) our approach will be to adapt and/or generalize that DAO, once we understand what's different.

cc'ing @lsetiawan so he's pinged on this isue

emiliom commented 7 years ago

@miguelcleon, I'm pasting here this other comment of yours, for future reference:

Also of note in the link above the graph view is filtered by dataset, this is a published dataset. Perhaps the ODM2 controlled Vocabulary for Dataset Type (http://vocabulary.odm2.org/datasettype/ ) should have published versions of specimenTimeSeries, singleTimeSeries, other, multiVariableSpecimentMeasurements and multiTimeSeeries? If that sounds like a good idea I can submit requests into the controlled vocab system.

But I'm not going to comment on it for now. I think we still have a ways to go in setting you up with WOFpy before we can focus on this topic per se. If Jeff comments on your question on the email thread, great. But my focus will be on getting WOFpy to work with your timeseries data, one incremental step at a time.

miguelcleon commented 7 years ago

@emiliom Thanks that sounds good. I could post something about the errors I received but It's probably worth while to poke a little deeper.

emiliom commented 7 years ago

Yes, let's also wait a bit until you have the sample MySQL LBR fully up and running, to compare against.

emiliom commented 7 years ago

@miguelcleon, I don't see any reference to a GROUP BY problem on this issue. Can you post the problem and errors here, or if it's from a different type of problem, go ahead and submit a new issue. Thanks.

emiliom commented 7 years ago

@miguelcleon, this is a heads-up that @lsetiawan and I should be able to start working with you on setting up WOFpy with your LCZ DB soon, hopefully towards the end of this week.

FYI, we plan to issue a new release by tomorrow, including conda and pypi packages.

miguelcleon commented 7 years ago

sounds great. Thanks @emiliom and @lsetiawan :smiley:

miguelcleon commented 7 years ago

Hi @emiliom and @lsetiawan, I went ahead and installed the latest release of WOFpy and it seems to be working! This is running currently on a local development machine, I'd like to get this setup on a development webserver, serving this over the web and then do some validation. Looks like I need to change one or more files here https://github.com/ODM2/WOFpy/tree/master/wof/examples/production_configs ? image

image

emiliom commented 7 years ago

That's great news, @miguelcleon! Don is working with a different project/PI today (as all Thursdays). Hopefully he'll be able to help you on Friday, but his availability will be very tight; then he's off on vacation next week.

I believe you do have to move around and edit some of the production_configs files (which can be deployed/copied via wofpy_config.py), but if it's not clear on the documentation, I'm afraid I can't help you myself.

miguelcleon commented 7 years ago

Ok, I'm also away at a workshop most of next week, back for a few days then I'm on vacation June 28th to July 9th. I'll probably play around with it a little, otherwise tomorrow works.

miguelcleon commented 7 years ago

Ok, I gave the documentation, instructions a try. I didn't follow them exactly because I'm trying to run it through apache2 instead nginx. I looked up instructions for running both and it seemed pretty complex. Really it seems like things should work but I'm getting an error that seems to be related to the conda environment.

ValueError: failed to parse CPython sys.version: '2.7.12 (default, Nov 19 2016, 06:48:10)'

Here is a stackoverflow post that makes me think it has something to do with the conda environment. https://stackoverflow.com/questions/34145861/valueerror-failed-to-parse-cpython-sys-version-after-using-conda-command

I'm using a new conda environment installed from https://anaconda.org/odm2/wofpy

Full Trace back: 

[Thu Jun 15 17:54:00.664620 2017] [core:notice] [pid 31241:tid 140079072356224] AH00094: Command line: '/usr/sbin/apache2'
[Thu Jun 15 17:54:12.235326 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283] mod_wsgi (pid=31252): Target WSGI script '/var/www/wofpy/wsgi.py' cannot be loaded as Python module.
[Thu Jun 15 17:54:12.235428 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283] mod_wsgi (pid=31252): Exception occurred processing WSGI script '/var/www/wofpy/wsgi.py'.
[Thu Jun 15 17:54:12.235472 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283] Traceback (most recent call last):
[Thu Jun 15 17:54:12.235514 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/var/www/wofpy/wsgi.py", line 4, in <module>
[Thu Jun 15 17:54:12.235573 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     from runserver import app as application
[Thu Jun 15 17:54:12.235586 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/var/www/wofpy/runserver.py", line 13, in <module>
[Thu Jun 15 17:54:12.235617 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     import wof
[Thu Jun 15 17:54:12.235681 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/wof/__init__.py", line 4, in <module>
[Thu Jun 15 17:54:12.235721 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     from wof.core import _SERVICE_PARAMS, _TEMPLATES, site_map
[Thu Jun 15 17:54:12.236109 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/wof/core.py", line 18, in <module>
[Thu Jun 15 17:54:12.236303 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     from spyne.application import Application
[Thu Jun 15 17:54:12.236319 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/spyne/__init__.py", line 40, in <module>
[Thu Jun 15 17:54:12.236361 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     from spyne.decorator import rpc
[Thu Jun 15 17:54:12.236372 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/spyne/decorator.py", line 43, in <module>
[Thu Jun 15 17:54:12.236455 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     from spyne.model import ModelBase, ComplexModel
[Thu Jun 15 17:54:12.236466 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/spyne/model/__init__.py", line 31, in <module>
[Thu Jun 15 17:54:12.236496 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     from spyne.model.primitive import *
[Thu Jun 15 17:54:12.236504 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/spyne/model/primitive/__init__.py", line 60, in <module>
[Thu Jun 15 17:54:12.236551 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     from spyne.model.primitive.number import Decimal
[Thu Jun 15 17:54:12.236582 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/spyne/model/primitive/number.py", line 148, in <module>
[Thu Jun 15 17:54:12.236661 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     class Double(Decimal):
[Thu Jun 15 17:54:12.236671 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages/spyne/model/primitive/number.py", line 163, in Double
[Thu Jun 15 17:54:12.236685 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     if platform.python_version_tuple()[:2] == ('2','6'):
[Thu Jun 15 17:54:12.236691 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/platform.py", line 1481, in python_version_tuple
[Thu Jun 15 17:54:12.237062 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     return tuple(string.split(_sys_version()[1], '.'))
[Thu Jun 15 17:54:12.237077 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]   File "/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/platform.py", line 1422, in _sys_version
[Thu Jun 15 17:54:12.237092 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283]     repr(sys_version))
[Thu Jun 15 17:54:12.237159 2017] [wsgi:error] [pid 31252:tid 140078976792320] [remote 40.85.180.138:15283] ValueError: failed to parse CPython sys.version: '2.7.12 (default, Nov 19 2016, 06:48:10)'
emiliom commented 7 years ago

@lsetiawan probably won't be able to respond until tomorrow.

But given that you suspect a conda problem, I'm pinging @ocefpaf (Filipe) in case he recognizes the errors.

BTW, all our wofpy testing has been done on nginx. Just yesterday we were talking about Apache. Don thinks wofpy should work there, but he hasn't looked into it.

ocefpaf commented 7 years ago

I'm pinging @ocefpaf (Filipe) in case he recognizes the errors.

I don't think this is conda related. My guess it is a Python "mix-up."

I would check what is in /var/www/wofpy/wsgi.py to see if that is calling another Python that is not from /home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7.

It that wsgi.py is the oringinal one from @lsetiawan's PR config files we need to be carful with the hardcoded shebang: #! /home/ubuntu/miniconda/envs/wofpy/bin/python. See https://github.com/ODM2/WOFpy/blob/master/wof/examples/production_configs/wsgi.py#L1.

We need to change that to call the first Python in the path instead.

emiliom commented 7 years ago

Hmm, shouldn't we wait for @miguelcleon to report back, before closing this issue?? Besides, this issue is broader than just the bug fixed by that PR

ocefpaf commented 7 years ago

Thanks to GitHub the "closing" is automatic. We need to manually re-open it. But I see you already did.

miguelcleon commented 7 years ago

Yeah I had changed that line in the wsgi.py file. Something is still pointing to the wrong python environment though. I check conda list and it has python version 2.7.13 for the wofpy environment, but the error is indicating 2.7.12.

ocefpaf commented 7 years ago

Yeah I had changed that line in the wsgi.py file.

To help us debug this can you inform us what ou changed that line to?

miguelcleon commented 7 years ago

well I changed it again to:

!/usr/bin/env python

before I had:

! /home/azureadmin/miniconda2/envs/wofpy4/bin/python

ocefpaf commented 7 years ago

What python do you get with $(which python) --version 2.7.12 or 2.7.13?

miguelcleon commented 7 years ago

from where should I do that in wsgi.py? from the command line with wofpy conda env active I get /home/azureadmin/miniconda2/envs/wofpy4/bin/python

miguelcleon commented 7 years ago

without the conda env active I get /home/azureadmin/miniconda2/bin/python: Python 2.7.12 :: Continuum Analytics, Inc.

ocefpaf commented 7 years ago

from where should I do that in wsgi.py?

Makes sense. Your root env python and your wofpy4 env Python. As long as you have the env where you installed wofpy activated #!/usr/bin/env python should find the right python. If that is not the case than I am lost.

miguelcleon commented 7 years ago

I think it maybe my apache settings are getting confused. I had wofpy listed under the same virtualhost as some ODM2 Admin instances so I think maybe it is trying to use the conda environment from those. So I'm trying to setup a different virtual host. thinking maybe that would help but I don't think I'm doing it right, can't seem to connect to the wofpy instance.

miguelcleon commented 7 years ago

ok so for whatever reason my wofpy installation is not using the correct conda environment when running through apache. I'm not sure why but I'm giving up for today.

miguelcleon commented 7 years ago

Ok, I got it to work! http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest_1_1/

There were confusing instructions in the django documentation about how to point to your venv in apache. For some reason the Django docs say to use python-home variable to point to your venv when in fact you should use python-path, at least for my particular setup. Frustrating, but it's working now :smile:

what I ended up using was: WSGIDaemonProcess wofpy python-path=/var/www/wofpy:/home/azureadmin/miniconda2/envs/wofpy4/lib/python2.7/site-packages:/home/azureadmin/miniconda2/envs/wofpy4/bin

miguelcleon commented 7 years ago

@lsetiawan

so it is timing out on getsites 


<ns0:Fault xmlns:ns0="http://schemas.xmlsoap.org/soap/envelope/">
<faultcode>soap11env:Server</faultcode>
<faultstring>
(psycopg2.DatabaseError) SSL SYSCALL error: Connection timed out [SQL: 'SELECT DISTINCT odm2.sites.samplingfeatureid AS odm2_sites_samplingfeatureid, odm2.samplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, odm2.sites.spatialreferenceid AS odm2_sites_spatialreferenceid, odm2.sites.sitetypecv AS odm2_sites_sitetypecv, odm2.sites.latitude AS odm2_sites_latitude, odm2.sites.longitude AS odm2_sites_longitude, odm2.samplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, odm2.samplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, odm2.samplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, odm2.samplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, odm2.samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, odm2.samplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, odm2.samplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, odm2.samplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, odm2.samplingfeatures.featuregeometrywkt AS odm2_samplingfeatures_featuregeometrywkt, CASE WHEN (odm2.samplingfeatures.samplingfeaturetypecv = %(samplingfeaturetypecv_1)s) THEN %(param_1)s WHEN (odm2.samplingfeatures.samplingfeaturetypecv = %(samplingfeaturetypecv_2)s) THEN %(param_2)s ELSE %(param_3)s END AS _sa_polymorphic_on \nFROM odm2.samplingfeatures JOIN odm2.sites ON odm2.samplingfeatures.samplingfeatureid = odm2.sites.samplingfeatureid JOIN odm2.featureactions ON odm2.samplingfeatures.samplingfeatureid = odm2.featureactions.samplingfeatureid JOIN (odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid) ON odm2.featureactions.featureactionid = odm2.results.featureactionid \nWHERE odm2.featureactions.samplingfeatureid = odm2.sites.samplingfeatureid AND odm2.results.featureactionid = odm2.featureactions.featureactionid'] [parameters: {'param_1': 'Specimen', 'param_2': 'Site', 'samplingfeaturetypecv_2': 'Site', 'param_3': 'samplingfeatures', 'samplingfeaturetypecv_1': 'Specimen'}]
</faultstring>
<faultactor/>
</ns0:Fault>
miguelcleon commented 7 years ago

and now I'm getting an assertion that there should be a database rollback, when there shouldn't be anything to rollback:

calling http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetVariableInfo?variable=odm2timeseries:DO%20Concentration 

<ns0:Fault xmlns:ns0="http://schemas.xmlsoap.org/soap/envelope/">
<faultcode>soap11env:Server</faultcode>
<faultstring>
(sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT DISTINCT ON (odm2.variables.variableid) odm2.timeseriesresultvalues.valueid AS odm2_timeseriesresultvalues_valueid, odm2.timeseriesresultvalues.resultid AS odm2_timeseriesresultvalues_resultid, odm2.timeseriesresultvalues.datavalue AS odm2_timeseriesresultvalues_datavalue, odm2.timeseriesresultvalues.valuedatetime AS odm2_timeseriesresultvalues_valuedatetime, odm2.timeseriesresultvalues.valuedatetimeutcoffset AS odm2_timeseriesresultvalues_valuedatetimeutcoffset, odm2.timeseriesresultvalues.censorcodecv AS odm2_timeseriesresultvalues_censorcodecv, odm2.timeseriesresultvalues.qualitycodecv AS odm2_timeseriesresultvalues_qualitycodecv, odm2.timeseriesresultvalues.timeaggregationinterval AS odm2_timeseriesresultvalues_timeaggregationinterval, odm2.timeseriesresultvalues.timeaggregationintervalunitsid AS odm2_timeseriesresultvalues_timeaggregationintervalunitsi_1 \nFROM odm2.timeseriesresultvalues JOIN (odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid) ON odm2.timeseriesresults.resultid = odm2.timeseriesresultvalues.resultid JOIN odm2.variables ON odm2.variables.variableid = odm2.results.variableid \nWHERE odm2.variables.variableid = odm2.results.variableid AND odm2.variables.variablecode = %(variablecode_1)s'] [parameters: [{}]]
</faultstring>
<faultactor/>
</ns0:Fault>
miguelcleon commented 7 years ago

more then 15 minutes later I'm still getting this rollback message.

I'm reloading apache.

lsetiawan commented 7 years ago

so it is timing out on getsites

Did you set lazy-apps to true in wsgi.ini? I encountered that same error before and fixed it that way.

miguelcleon commented 7 years ago

Yes, lazy-apps is set to true

lsetiawan commented 7 years ago

Maybe it's your DB configuration? https://github.com/psycopg/psycopg2/issues/356, Based on that link, you are running into TCP timeouts?

@miguelcleon The rollback, you just have to restart WOFpy. It happens everytime after you encounter an error in WOFpy.

miguelcleon commented 7 years ago

yes, I'll look at increasing the timeout.

miguelcleon commented 7 years ago

ok I changed the timeout but now I'm getting a different error for getsites 

<ns0:Fault xmlns:ns0="http://schemas.xmlsoap.org/soap/envelope/">
<faultcode>soap11env:Server</faultcode>
<faultstring>
'ascii' codec can't encode character u'\u2212' in position 33: ordinal not in range(128)
</faultstring>
<faultactor/>
</ns0:Fault>
miguelcleon commented 7 years ago

getvariables worked I think that timed out before.

lsetiawan commented 7 years ago

'ascii' codec can't encode character u'\u2212'

I think you have some sort of weird character that can't be handled by WOFpy. If so, please open up a separate issue for this so that we can improve the sophistication.

miguelcleon commented 7 years ago

yes it is a unicode minus sign http://www.fileformat.info/info/unicode/char/2212/index.htm which I may just be using as a dash in a site name.

miguelcleon commented 7 years ago

strangely I tried running getsites again and got a completely different error: 


<ns0:Fault xmlns:ns0="http://schemas.xmlsoap.org/soap/envelope/">
<faultcode>soap11env:Server</faultcode>
<faultstring>
(psycopg2.OperationalError) SSL SYSCALL error: EOF detected [SQL: 'SELECT DISTINCT odm2.sites.samplingfeatureid AS odm2_sites_samplingfeatureid, odm2.samplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, odm2.sites.spatialreferenceid AS odm2_sites_spatialreferenceid, odm2.sites.sitetypecv AS odm2_sites_sitetypecv, odm2.sites.latitude AS odm2_sites_latitude, odm2.sites.longitude AS odm2_sites_longitude, odm2.samplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, odm2.samplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, odm2.samplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, odm2.samplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, odm2.samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, odm2.samplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, odm2.samplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, odm2.samplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, odm2.samplingfeatures.featuregeometrywkt AS odm2_samplingfeatures_featuregeometrywkt, CASE WHEN (odm2.samplingfeatures.samplingfeaturetypecv = %(samplingfeaturetypecv_1)s) THEN %(param_1)s WHEN (odm2.samplingfeatures.samplingfeaturetypecv = %(samplingfeaturetypecv_2)s) THEN %(param_2)s ELSE %(param_3)s END AS _sa_polymorphic_on \nFROM odm2.samplingfeatures JOIN odm2.sites ON odm2.samplingfeatures.samplingfeatureid = odm2.sites.samplingfeatureid JOIN odm2.featureactions ON odm2.samplingfeatures.samplingfeatureid = odm2.featureactions.samplingfeatureid JOIN (odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid) ON odm2.featureactions.featureactionid = odm2.results.featureactionid \nWHERE odm2.featureactions.samplingfeatureid = odm2.sites.samplingfeatureid AND odm2.results.featureactionid = odm2.featureactions.featureactionid'] [parameters: {'param_1': 'Specimen', 'param_2': 'Site', 'samplingfeaturetypecv_2': 'Site', 'param_3': 'samplingfeatures', 'samplingfeaturetypecv_1': 'Specimen'}]
</faultstring>
<faultactor/>
</ns0:Fault>
miguelcleon commented 7 years ago

SSL SYSCALL error: EOF detected

A stackoverflow post suggested this is a disk out of space error, I deleted a bunch of daily database back ups to free space and the error doesn't seem to be happening now.

miguelcleon commented 7 years ago

I created #148 regarding the unicode character error.

lsetiawan commented 7 years ago

@miguelcleon Thanks for opening the issue. This is great. I'm glad we encountered that error. So have you tested all the other requests GetSiteInfo etc?

miguelcleon commented 7 years ago

running GetSiteInfo, it has been spinning for 15 minutes+

miguelcleon commented 7 years ago

eventually I got another EOF error:


<ns0:Fault xmlns:ns0="http://schemas.xmlsoap.org/soap/envelope/">
<faultcode>soap11env:Server</faultcode>
<faultstring>
(psycopg2.OperationalError) SSL SYSCALL error: EOF detected [SQL: 'SELECT odm2.timeseriesresults.resultid AS odm2_timeseriesresults_resultid, odm2.results.resultid AS odm2_results_resultid, odm2.timeseriesresults.xlocation AS odm2_timeseriesresults_xlocation, odm2.timeseriesresults.xlocationunitsid AS odm2_timeseriesresults_xlocationunitsid, odm2.timeseriesresults.ylocation AS odm2_timeseriesresults_ylocation, odm2.timeseriesresults.ylocationunitsid AS odm2_timeseriesresults_ylocationunitsid, odm2.timeseriesresults.zlocation AS odm2_timeseriesresults_zlocation, odm2.timeseriesresults.zlocationunitsid AS odm2_timeseriesresults_zlocationunitsid, odm2.timeseriesresults.spatialreferenceid AS odm2_timeseriesresults_spatialreferenceid, odm2.timeseriesresults.intendedtimespacing AS odm2_timeseriesresults_intendedtimespacing, odm2.timeseriesresults.intendedtimespacingunitsid AS odm2_timeseriesresults_intendedtimespacingunitsid, odm2.timeseriesresults.aggregationstatisticcv AS odm2_timeseriesresults_aggregationstatisticcv, odm2.results.resultuuid AS odm2_results_resultuuid, odm2.results.featureactionid AS odm2_results_featureactionid, odm2.results.resulttypecv AS odm2_results_resulttypecv, odm2.results.variableid AS odm2_results_variableid, odm2.results.unitsid AS odm2_results_unitsid, odm2.results.taxonomicclassifierid AS odm2_results_taxonomicclassifierid, odm2.results.processinglevelid AS odm2_results_processinglevelid, odm2.results.resultdatetime AS odm2_results_resultdatetime, odm2.results.resultdatetimeutcoffset AS odm2_results_resultdatetimeutcoffset, odm2.results.validdatetime AS odm2_results_validdatetime, odm2.results.validdatetimeutcoffset AS odm2_results_validdatetimeutcoffset, odm2.results.statuscv AS odm2_results_statuscv, odm2.results.sampledmediumcv AS odm2_results_sampledmediumcv, odm2.results.valuecount AS odm2_results_valuecount, CASE WHEN (odm2.results.resulttypecv = %(resulttypecv_1)s) THEN %(param_1)s WHEN (odm2.results.resulttypecv = %(resulttypecv_2)s) THEN %(param_2)s WHEN (odm2.results.resulttypecv = %(resulttypecv_3)s) THEN %(param_3)s WHEN (odm2.results.resulttypecv = %(resulttypecv_4)s) THEN %(param_4)s WHEN (odm2.results.resulttypecv = %(resulttypecv_5)s) THEN %(param_5)s WHEN (odm2.results.resulttypecv = %(resulttypecv_6)s) THEN %(param_6)s WHEN (odm2.results.resulttypecv = %(resulttypecv_7)s) THEN %(param_7)s WHEN (odm2.results.resulttypecv = %(resulttypecv_8)s) THEN %(param_8)s WHEN (odm2.results.resulttypecv = %(resulttypecv_9)s) THEN %(param_9)s WHEN (odm2.results.resulttypecv = %(resulttypecv_10)s) THEN %(param_10)s ELSE %(param_11)s END AS _sa_polymorphic_on \nFROM odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid JOIN odm2.featureactions ON odm2.featureactions.featureactionid = odm2.results.featureactionid JOIN odm2.samplingfeatures ON odm2.samplingfeatures.samplingfeatureid = odm2.featureactions.samplingfeatureid \nWHERE odm2.results.featureactionid = odm2.featureactions.featureactionid AND odm2.samplingfeatures.samplingfeaturecode = %(samplingfeaturecode_1)s GROUP BY odm2.results.variableid, odm2.timeseriesresults.resultid, odm2.results.resultid'] [parameters: {'param_11': 'results', 'param_10': 'Measurement', 'resulttypecv_3': 'Category coverage', 'resulttypecv_2': 'Profile Coverage', 'resulttypecv_1': 'Point coverage', 'resulttypecv_7': 'Section coverage', 'resulttypecv_6': 'Time series coverage', 'resulttypecv_5': 'Spectra coverage', 'resulttypecv_4': 'Transect Coverage', 'resulttypecv_9': 'Trajectory coverage', 'resulttypecv_8': 'Profile Coverage', 'resulttypecv_10': 'Measurement', 'param_9': 'Trajectory coverage', 'param_8': 'Profile Coverage', 'param_5': 'Spectra coverage', 'param_4': 'Transect Coverage', 'param_7': 'Section coverage', 'param_6': 'Time series coverage', 'param_1': 'Point coverage', 'param_3': 'Category coverage', 'param_2': 'Profile Coverage', 'samplingfeaturecode_1': u'Rio Icacos Trib-IO'}]
</faultstring>
<faultactor/>
</ns0:Fault>
miguelcleon commented 7 years ago

I reloaded apache and GetSiteInfo took just a few seconds to comeback.

http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetSiteInfo?site=odm2timeseries:Rio%20Icacos%20Trib-IO

miguelcleon commented 7 years ago

This works http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetValuesForASite?site=odm2timeseries:Rio%20Icacos%20Trib-IO&startDate=2016-12-01T12:00:00&endDate=2016-12-01T14:30:00

a different site works http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetSites?site=odm2timeseries:EP1

emiliom commented 7 years ago

Awesome!

Miguel, I recommend you test all the services (REST 1_1) that are provided in the example page at http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest_1_1/ EXCEPT GetSites w/o a site argument! It looks like the example site/location, "Rio Icacos Trib-IO", doesn't have the unicode issue, so it's a great test. It'll be fantastic if you can confirm by the end of the day today that ALL REST services on that page work fine, except the naked GetSites request. That'd be major progress. Then @lsetiawan goes on vacation, you go on travel, etc.

miguelcleon commented 7 years ago

trying to create a bounding box I get another unicode error for a different dash http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetSitesByBox?west=-65.85692813&south=18.26143335&east=-65.74067507&north=18.32232903&IncludeSeries=false

this bounding box didn't work http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetSitesByBox?west=-65.7800&south=18.2790&east=-65.7681&north=18.2870&IncludeSeries=false

this bounding box worked! http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetSitesByBox?west=-65.8333&south=18.3383&east=-65.8212&north=18.3483&IncludeSeries=false

This one works

http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetSiteInfo?site=odm2timeseries:Rio%20Icacos%20Trib-IO

This one works

http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetVariableInfo

this one works

http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetVariableInfo?variable=odm2timeseries:DO%20Concentration

This one works

http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetValuesForASite?site=odm2timeseries:Rio%20Icacos%20Trib-IO&startDate=2016-12-01T12:00:00&endDate=2016-12-01T14:30:00

miguelcleon commented 7 years ago

@emiliom @lsetiawan Everything seems to work! except when I get unicode errors from sampling feature codes.

even this worked:

http://dev-odm2admin.cuahsi.org/wofpy/odm2timeseries/rest/1_1/GetValues?location=odm2timeseries:Rio%20Icacos%20Trib-IO&variable=odm2timeseries:DO%20Concentration

miguelcleon commented 7 years ago

It would be a good Idea to try some other sites and variables though.

emiliom commented 7 years ago

Woo-hoo! That's great that everything not involving the unicode characters is working!

It would be a good Idea to try some other sites and variables though.

Yeah. I suggest you identify a handful of stations that don't have unicode characters, and represent a good contrast of data types -- stream data, met stations, etc. We can assess that thing are working ok, identify issues, etc, on those stations, while we wait for Don to come back and be able to look into the unicode issue.

I also suggest that you post here one or two stations (station codes) that do have unicode characters, which we can use for debugging.

lsetiawan commented 7 years ago

Hi @miguelcleon,

It has been a while since we address this issue. I am starting up the discussion again now that we have a new release. I'm not sure what your time is like this week, but when you get a chance, could you please test out the latest release of WOFpy in conda-forge with your Luquillo CZO ODM2 PostgreSQL DB running on Apache Server.

Please let me know if you run into any problems and document your fixes or errors. Thank you so much @miguelcleon. 😃

miguelcleon commented 7 years ago

I haven't noticed this problem on the new installation. If the issue comes back, this can be opened again.