Database Warnings/Errors

[Kate]

I'm having a problem that was addressed in a previous thread "Adding
Sample data to 14rc2" (http://groups.google.com/group/xnat_discussion/
browse_thread/thread/67e92e18fb5e7d01/4b5387f004d73649?lnk=gst&q=Adding
+Sample+data+to+14rc2#4b5387f004d73649), but a faulty subjectID doesnt
seem to be a problem in my case. (e.g. xnat:Subject
ID="nundaBETA_S00003", xnat:MRSession ID="nundaBETA_E00003" ). I'm
running xnat_1_4rc2 with PostgreSQL 8.4.1 on RedHatEl5.

Here's what I experience:
1) When building XNAT, Step 7 (Build the database), I get a number of
warnings about string literals and unknown types, but no errors. The
psql message (from the terminal that launched the command) is labeled
A below, and the postgres (from the terminal that launched pg_ctl
start) message is labeled B below.

2) When building XNAT, Steps 8 and 9 (Store default security settings
and field groups) both warn about datatypes with "no columns which
uniquely identify [them]" (see (C) below). Both steps do finish
successfully. (I do NOT have the field_groups issue described above.)

3) When I start tomcat (bin/startup.sh), postgres complains about non-
existing relations as Jan described (see (D) below). The web
interface appears normal.

4) When I'm working from the web interface, I can upload images from
the home page (Upload>Images) and from "Project View" (Actions
box>Upload Images), but postgres complains about "nonstandard use of \
\ or escape in string literal" (see (E) below). And when I click on
the newly added session (to go to "Experiment View"), postgres
complains about "relation "xdat_meta_element_meta_data" does not exist
at character 81" (see (F) below). This only happens at the link from
the upload screen.

5) I CANNOT upload from "Experiment View" (Actions box > upload >
upload scans). The scans seem to be loaded successfully (I am brought
to the page: Uploaded Image Scan Details, though all the fields are
empty), but no scans appear under the session. One difference I notice
is that in the problematic case, I've created subject and/or
experiment manually (From "Project View"> Action box>New).

6) Though the Transfer.xml pipeline runs without issue, I am having
trouble with queued custom pipelines, but I will save that for another
thread unless you think it's pertinent. The process of adding the
custom pipeline to the site and to the project, and then executing
"Build" on the session does not spark any warnings or errors from
postgres - it just remains queued forever .

I notice from looking at the xnat database that some of the column
names are rather unwieldy, for example:
xnat=# SELECT * FROM wrk_workflowdata;
Shows a column called
"executionenvironment_wrk_abstractexecutionenvironment_id". Is this
proper?

Thanks for your help!
Kate



------------------WARNING/ERROR MESSAGES-------------
A)
psql:deployments/xnat/sql/xnat.sql:74885: WARNING: nonstandard use of
\' in a string literal
LINE 1: ...REPLACE FUNCTION getnextview() RETURNS name AS '
DECLARE ...
^
HINT: Use '' to write quotes in strings, or use the escape string
syntax (E'...').
psql:deployments/xnat/sql/xnat.sql:74890: WARNING: nonstandard use of
\' in a string literal
LINE 1: ...OR REPLACE FUNCTION viewcount() RETURNS int8 AS '
DECLARE ...
^
HINT: Use '' to write quotes in strings, or use the escape string
syntax (E'...').
psql:deployments/xnat/sql/xnat.sql:74900: WARNING: nonstandard use of
\' in a string literal
LINE 1: ...PLACE FUNCTION removeviews() RETURNS varchar AS '
DECLARE ...
^
HINT: Use '' to write quotes in strings, or use the escape string
syntax (E'...').
psql:deployments/xnat/sql/xnat.sql:75000: WARNING: column "rpt" has
type "unknown"
DETAIL: Proceeding with relation creation anyway.
psql:deployments/xnat/sql/xnat.sql:75055: WARNING: column "su" has
type "unknown"
DETAIL: Proceeding with relation creation anyway.
psql:deployments/xnat/sql/xnat.sql:75055: WARNING: column "rpt" has
type "unknown"
DETAIL: Proceeding with relation creation anyway.
psql:deployments/xnat/sql/xnat.sql:75485: WARNING: column "rpt" has
type "unknown"
DETAIL: Proceeding with relation creation anyway.

B)
WARNING: nonstandard use of \' in a string literal at character 61
HINT: Use '' to write quotes in strings, or use the escape string
syntax (E'...').
WARNING: nonstandard use of \' in a string literal at character 58
HINT: Use '' to write quotes in strings, or use the escape string
syntax (E'...').
WARNING: nonstandard use of \' in a string literal at character 63
HINT: Use '' to write quotes in strings, or use the escape string
syntax (E'...').
WARNING: column "rpt" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
WARNING: column "su" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
WARNING: column "rpt" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
WARNING: column "rpt" has type "unknown"
DETAIL: Proceeding with relation creation anyway.

