Ref: #71

We should be able to speed things up and simplify the code a lot by implementing some of the following recommendations. My initial (but brief) prototyping has demonstrated that the following suggestions are feasible.

1. Load detections in to DB directly using PostgresQL's COPY command
   • one table which mirrors the info exactly from the uploaded CSV files
   • much faster and more straight-forward upload
   • no validation, just loading
   • will fix #9 implicitly
2. Validate and join to deployments/recoveries on the fly
   • select from smaller table first, e.g. receiver, then join to detections - should be pretty fast, provided appropriate indexes are in place (see the examples in #71)
   • removes the chance of data inconsistency, e.g when deployment info is added after detections, no need to rescanForDeployment
   • no need for the materialised view (and associated refresh, which is putting a lot of load on the DB)

Hi Kate,

I have just been entering tags for the project VIC Fisheries Snapper- Port Phillip Bay. I have entered 140 tags and have completed tag releases for all of them.

When I search for tag detections under this project it comes up with 230 detections all received on the station 01 Gellibrand. The receiver ID for the 01 Gelibrand station is 107481. When I look at the Vemco Database file that was used to export the CSV uploaded to the AATAMS database it shows a total of 1353 detections for receiver 107481.

The total number of detections for all receivers in this Vemco database is 520070. This suggests that we should expect to see far more detections associated with the Vic Fisheries Snapper- Port Phillip Bay Project in the AATAMS database.

Can you please take a look at this issue for me and see if you can see where the error is occurring?

Kind Regards

Phil McDowall

Hi Phil,

Can you please send through the csv file from VEMCO (if possible) so we can have a look at it and also roughly when did you do your upload?

Cheers,
Kate

Hi Kate,

Thanks for getting back to me, I have attached the csv files that were uploaded to the database. The files were uploaded to the AATAMS database on 11/12/2012 under the name Kade Mills Snapper Port Philip Bay 2012 events.csv & Kade Mills Snapper Port Philip Bay 2012 detections.csv . The uploader was Andre Steckenreuter.

Hope it helps.

Kind Regards

Phil McDowall

Note from Kate as Github won't allow files to be uploaded csv is saved in

Shery; > HelpDesk > AATAMS > Port Philip Bay Kade Mills CSV files uploaded to DAtabase

Due to having a slow RC environment, we require support to disable the materialized views creation, otherwise the slow RC environment just die while trying to do that.

There may be a problem entering tags which don't have a PINGER, e.g. only TEMP and/or ACCELEROMETER.

Email from Andrew:

Hi guys,

I have been sent the below email to edit tags in the system however I can’t seem to edit these tags from pinger tags to sensor tags,

From memory I have entered these as just sensor tags before see Rowley shoals project and now I seem not to be able,

I not the cause if someone can have a look and let me know asap,

Andrew

From: Alicia Schmidt-Roach [mailto:[email protected]]
Sent: Monday, 27 October 2014 7:22 AM
To: Andrew Boomer
Subject: Re: Problems entering tags

Hello Andrew,

Hope you had a good trip. Thanks for getting back to me so quickly. The tags to be deleted are listed below. They are all "Pinger", the accelerometer/pressure versions are correct. Thank you very much for your help.

A69-9002- 14734, serail number: 1127333
A69-9002- 10420, serial number: 1177659
A69-9002- 14714, serial number: 1127378
A69-1105-170, serial number: 1138523

Below is the tag I can not enter, I always get an error page when I try to.

Serial number = 1138535
Project = Chondrichthyan monitoring of south-east Australia
Model= V9AP
Code Map= A69-1105
Status= Deployed
Transmitter Type= Accelerometer
Ping Code= 190
Slope= 0.013588
intercept= 0
Unit= m.sec-3

Thanks again for all your help and let me know if you need any further information.

Best wishes,
Alicia

Alicia Schmidt-Roach
PhD Candidate| University of Tasmania
Institute for Marine and Antarctic Studies
Email: [email protected]
Tel: +61 404080023
Office: IMAS - Fisheries, Aquaculture & Coasts
Nubeena Crescent, TAROONA TAS 7053
Mail: University of Tasmania, Private Bag 49, HOBART TAS 7001
Web: www.imas.utas.edu.au

From: Andrew Boomer [email protected]
Date: Monday, 27 October 2014 10:25 AM
To: Alicia Schmidt-Roach [email protected]
Subject: RE: Problems entering tags

Hi Alicia,

Back in the office,

Any chance you can send me the tag ids and ill sort this out,

Just outline which ones to delete etc. and I’ll do the rest,

Kind regards,

Andrew

From: Alicia Schmidt-Roach [mailto:[email protected]]
Sent: Tuesday, 14 October 2014 11:55 PM
To: Andrew Boomer
Subject: Problems entering tags

Hello Andrew,

I've had some problems entering a tag into the data base. Every time I enter the tag information I get an ERROR page. Tag information listed below.

Serial number = 1138535
Project = Chondrichthyan monitoring of south-east Australia
Model= V9AP
Code Map= A69-1105
Status= Deployed
Transmitter Type= Accelerometer
Ping Code= 190
Slope= 0.013588
intercept= 0
Unit= m.sec-3

Also I entered about 4 tags as pingers instead of accelerometers and when I changed this (using edit and updated) it created a new tag. Is there a way I can delete the extra tags (i.e. the error tag)? This only occurs with tags that are accelerometers or pressure. I had no problem updating the pinger tags.

Thank you for your assistance and let me know if you need any further information.

Best wishes,
Alicia

Alicia Schmidt-Roach
PhD Candidate| University of Tasmania
Institute for Marine and Antarctic Studies
Email: [email protected]
Tel: +61 404080023
Office: IMAS - Fisheries, Aquaculture & Coasts
Nubeena Crescent, TAROONA TAS 7053
Mail: University of Tasmania, Private Bag 49, HOBART TAS 7001
Web: www.imas.utas.edu.au

For various reasons, some uploads have failed when they should've passed.

We should probably have an analyse of these, and re-process them, given that some bugs etc have now been fixed and they may now pass.

1. Go to 'tag detections'
2. Filter by a tag which has embargoed data (e.g. 'A69-1303-5029')
3. Download as CSV

Note that columns containing 'sensitive' data, such as species, should be blank.

Reported by Andre Steckenreuter although we think he shouldn't get all detections anyway because of project permissions,

Steps to reproduce:

Go to Tag Detections page and in the project field begin typing Narooma and select from list when it pops up.

Once the page has loaded with all of the detections for that Project, note the number above the entry fields reads 1119.

Click in the export data as csv checkbox

What does happen:

Andre with limited permission gets 288 records, Kate with all permissions gets 1165 records in the csv

What should happen:

Number of records in csv should be the same as what was indicated in the count

Therefore: should this then be reflective of the user's permissions but whether or not it is it's still incorrect

I repeated for AATAMS Glenelg Line and it indicated 3480 records and the CSV gave 3664

The day after the release of AATAMS 3.9.1 Andrew boomer attempted to upload some VR4 data, (20 December 2013)

This is his email:

Hi guys can someone have a quick look at the vr4 data i just uploaded,

it seems the report give me all detections as invalid as a result of the unknown receiver count?

recovery data was entered as well as all receiver info and I'm not sure whats going on,

regards,

Andrew

Possibly related to #58

Using the site today the Tag Detections page (https://aatams.emii.org.au/aatams/detection/index) wouldn't load and timed out. Possibly as a result of the performance of the materialised view.

When receiver data is uploaded into the database a report is produced indicating all of the details of the upload. If there is an error which is resolved and the data reloaded the report "should" update so that the user knows the error is resolved

These reports are not updating when the data is reloaded there is another being created

From Charlie Huveneers:

I’d like to include a map of all the station entered within the database, but I keep getting error message when I’m trying to download the .kmz file. It might be because of the number of stations and that my connection gets pinged out.

Steps To Repeat:
Connected to aatams3, wanted to list the name of all installation stations, along with their corresponding number of deployments and number of detections. Ran the following SQL to do this:

SELECT installation_station.name AS station_name,
installation_station.num_deployments AS no_deployments,
COUNT(*) AS no_detections
FROM aatams.valid_detection
FULL JOIN aatams.receiver_deployment ON valid_detection.receiver_deployment_id = receiver_deployment.id
FULL JOIN aatams.installation_station ON receiver_deployment.station_id = installation_station.id
GROUP BY installation_station.name,installation_station.num_deployments
ORDER BY no_deployments;

What I Expected To Happen:
I expected to get a list in which the number of deployments could not be equal to zero if there were detections. The values in the 'num_deployments' column of the installation_station table are therefore erroneous.

What Actually Happened:
A lot of installation stations had many detections and yet the number of deployments was equal to zero. Additionally, many installation stations had a number of detections equal to 1, not sure whether it's an error in the database too.

Ref: https://aatams.emii.org.au/aatams/receiverDownloadFile/show/97344649

Steps to reproduce:

1. Upload the CSV file available from the above link.

What does happen:

• valid detection count is 0.
• receiver "VR2W-104508" is reported as unknown, even though it is known to the system. Likewise for (at least some of) the other receivers in the upload.

What should happen:

• receiver "VR2W-104508" should not be reported as unknown
• valid detection count should be > 0

Saw when trying to upload CSV detection files using the AATAMS web app running on the YOLO vm.

Steps to reproduce:
Upload a CSV detection file with latitude and longitude columns left blank

What does happen:
The detections don't get uploaded, the web app reports a system error in the receiver_download_file table.

What should happen:
The web app should handle the case when lat/lon are left blank. To upload those detection files, I had to manually change all the blanks for the lat/lon to 0,0.

Uploads of detection CSVs for VR4 receivers is failing, with the following error in the logs:

2013-12-02 20:40:22,881 [quartzScheduler_Worker-2] [] ERROR FileProcessorService - Error processing file
java.lang.AssertionError: Invalid receiver name: VR4-UWM-250379. Expression: (tokens.size() == 2)

Steps to reproduce:

SET SEARCH_PATH = aatams, public;

SELECT * FROM animal 
WHERE sex_id IS NULL;

What happens:
208 records are returned.

What should happen:
No record should be returned, sex_id can only be male, female or unknown. Nulls should be unknown.

See: https://animaltracking.aodn.org.au/receiverDownloadFile/show/77509470

In this particular case, the system is expecting a column named "Date and Time (UTC)", with a example values such as "2011-07-07 02:22:34" (note, no timezone).

The error message given if the file is missing is "Incorrect format for Date and Time (UTC) expected yyyy-MM-dd HH:mm:ss Z".

This error message could be improved in two ways:

1. Tell the user that the column is missing or incorrectly named (probably as a result of using older VUE software)
2. Rather than including the java date format string in the error message, give an example date/time (as users will most likely be confused by the 'Z')

https://aatams.emii.org.au/[:]/animalRelease/show/32410103

instead of

https://aatams.emii.org.au/aatams/animalRelease/show/32410103

At the moment this problem is mitigated by a RewriteRule in apache. Please remove the rule after this issue is solved.

1. Upload detection file which has station name containing apostrophe, e.g. http://aatams.emii.org.au/aatams/receiverDownloadFile/show/70615542 (spider's ledge)

A org.postgresql.util.PSQLException is thrown.

We should be escaping/quoting things properly.

Steps to Reproduce:

Click on receiver deployment, sort/or don't sort byt project I did it with no sorting to start and then secondly sorted by AIMS scientific zone in case size was the cause
Click export data as PDF

What should happen:

Get data in a pdf

What does happen:

Get following error

File not found

Firefox can't find the file at https://aatams.emii.org.au/aatams/receiverDeployment/list.

Check the file name for capitalization or other typing errors.
Check to see if the file was moved, renamed or deleted.

This conflicts with chef db management that expects to be able to manage postgis functions in the public schema via the extension approach. There is also the potential for other general namespace conflicts.

Consequences are,

• Prevents a copy of the database being restored into data_warehouse alongside other imos data for use by project-officers and reporting.
• The aatams db is not currently managed by chef and therefore not benefitting from automatic back-ups.
• aatams test backup and restore scripts generate many errors about existing public postgis implementation created when running createdb, making it difficult to evaluate whether these operations were genuinely successful.

Reported by Andrew Boomer on behalf of Malcolm Francis (NIWA)

This will need investigation into when AB detections were uploaded to see if they were before the bug was fixed that was not associating detections and tags if they were entered in the "wrong" order. Could be a left over from that and require a big tidy up, or it may be a re-introduced bug.

Steps to reproduce:

From notification email user would click data link (as per trail) and be taken to https://aatams.emii.org.au/aatams/detection/list?filter.in=transmitterId&filter.in=A69-1303-40969 making sure user is logged out click export data as csv and open.

The log in and again click export data as csv

Compare the 2 files.

What should happen:

When logged out user should get no results downloaded

When logged in user should get all 257 records from that tag number with all details filled in

What does happen:

When logged out get 181 results that do not have species associated so system doesn't know to keep them secure

When logged in user does get all 257 records but AB uploads are missing details

Email trail:

No there is no embargo on receiver data,

My best guess is it’s a bug in the system but I will confirm this soon,

Thanks for the feedback,

Andrew

From: Malcolm Francis [mailto:[email protected]]
Sent: Tuesday, 26 November 2013 3:09 AM
To: Andrew Boomer
Subject: RE: AATAMS detections for tags [A69-1303-40969, A69-1601-3301]

OK, this is weird. I just compared the two downloads (logged in and not logged in) and when I’m not logged in I can retrieve all 181 records uploaded by someone called Andrew Boomer, but none of the records uploaded by Phil McDowall (15) or Stephanie Brodie (61). So is there an embargo or block on those data if not logged in? Or is it a data format issue – I just checked and found that none of your uploaded records have entries in the ‘tag ID’ and ‘species’ columns, whereas all the uploads by the other two do.

Cheers

Malcolm

Malcolm Francis
Principal Scientist, Coastal Group
National Institute of Water and Atmospheric Research Ltd
301 Evans Bay Parade, Greta Point, Wellington 6021, New Zealand
Private Bag 14901, Wellington 6241, New Zealand
Phone +64 4 386 0377

From: Andrew Boomer [mailto:[email protected]]
Sent: Tuesday, 26 November 2013 3:59 p.m.
To: Malcolm Francis
Subject: RE: AATAMS detections for tags [A69-1303-40969, A69-1601-3301]

Thanks Malcolm,

The record should be the same just the information is somewhat restricted for the logged out version,

Make sure that you are logged in as the information is of higher resolution in this view,

I will send this on to the eMII guys,

Regards,

Andrew

From: Malcolm Francis [mailto:[email protected]]
Sent: Tuesday, 26 November 2013 2:56 AM
To: Andrew Boomer
Subject: RE: AATAMS detections for tags [A69-1303-40969, A69-1601-3301]

Yep, that’s the problem. I get 257 records when logged in and 181 recorded when not for tag A69-1303-40969.

Cheers

Malcolm

Malcolm Francis
Principal Scientist, Coastal Group
National Institute of Water and Atmospheric Research Ltd
301 Evans Bay Parade, Greta Point, Wellington 6021, New Zealand
Private Bag 14901, Wellington 6241, New Zealand
Phone +64 4 386 0377

From: Andrew Boomer [mailto:[email protected]]
Sent: Tuesday, 26 November 2013 3:51 p.m.
To: Malcolm Francis
Subject: RE: AATAMS detections for tags [A69-1303-40969, A69-1601-3301]

Ok I can’t seem to replicate this one at all,

I will forward this on to the tech guys and see if we have a bug in the system,

Out of curiosity can you log in and see if you get the same problem,

Andrew

From: Malcolm Francis [mailto:[email protected]]
Sent: Saturday, 23 November 2013 3:11 AM
To: Andrew Boomer
Subject: FW: AATAMS detections for tags [A69-1303-40969, A69-1601-3301]

Re my data retrieval issue, please try the following and see what you get (this is what I've been doing):

1. Click on the 'data' link next to tag 40969. This should bring up the tag detections page listing "257 matching records" and showing the first of 13 pages of data.
2. Click on "export data as csv" box. Then "Open".

This delivers 181 records to me. How many do you get?

Note: I am not logged in while doing this - would that make a difference?

Cheers

Malcolm

Malcolm Francis
Principal Scientist, Coastal Group
National Institute of Water and Atmospheric Research Ltd
301 Evans Bay Parade, Greta Point, Wellington 6021, New Zealand
Private Bag 14901, Wellington 6241, New Zealand
Phone +64 4 386 0377, Fax +64 4 386 0574

From: [email protected] [[email protected]]
Sent: Tuesday, 12 November 2013 12:25 p.m.
To: Malcolm Francis
Subject: AATAMS detections for tags [A69-1303-40969, A69-1601-3301]
Dear Malcolm Francis,

The following tags have new detections as a result of a recent upload:
Tag Link to the data
A69-1303-40969
data...

A69-1601-3301
data...

Regards,
The AATAMS team

In some cases, extracted detections are not being related back to release (and therefore species) metadata.

Steps to reproduce:

1. Go to "Tag Detections"
2. Add filter on "tag ID" equal to "A69-1303-8035"
3. Export data as CSV

What happens:

timestamp,station name,latitude,longitude,receiver ID,tag ID,species,uploader,transmitter ID,organisation,sensor value,sensor unit
21/02/2008 5:43,NL1,-21.89902,113.93674,VR2W-101764,,,Andrew Boomer,A69-1303-8035,SARDI  ,,

Note there is no data in the species column.

What should happen:

Expect to see species data.

Email from Andrew

HI Jon and Rog,

Just touching base to see if you guys can shed a bit of light on a problem we are having with the data base,

We have just uploaded some historic data into the system from the Wenlock river array,

The report is telling me that we have 621 detections under “No deployment at time count”,

When I go through the vue system there is no data with this amount of detections?

What is causing this problem in the AATAMS data base?

Jon's investigation
He's talking about this: https://aatams.emii.org.au/aatams/receiverDownloadFile/show/86718282

The report is telling me that we have 621 detections under “No deployment at time count”,

When I go through the vue system there is no data with this amount of detections?

I have absolutely no idea what he means by this - maybe he can elaborate?

What this figure means is that for 621 of the uploaded detections, there was a receiver which matched the receiver name (in the detection record) but as far as the AATAMS DB knows, that receiver was not deployed at the time of the detection.

Having said that, I've just done a query on the DB to list the 621 dets in question and it looks like there's a bug here. It looks as though there is a deployment at the time of the detections (at least the 3 or 4 that I checked). Come and see me when you have a chance and I can explain.

We think Andrew through the "no" in "No Deployment at time count" was the abrev. for number so I have clarified in my email back to him it is No not Number.

Hi Andrew,

It appears there is an issue in the system that is not “connecting” deployments and detections.

We had a look into it and although it indicates that for the 621 detections there was no deployment at the time there actually was. Which indicates to us the disconnect.

Can you please clarify – it will help us when it comes to resolving it – if the detections or the deployment were entered first?

Thanks,
Kate

Reported by Andrew Boomer

Steps to reproduce:

Once Logged in to database, select tag detections from the menu on the lfet then in the project field start typing NRETA and select option from dropdown. (Can try with any name though ie type an a and pick anything from dropdown list)

What should happen:

Should get list of all entries from that project

What does happen:

Get the following error.

Proxy Error

The proxy server received an invalid response from an upstream server.
The proxy server could not handle the request POST /aatams/detection/list.

Reason: Error reading from remote server

Apache Server at aatams.emii.org.au Port 443

Steps to reproduce:
SELECT * FROM animal a FULL JOIN animal_release ar ON ar.animal_id = a.id WHERE animal_id IS NULL ORDER BY a.id;

What happens:
108 records from the 'animal' table are returned with no associated information from the 'animal_release' table.

What should happen:
@jkburges says that this is 'possible if a release is created then deleted (the release "belongs" to the animal, not the other way around)'. Shouldn't the animal be deleted at the same time then?

Steps to reproduce

Go to https://aatams.emii.org.au/aatams/detection/list
(If you get this far), filter by "AATAMS Rowley Shoals"

What should happen

It should load within a few seconds. Further, applying a filter to the list should also be fast.

What does happen

It either times-out, or takes a minute of two to load.

Note: Tim reported this

... because the database is running out of disk space when trying to refresh the materialised view.

Steps to reproduce:

1. Attempt a sign in with bad authentication details and format parameter set to "xml", e.g. using curl: curl -L -i "http://localhost:8080/aatams/auth/signIn?format=xml" --data "username=bad&password=stuff"

What should happen:
The client should be sent an XML response with status code 401.

What does happen:
The client is redirected to the HTML login page.

$ curl -i "http://localhost:8080/aatams/auth/signIn?format=xml" --data "username=bad&password=stuff"
HTTP/1.1 302 Moved Temporarily
Server: Apache-Coyote/1.1
Set-Cookie: rememberMe=deleteMe; Path=/aatams; Max-Age=0; Expires=Wed, 03-Sep-2014 04:13:37 GMT
Set-Cookie: JSESSIONID=64DD03A338DDDF3A088E327AF223F477; Path=/aatams
Location: http://localhost:8080/aatams/auth/login?username=bad
Content-Length: 0
Date: Thu, 04 Sep 2014 04:13:37 GMT

Note: the example XML upload script also needs to be updated to have the format=xml parameter included in the sign in.

Charlie Huveeners has reported the following error while trying to access https://aatams.emii.org.au/aatams/detection/list :

Proxy Error

The proxy server received an invalid response from an upstream server.
The proxy server could not handle the request GET /aatams/detection/list.

Reason: Error reading from remote server

Apache Server at aatams.emii.org.au Port 443

@kereid or I have been unable to repeat this error.

From Andrew Boomer:

Hi Jon,

just checking if this is a bug in the system,

when i try to change the serial numbers of the tags below it creates a new tag under the same serial,

any chance you can have a quick look please,

regards,

Andrew
From: De Faria, Fernanda [[email protected]]
Sent: Tuesday, 10 September 2013 1:21 PM
To: Andrew Boomer; Andre Steckenreuter; Phil McDowall
Subject: Please double check database

Hi Guys,

It appears that I made a mistake on the spreadsheet I sent you with the data from heron and OTI. Can you please change the tag serial numbers for pin codes 8446 – 8449

8446 serial number 1148263
8447 1148264
8448 1148265
8449 1148266

Please also double check
6723 1126341
6724 1126342
6725 1126343
6726 1126344

Cheers

Fernanda de Faria,MSc

On the IMOS portal

Steps to reproduce:

Add "AATAMS Acoustic Tag Recievers" layer to map.

Click on layer to open Get Feature infor on any layer point.

Click on any station link in GFI

What should happen:

Page is loaded

What does happen:

Get an error and page does not load:

Unable to connect

      Firefox can't establish a connection to the server at localhost:8080.

The site could be temporarily unavailable or too busy. Try again in a few
moments.
If you are unable to load any pages, check your computer's network
connection.
If your computer or network is protected by a firewall or proxy, make sure
that Firefox is permitted to access the Web.

Logging on behalf of Kath. See Kath for details.

Steps to reproduce:
SELECT *
FROM surgery s
FULL JOIN animal_release ar ON s.release_id = ar.id
WHERE s.id IS NULL

What happens:
Five records from the 'animal_release' table aren't associated with surgical information from the 'surgery' table.

What should happen:
Each animal release record should be linked to a surgery record.

This issue is probably related to #29 but seen by a different person loading up different things? so am logging it so it's on record as being reported, and then I can follow up with James and maybe Stephanie when issue #29 is resolved.

Email from James for the record:

Thanks for the email regarding the database issues we are facing. I just would like to clarify that it is not only the project manta csv uploads that are recording error messages. I have also received errors from shoalhaven uploads and I believe Stephanie Brodie also encountered an error whilst uploading data on the 29/10. I am unsure if these errors are related to the same issue or not.

Steps to reproduce:

1. Create a release with embargo date set.
2. Upload some detections matching tag from release.
3. Change the embargo date of the release.
4. Extract detections for the tag.

Note that the embargo date is the original one.

This is a problem because it can result in embargoed data being visible when it is not supposed to be.

There are some email notifications that come from the rc instance of AATAMS that indicate embargoes on tags are expiring, these shouldn't be going out to the community as it is rc and only meant for testing. They are duplicates of the correct emails that are going out from production AATAMS

Some go to groups and some go to individuals.

RC has been switched off to prevent further emails for now, and send Andrew an email advising.

It's a possibility that some issues in AATAMS (search the logs for DB connection errors) are being cause by DB outages, and the connection(s) not being properly re-connected/refreshed.

We need to investigate what happens to the tomcat/grails side of things when the DB goes down (this could possibly affect all of our webapps).

Note: this may be a chef/config bug, I think, since it came about after the migration to NSP/chef, but putting it here since it only manifests in AATAMS currently.

The auto-redirect after successful login is failing.

Steps to reproduce:

1. Go to a URL such as the one produced in "tag embargo expiration" email, which is to an embargoed entity, e.g.:
   https://aatams.emii.org.au/aatams/animalRelease/show/38784128
2. Login

What happens:

• user is redirected to an invalid URL, e.g. https://aatams.emii.org.au/aatamshttps://aatams.emii.org.au/aatams/animalRelease/show/38784128 (note the extraneous prefix of https://aatams.emii.org.au/aatams)

What should happen:

• user should be redirected to the correct URL, e.g. https://aatams.emii.org.au/aatams/animalRelease/show/38784128

The following exception occurs when sorting by uploader, as a non-admin user:

java.lang.NullPointerException: Cannot get property 'id' on null object
    at au.org.emii.aatams.ReceiverDownloadFileFilters$_applyVisibility_closure2.doCall(ReceiverDownloadFileFilters.groovy:27)
    at au.org.emii.aatams.ReceiverDownloadFileFilters.applyVisibility(ReceiverDownloadFileFilters.groovy:25)
    at au.org.emii.aatams.ReceiverDownloadFileFilters$_closure1_closure3_closure4.doCall(ReceiverDownloadFileFilters.groovy:16)

[cut]

Steps to reproduce:

1. Log in as non-admin user.
2. Go to: https://aatams.emii.org.au/aatams/receiverDownloadFile/list
3. Click column heading "Uploader" twice.

What happens:
The above exception occurs.

What should happen:
The receiver export list should be sorted (desc) by uploader.

An sql injection attack could modify any data in our production databases without trace. This includes dropping harvest, data_warehouse and aatams databases entirely.

There needs to be some explanation of the status codes returned and possible error messages, etc. See email to Vemco below...

Detection file uploads are erroring for some files, e.g. https://aatams.emii.org.au/aatams/receiverDownloadFile/show/77353288

The exception in the log is:

2013-10-10 09:53:06,876 [quartzScheduler_Worker-9] [] ERROR FileProcessorJob - 
Assertion failed: 

assert(detSurgery.detectionId)
       |          |
       |          null
       [surgeryId:4463056, sensorId:4462993, detectionId:null]

        at org.codehaus.groovy.runtime.InvokerHelper.assertFailed(InvokerHelper.java:386)
        at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.assertFailed(ScriptBytecodeAdapter.java:662)
        at au.org.emii.aatams.DetectionSurgery.addToPreparedStatement(DetectionSurgery.groovy:82)
        at au.org.emii.aatams.DetectionSurgery$addToPreparedStatement.call(Unknown Source)
        at au.org.emii.aatams.detection.JdbcTemplateVueDetectionFileProcessorService$_insertDetections_closure2_closure6.doCall(JdbcTemplateVueDetectionFileProcessorService.groovy:124)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)

Although it should be possible to create duplicate station names for different installations (since people often use naming schemes like "N1", "N2",...) it should not be possible to create duplicates within the same installation.

I just noticed that there currently are some duplicates in the database, so we need to both:

1. reconcile duplicates (through a liquibase migration or some such);
2. add a constraint to make it not possible to add duplicates in the first place.

Reported by James van den Broek:

Stations belong to projects.
People must be added to projects to see station names.

James was a member of the UQ Manta Ray project.

In "Create receiver deployment" James clicked the station drop down or started typing to auto complete.

Stations belonging to the project such as LEI_LHB, LEI_Groupler did not appear

Removed James as a member of project, added him back on.

He could now see the station names.

Running AATAMS from scratch (grails run-app) on a vagrant dbprod instance you end up with:

org.codehaus.groovy.runtime.InvokerInvocationException: liquibase.exception.MigrationFailedException: Migration failed for change set 20140827-AS-AddReceiverModel.groovy::1409101938-1::anguss00 (generated):
     Reason: liquibase.exception.DatabaseException: Error executing SQL INSERT INTO aatams.device_model (class, id, manufacturer_id, model_name, version) VALUES ('au.org.emii.aatams.ReceiverDeviceModel', '30', '14', 'VR3UWM', '0'): ERROR: insert or update on table "device_model" violates foreign key constraint "fkdcc4e40025f67029"
  Detail: Key (manufacturer_id)=(14) is not present in table "device_manufacturer".:
          Caused By: Error executing SQL INSERT INTO aatams.device_model (class, id, manufacturer_id, model_name, version) VALUES ('au.org.emii.aatams.ReceiverDeviceModel', '30', '14', 'VR3UWM', '0'): ERROR: insert or update on table "device_model" violates foreign key constraint "fkdcc4e40025f67029"
  Detail: Key (manufacturer_id)=(14) is not present in table "device_manufacturer".:
          Caused By: ERROR: insert or update on table "device_model" violates foreign key constraint "fkdcc4e40025f67029"
  Detail: Key (manufacturer_id)=(14) is not present in table "device_manufacturer".

I've discovered this whilst trying to create a unique index on timestamp, receiver_name, transmitter_id (which should indeed be unique) - there are some duplicates though.

e.g.

select count(*) from valid_detection
where timestamp = '2013-06-13 21:18:50+00'
and receiver_name = 'VR2W-113916'
and transmitter_id = 'A69-1601-28325'

gives 2 instead of the expected 1 (or 0 if there's no match).

Deletion of detection exports, and their associated detections, fails - or at least appear to the user to have failed.

Steps to repeat

1. Sign in and navigate to the "Receiver Exports" screen.
2. Select "info" symbol for the particular export (currently trying to find out which exports this affects).
3. Click "Delete".

What should happen
The export should be deleted, along with all of its associated detections, with a notification to that effect shown to the user.

What does happen

Proxy Error

The proxy server received an invalid response from an upstream server.
The proxy server could not handle the request POST /aatams/receiverDownloadFile/index.

Reason: Error reading from remote server

Apache Server at aatams.emii.org.au Port 443

My guess is that the delete operation is taking a long time, upsetting apache which sits in between as a result.

Some animals that have been tagged don't have a scientific_name, family, genus, or common_name linked to them. The column 'name' in the species has some information for some animals, but for some others it is null or incorrect (e.g. 'range test tag 200m, 37018036, example, example1)

Steps to reproduce:
SELECT a.id,
a.version,
a.sex_id,
a.species_id,
s.*
FROM animal a
JOIN species s ON a.species_id = s.id
WHERE s.common_name IS NULL OR s.scientific_name IS NULL OR family IS NULL;

What happens:
65 records are returned, for which there are no taxonomic information available (e.g. scientific_name, common_name, family, genus)

What should happen:
The species table should hold that information at least for all animals that have been tagged.

There is currently some confusion, and possibly a bug surrounding a couple of sets of tags owned by Fernanda De Faria and Mario Espinoza.

Email trail to follow...