Hi Mya,

I'm afraid we are going to need more information here. When performing a Copy-To-Study, a Copy-To-Study will only be successful if the ParticipantIds are properly listed within the data that is from the Assay (along with any other required information for the study, like the date or the visit number) before the information is then copied to the study's dataset. This is both when a manual copy-to-study is done or an automatic one via import as described here:

https://labkey.org/Documentation/wiki-page.view?name=publishAssayData

When you say the study has "no data to show", where are you looking exactly? Are you in the dataset that was created by the copy-to-study function or somewhere else?

The error "Ignoring filter/sort on column 'SourceLSID' because it does not exist." is only appearing due to the view trying to refer to a column that isn't there in the table or query.

Can you supply us with screenshots and/or logs that correspond with the work you were doing with the Copy-To-Study so we can get a better sense of what is occurring?

Regards,

Jon

Hi Jon,

Thanks for your reply. Where would I find the log files? While I track them down, here are some answers to your questions:

J: When you say the study has "no data to show", where are you looking exactly? Are you in the dataset that was created by the copy-to-study function or somewhere else?

M: I mean the dataset that was created by the copy-to-study function (see study-dataset.png)

J: When performing a Copy-To-Study, a Copy-To-Study will only be successful if the ParticipantIds are properly listed within the data that is from the Assay (along with any other required information for the study, like the date or the visit number) before the information is then copied to the study's dataset.

M: Would this throw an error when you try to verify results? I got no errors on the verify results page (see copy-to-study.png). I got no errors when I clicked re-validate either. I have created columns for

- specimen id (unique ids for each row)
- participant id
- visit id (participant + visit is also unique)
- date (participant + date is not unique)
(see assay-design.png)

I should also note that I successfully copied a different assay dataset using the same procedure, and it worked fine. Could there be an interaction between the two assay datasets? The two use the same participant ids, and there may be some collisions between the two studies using the participant id + visit id combination since visit id is just an integer.

Hi Jon,

I think I've identified the log files. I found three: labkey-20171024.log, labkey-errors.log and postgresql-2017-10-24_000000.log. I deleted the entire study folder I was using and started new at roughly 1pm on October 24 so that I would have a clean log of my work.

I don't see any errors during the time I was loading data (do I have to turn logging on somehow?). Earlier in the day, we were attempting to install the LDAP module. I see this error in the postgres log, and also in the labkey-20171024 log:

2017-10-24 02:00:00 PDT ERROR: duplicate key value violates unique constraint "uq_statusfiles_filepath"
2017-10-24 02:00:00 PDT DETAIL: Key (filepath)=(C:/Program Files (x86)/LabKey Server/files/@files/system_maintenance_2017-10-24_01-59-59.log) already exists.
2017-10-24 02:00:00 PDT STATEMENT: INSERT INTO pipeline.statusfiles
                (_ts, createdby, created, modifiedby, modified, container, entityid, status, info, filepath, email, description, dataurl, job, provider, haderror, jobparent, jobstore, activetaskid, activehostname)
                VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NULL, $9, NULL, $10, NULL, $11, NULL, $12, NULL, $13, NULL, NULL)
        RETURNING rowid

I don't know what that has to do with my tasks though, as it occurred at 2 am.

Hi Mya,

The best log to provide is the labkey.log file which is located in the Tomcat logs subdirectory. It looks like your server is appending those logs with a date (e.g. labkey-20171024.log), which might be okay, but if the errors did not generate on the 24th that you observed, then the logs will not contain that information either. The specific logs you provided look like they're all only from the 24th. Do you by chance have any of the log info from the 17th or from whenever that error appeared?

Regarding copying to Study from an Assay, those Xs indicate that there's no matching participant Id and in order to copy from an assay results table to a study dataset, you need to have a valid participant Id that exists in the study already as apart of a demographics dataset.

Can you confirm that the particpantIds you're using the assay actually exist in the study? That is pretty much the only thing that would inhibit the ability to do a proper copy to study.

Regards,

Jon

Hi Jon,

RE: logs
I actually created a new folder and repeated all of my loading steps on the 24th in order to get clean logs. I don't know why none of this activity shows up.

RE: data
The X's indicate that there is no specimen repository with that name. It is not actually essential that there be one though, so this shouldn't cause the error (and doesn't for the other dataset I am loading). The ParticipantIDs are a subset of the demographics set.

I've figured out the problem, although I still don't know the solution. The problem was not with the data, but with the name I gave the assay. If I repeat exactly the same steps, with exactly the same data, but call the assay "Treatment2" instead of "Treatment", the data is successfully copied to study. I think there may have been some database corruption when I was originally playing with LabKey. Apparently deleting the toy directory was not enough to clear all previous data. I don't know how to fix this except by reinstalling LabKey.

At least I know what the problem is now. I thought I was going out of my mind for a while.

Thanks Jon, for all of your help. If you have a database fix idea, let me know. Otherwise, I'm going to reinstall.

Just for curiosity sake, I found the error in the labkey.log file:

ERROR Table 2017-10-25 11:27:19,875 p-apr-0.0.0.0-80-exec-41 : SQL [732]
    SELECT Label, Description, DescriptionRendererType, "DescriptionRendererType$Value"
    FROM (
    SELECT
    Treatment.label AS Label,
    Treatment.description AS Description,
    Treatment.descriptionrenderertype AS DescriptionRendererType,
    Treatment$DescriptionRendererType$.Value AS "DescriptionRendererType$Value",
    Treatment.container AS Container
    FROM (SELECT * FROM studydesign.c35d384_treatment
    WHERE (container='4d770f22-9b15-1035-96c1-eff84673f228'/* Clinical */)) Treatment
    
        LEFT OUTER JOIN (SELECT ? AS VALUE, ? AS RowId, ? As Ordinal UNION SELECT ? AS VALUE, ? AS RowId, ? As Ordinal UNION SELECT ? AS VALUE, ? AS RowId, ? As Ordinal) Treatment$DescriptionRendererType$ ON (Treatment.descriptionrenderertype = Treatment$DescriptionRendererType$.Value)) x
    WHERE (Run IS NOT NULL)
    ORDER BY ParticipantId ASC, SequenceNum ASC
    ?[1] RADEOX
    ?[2] 0
    ?[3] 0
    ?[4] HTML
    ?[5] 1
    ?[6] 1
    ?[7] TEXT_WITH_LINKS
    ?[8] 2
    ?[9] 2

    org.labkey.api.data.SqlExecutingSelector$ExecutingResultSetFactory.handleSqlException(SqlExecutingSelector.java:445)
    org.labkey.api.data.BaseSelector.handleResultSet(BaseSelector.java:280)
    org.labkey.api.data.SqlExecutingSelector.getResultSet(SqlExecutingSelector.java:147)
    org.labkey.api.data.SqlExecutingSelector.getResultSet(SqlExecutingSelector.java:175)
    org.labkey.query.QueryServiceImpl.select(QueryServiceImpl.java:2209)
ERROR ExceptionUtil 2017-10-25 11:27:20,047 p-apr-0.0.0.0-80-exec-41 : Exception detected and logged to mothership
org.springframework.jdbc.BadSqlGrammarException: ExecutingSelector; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "run" does not exist
  Position: 769