C)
Unable to connect to web service.
SETTINGS LOCATION: /usr/local/xnat/deployments/xnat/
WARNING: Data Type:cat:catalog has no columns which uniquely identify
it.
WARNING: Data Type:cat:catalog_metaField has no columns which uniquely
identify it.
WARNING: Data Type:cat:catalog_tag has no columns which uniquely
identify it.
WARNING: Data Type:cat:entry has no columns which uniquely identify
it.
WARNING: Data Type:cat:entry_metaField has no columns which uniquely
identify it.
WARNING: Data Type:cat:entry_tag has no columns which uniquely
identify it.
WARNING: Data Type:cat:dcmEntry has no columns which uniquely identify
it.
WARNING: Data Type:cat:dcmCatalog has no columns which uniquely
identify it.
WARNING: Data Type:pipe:PipelineRepository has no columns which
uniquely identify it.
WARNING: Data Type:pipe:pipelineDetails_element has no columns which
uniquely identify it.
WARNING: Data Type:pipe:pipelineDetails_parameter has no columns which
uniquely identify it.
WARNING: Data Type:xnat:statisticsData has no columns which uniquely
identify it.
WARNING: Data Type:xnat:abstractStatistics has no columns which
uniquely identify it.
WARNING: Data Type:xnat:contrastBolus has no columns which uniquely
identify it.
WARNING: Data Type:arc:project_pipeline has no columns which uniquely
identify it.
WARNING: Data Type:arc:pathInfo has no columns which uniquely identify
it.
WARNING: Data Type:wrk:abstractExecutionEnvironment has no columns
which uniquely identify it.
WARNING: Data Type:wrk:xnatExecutionEnvironment has no columns which
uniquely identify it.
WARNING: Data Type:wrk:xnatExecutionEnvironment_notify has no columns
which uniquely identify it.

D)
ERROR: column "xnat_projectData_resource_id" of relation
"xnat_projectdata_resource" does not exist
STATEMENT: SELECT pg_get_serial_sequence
('xnat_projectData_resource','xnat_projectData_resource_id') AS
col_name
ERROR: column "xnat_projectData_investigator_id" of relation
"xnat_projectdata_investigator" does not exist
STATEMENT: SELECT pg_get_serial_sequence
('xnat_projectData_investigator','xnat_projectData_investigator_id')
AS col_name
ERROR: relation
"xnat_projectdata_investigator_xnat_projectdata_investigator_id_" does
not exist at character 15
STATEMENT: SELECT * FROM
xnat_projectData_investigator_xnat_projectData_investigator_id_seq
ERROR: relation
"xnat_projectdata_investigator_xnat_projectdata_investigator__se" does
not exist at character 15
STATEMENT: SELECT * FROM
xnat_projectData_investigator_xnat_projectData_investigator__seq
ERROR: column "xnat_subjectData_resource_id" of relation
"xnat_subjectdata_resource" does not exist
STATEMENT: SELECT pg_get_serial_sequence
('xnat_subjectData_resource','xnat_subjectData_resource_id') AS
col_name
ERROR: column "xnat_experimentData_resource_id" of relation
"xnat_experimentdata_resource" does not exist
STATEMENT: SELECT pg_get_serial_sequence
('xnat_experimentData_resource','xnat_experimentData_resource_id') AS
col_name
ERROR: relation
"xnat_experimentdata_resource_xnat_experimentdata_resource_id_se" does
not exist at character 15
STATEMENT: SELECT * FROM
xnat_experimentData_resource_xnat_experimentData_resource_id_seq
ERROR: column "xnat_datatypeProtocol_fieldGroups_id" of relation
"xnat_datatypeprotocol_fieldgroups" does not exist
STATEMENT: SELECT pg_get_serial_sequence
('xnat_datatypeProtocol_fieldGroups','xnat_datatypeProtocol_fieldGroups_id')
AS col_name
ERROR: relation
"xnat_datatypeprotocol_fieldgroups_xnat_datatypeprotocol_fieldgr" does
not exist at character 15
STATEMENT: SELECT * FROM
xnat_datatypeProtocol_fieldGroups_xnat_datatypeProtocol_fieldGroups_id_seq
ERROR: relation
"xnat_datatypeprotocol_fieldgro_xnat_datatypeprotocol_fieldgro_s" does
not exist at character 15
STATEMENT: SELECT * FROM
xnat_datatypeProtocol_fieldGro_xnat_datatypeProtocol_fieldGro_seq

E)
WARNING: nonstandard use of escape in a string literal at character
100
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING: nonstandard use of \\ in a string literal at character 362
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 422
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 400
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 517
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 400
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 515
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 484
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 507
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 602
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 488
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 511
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 612
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 488
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 511
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 612
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 488
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 511
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 614
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 400
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 519
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 363
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 400
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal at character 520
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.

F)
ERROR: relation "xdat_meta_element_meta_data" does not exist at
character 81
QUERY: SELECT xdat_meta_element.*, meta.meta_data_id FROM
xdat_meta_element LEFT JOIN xdat_meta_element_meta_data meta ON
xdat_meta_element.meta_element_info=meta.meta_data_id WHERE
xdat_meta_element_id= $1
CONTEXT: PL/pgSQL function "update_ls_xdat_meta_element" line 6 at
FOR over SELECT rows
STATEMENT: SELECT update_ls_xdat_meta_element(549,NULL)

[Timothy Olsen]

Kate,

Most of the issues you present should not be any issue.

1) This can be ignored. This issue only shows up on certain versions of PostgreSQL and is only a warning.

2) The "no columns which uniquely..." warnings can be ignored. Unless, you start creating your own schema, and get these issues on the elements you create. Some data types don't need columns which uniquely identify them. In future versions the warnings on these particular elements will be concealed to prevent confusion.

3) This can be ignored. On init, XNAT checks the tables to see if everything is in order. It is a bit too strict in its review.

4) Same as issue 1.

5) I believe this is a known bug and has been fixed in developmental code. I'll review it though.

6) Mohana, do you have any ideas on this?

Regarding the column names, when a database schema is generated off of an XML schema you can get some pretty odd looking column names. Generally, XNAT is the only one who sees those column names, so it hasn't been an issue. They wouldn't be presented to site users, so we don't worry too much about how user friendly they look.

Thanks for the concise depiction of all of these. We'll use it as a checklist before final release.

Tim