Error Creating Database and Views

[VS]

Hi,

I am installing XNAT on a RHEL OS server, and am having the following
error when I try to upload images:

An error has occured.
Please contact your IT staff or the application logs for more
information

I went into the postgres log, and found this error:

LOG: database system was shut down at 2011-08-28 12:36:14 PDT
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
ERROR: relation "xdat_element_security" does not exist at character
74
STATEMENT: SELECT xdat_element_security.element_name AS
xdat_element_security0 FROM xdat_element_security
xdat_element_security;
ERROR: relation "xdat_security" does not exist at character 2550

I went into my database xnat, and verified that the tables exist, but
nothing beyond that. I then was speculating that perhaps the tables
exist, but they aren't correctly filled? This had me think back to
the installation:

INSTALLATION TROUBLE
I had trouble with step 6 here --> http://docs.xnat.org/XNAT+Installation+Guide

I have followed the instructions to a T, and when my xnat database was
being populated by the xnat.sql file (command sudo psql -d DBNAME -f
sql/DBNAME.sql -U postgres) it seemed to create all the functions ok,
but then it hit this error and stopped:

psql:sql/DBNAME.sql:81384: invalid command \')

When I looked at the sql file, this is the command with the error on
line 81384:

CREATE OR REPLACE FUNCTION getnextview() RETURNS name AS '
DECLARE my_record RECORD; viewName name; BEGIN FOR my_record IN
SELECT c.relname FROM pg_catalog.pg_class AS c LEFT JOIN
pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE
c.relkind IN (\'v\') AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast
\') AND pg_catalog.pg_table_is_visible(c.oid) LIMIT 1 LOOP
viewName := my_record.relname; END LOOP; RETURN (viewName); END; '
LANGUAGE 'plpgsql' VOLATILE;

The error is obviously with the backslashes around the V? I actually
uncommented the (\set ON_ERROR_STOP on;
) at the beginning to see the full chain of errors:

psql:sql/niaimbackup.sql:81384: invalid command \')
psql:sql/niaimbackup.sql:81389: invalid command \')
psql:sql/niaimbackup.sql:81394: ERROR: syntax error at or near "v"
LINE 1: ... AS n ON n.oid = c.relnamespace WHERE c.relkind IN
(\'v

^
psql:sql/niaimbackup.sql:81399: invalid command \'||
psql:sql/niaimbackup.sql:81404: ERROR: syntax error at or near "DROP"
LINE 1: ...t(); WHILE (viewCounter > 0) LOOP EXECUTE \'DROP
VIEW
^
psql:sql/niaimbackup.sql:81409: ERROR: current transaction is
aborted, commands ignored until end of transaction block

Knowing pretty much nothing about sql, I decided that the errors had
something to do with the 'v'\ and tried changing the ' symbols to "
symbols, and was able to get the function to run, and at least I
thought, finish populating the xnat database with the sql file.
However, in retrospect I realize that my fix likely skipped over
important steps, and the result was a faulty xnat installation.

So instead of troubleshooting the error after the installation, I want
to go back and figure out the original / first error I encountered,
which is the -->

psql:sql/niaimbackup.sql:81384: invalid command \')

I would be most appreciative for help and advice! I can try / post
any information / files that are needed promptly to figure this out.
Big thanks!!

Best,

Vanessa

[VS]

A few more notes:

1) To the best of my understanding, the dicom server comes with the
xnat installation in version 1.5? I want to note that there is a
previously installed dicom running on this server, although I'm not
sure how to check if there is some sort of conflict

2) The log above is from postgres, the following error is in the
application.log:

2011-09-04 00:01:09,447
[org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-4]
ERROR org.quartz.core.ErrorLogger - Job (prearchive-jobs.session-
rebuilder threw an exception.
org.quartz.SchedulerException: Job threw an unhandled exception. [See
nested exception: java.lang.NullPointerException]
at org.quartz.core.JobRunShell.run(JobRunShell.java:227)
at org.quartz.simpl.SimpleThreadPool
$WorkerThread.run(SimpleThreadPool.java:549)
Caused by: java.lang.NullPointerException
at
org.nrg.xnat.helpers.prearchive.SessionXMLRebuilderJob.execute(SessionXMLRebuilderJob.java:
67)
at
org.nrg.schedule.DelegatingJobBean.executeInternal(DelegatingJobBean.java:
55)
at
org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:
86)

3) axis.log:

1571 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] ERROR
org.apache.axis.configuration.EngineConfigurationFactoryServlet -
Unable to find config file. Creating new servlet engine config file: /
WEB-INF/server-config.wsdd

4) xdat.log:

2011-09-04 00:52:09,448
[org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-5]
ERROR org.nrg.xnat.helpers.prearchive.SessionXMLRebuilderJob -
org.postgresql.util.PSQLException: ERROR: relation
"xdat_search.prearchive" does not exist

at
org.postgresql.util.PSQLException.parseServerError(PSQLException.java:
139)
at
org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:
517)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:
50)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:
233)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:
221)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:
205)
at org.nrg.xft.db.PoolDBUtils.executeQuery(PoolDBUtils.java:
876)
at org.nrg.xnat.helpers.prearchive.PrearcDatabase
$22.op(PrearcDatabase.java:1238)
at org.nrg.xnat.helpers.prearchive.PrearcDatabase
$22.op(PrearcDatabase.java:1236)
at org.nrg.xnat.helpers.prearchive.PrearcDatabase
$SessionOp.run(PrearcDatabase.java:1639)
at
org.nrg.xnat.helpers.prearchive.PrearcDatabase.getAllSessions(PrearcDatabase.java:
1236)
at
org.nrg.xnat.helpers.prearchive.SessionXMLRebuilderJob.execute(SessionXMLRebuilderJob.java:
57)
at
org.nrg.schedule.DelegatingJobBean.executeInternal(DelegatingJobBean.java:
55)
at
org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:
86)
at org.quartz.core.JobRunShell.run(JobRunShell.java:216)
at org.quartz.simpl.SimpleThreadPool
$WorkerThread.run(SimpleThreadPool.java:549)


Thanks for your help in advance! I enjoy troubleshooting, and am
relatively new to unix, so it's not completely intuitive how to
navigate this landscape!

[Herrick, Rick]

Vanessa,

Couple of questions/comments:

* What version of PostgreSQL are you running? The newest version of PostgreSQL apparently stopped supporting the backslash-escaped single quote in scripts, although this may change/may have changed with later RC versions of that release.
* What version of XNAT are you trying to install? The newer versions of XNAT use the double single-quote means of escaping and so should avoid that issue, e.g. I just ran builds on both Windows and Mac and am seeing the single quotes escaped with double single quotes in the script, i.e. instead of (\'pg_catalog\', \'pg_toast\'), I see (''pg_catalog'', ''pg_toast'').
* You shouldn't need to run psql with sudo. psql does its own authentication based on the -U parameter. I'm wondering if something might be getting bollixed up with the permissions because of that.
* Regarding following the instructions "to a T", on one error message I see this: "psql:sql/DBNAME.sql" and on another I see this: "psql:sql/niaimbackup.sql". Are you actually running anything with DBNAME in there literally?

Hi,

Best,

Vanessa

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To post to this group, send email to xnat_di...@googlegroups.com.
To unsubscribe from this group, send email to xnat_discussi...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/xnat_discussion?hl=en.

________________________________

The material in this message is private and may contain Protected Healthcare Information (PHI). If you are not the intended recipient, be advised that any unauthorized use, disclosure, copying or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error, please immediately notify the sender via telephone or return mail.

[Herrick, Rick]

You're correct about #1, but I'm not sure what you need to do to squash the existing instance of the server.

Re: #2, there's a problem in the parameters being passed into the SessionXMLRebuilderJob, which I'm pretty certain has its root cause in the problem you're seeing in your #4. And I suspect that the #4 has its root cause in your SQL issues in your first email.

Re: #3, that's a pretty standard error message from Axis. It really should be a warning instead of an error, but we have no control over that really.

-----Original Message-----
From: xnat_di...@googlegroups.com [mailto:xnat_di...@googlegroups.com] On Behalf Of VS
Sent: Sunday, September 04, 2011 10:22 PM
To: xnat_discussion

A few more notes:

3) axis.log:

4) xdat.log:

--

[vanessa s]

Hi Rick,

Thanks for your prompt reply!  Here are answers to your questions:

* What version of PostgreSQL are you running? The newest version of PostgreSQL apparently stopped supporting the backslash-escaped single quote in scripts, although this may change/may have changed with later RC versions of that release.

I am running PostgreSQL 8.1.23.  I think that you are correct that it stopped supporting the backslash character, as I keep running into this error.

 

* What version of XNAT are you trying to install? The newer versions of XNAT use the double single-quote means of escaping and so should avoid that issue, e.g. I just ran builds on both Windows and Mac and am seeing the single quotes escaped with double single quotes in the script, i.e. instead of (\'pg_catalog\', \'pg_toast\'), I see (''pg_catalog'', ''pg_toast'').

I just re-downloaded the latest version (using wget for xnat_1_5_2.tar.gz, and I do think that I was using an old version, because the build.properties file is slightly different.  However, when I run setup.sh and give the psql command another go, I again run into the error with the slashes, and when I look at the .sql file, I still see (\'pg_catalog\', \'pg_toast\') instead of (''pg_catalog'', ''pg_toast'').  Given the new version of postgresql, and the slight difference of the build.properties file, I gave manually changing the \' to "s another try... and the solution didn't work for removeviews():

What I did is a combination of "s and double '' s - and that seemed to stop the production of errors, and given that this is the crux of the problem, here is the function how I changed it for your review!

--BR

--DEFINED FUNCTION

CREATE OR REPLACE FUNCTION removeviews()   RETURNS varchar AS ' DECLARE  viewName name;  viewCounter int8;  BEGIN  SELECT INTO viewName getnextview();  SELECT INTO viewCounter viewCount();  WHILE (viewCounter > 0)   LOOP    EXECUTE "DROP VIEW "|| viewName || " CASCADE";   RAISE NOTICE ''DROPPED %. % more.'',viewName,viewCounter;   SELECT INTO viewName getnextview();    SELECT INTO viewCounter viewCount();  END LOOP;   RETURN (''DONE''); END; '   LANGUAGE 'plpgsql' VOLATILE;

1) changed \' to " around DROP VIEW and CASCADE

2) changed \' to '' (double single ' ) around DROPPED %. % more.

3) changed \' to " around "DONE"

Is there someway of checking the database for correctness at this step?  What I did is log in as postgres and save the entire database as an .sql dump, for further troubleshooting.

* You shouldn't need to run psql with sudo. psql does its own authentication based on the -U parameter. I'm wondering if something might be getting bollixed up with the permissions because of that.

Ok, I didn't use sudo for psql.  I did need to use it for the StoreXML, however, because it would give me write access errors if I didn't, and then I also needed to pass the JAVA_HOME variable to sudo.  After the second StoreXML step (sudo env PATH=$PATH env JAVA_HOME=$JAVA_HOME /usr/local/XNAT/xnat/bin/StoreXML ./work/field_groups -u admin -p admin -allowDataDeletion true) I got this error:

No authentication.properties file found in conf directory. Skipping enhanced authentication method.

null

ERROR:  See log for details (logs/xdat.log).

In the log I see 18 entries of this type:

2011-09-06 07:58:12,350 [main] INFO  org.nrg.xft.XFT - WARNING: Data Type:xnat:statisticsData has no columns which uniquely identify it.

* Regarding following the instructions "to a T", on one error message I see this: "psql:sql/DBNAME.sql" and on another I see this: "psql:sql/niaimbackup.sql". Are you actually running anything with DBNAME in there literally?

 oh, don't worry, DBNAME is just a dummy variable for the database name... I am definitely not that dense!  The niaimbackup.sql was the original that I had backed up after changing "niaim.sql."  I was trying to be more secure in not publicly stating the database name, however I realize in retrospect how I promptly undid that on the following line :P  

Please let me know what I might try next, and if there are any logs or things that would be helpful to see.  I think that it makes most sense to first tackle these obvious errors, and then investigate the possible conflict with the other dicom installation.  Thanks for your help!

--
Vanessa Villamia Sochat
Stanford University '16

(603) 321-0676

[Vanessa]

I also just noticed that the default build.properties file describes
the URL as:

# eg. http://localhost:8080.xnat
xdat.url=http://localhost:8090.xnat

should that possibly be...

xdat.url=http://localhost:8090/xnat

> 0)   LOOP    EXECUTE *"*DROP VIEW *"|| viewName || " CASCADE"*;   RAISE

[Herrick, Rick]

Yes, definitely. That "8080.xnat" is a typo in the sample build.properties, it will be fixed in the next release. That said, this shouldn't affect any of the issues you're seeing.

I'm still looking into your last email to see what's working for us.

[Herrick, Rick]

Vanessa,

I just saw something else in this. I created a simple SQL script (shown below) to do some tests around this issue. If I convert the \' sequences to '', my script works just fine. With the \', it's another matter. In the postgresql.conf, there's a setting called standard_conforming_strings I'm playing with that lets you tailor the way Postgres handles strings and especially escaping characters. If I set standard_conforming_strings = off, then when I run my script, I get this warning:

WARNING: nonstandard use of \' in a string literal
LINE 16: '
^
HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...').
Query returned successfully with no result in 61 ms.

Now, a warning is all it is. My table has been created and populated and the function has been created. If I set standard_conforming_strings = on, then when I run my script, I get this warning:

ERROR: syntax error at or near "OOF"
LINE 22: ...ecord IN SELECT * FROM test WHERE thing NOT IN (\'OOF\', \'U...

This time no function or table is created.

What's interesting here is that neither one of those is the problem you're seeing. In fact, it just looks like something's completely fouled up in your scripts. What's really happening there isn't even related to how PostgreSQL processes the escape of single quotes, but is related to the script itself. It's not a bad string sequence, it's a bad COMMAND:

psql:sql/DBNAME.sql:81384: invalid command \')

It's not seeing that \') as part of the command in front of it, but as its own stand-alone command that is, for obvious reasons, meaningless. So the issue here really has nothing to do with \' vs. '', but instead stems from a parsing error because of a new line in the middle of that string.

So here's what we're thinking: your installation is completely and irretrievably screwed up. I suspect that the use of sudo in there may have gotten you into trouble. I haven't been on the XNAT team for too long and when I first started and was learning the system, I managed to get into lots of trouble by messing up the permissions on various parts of the installation. The errors from this sort of thing can be really weird and unpredictable, which sounds like what you're seeing. So here's what I'd suggest doing:

1) Drop your xnat database and re-create it.
2) Delete your xnat_builder folder and restore from the downloaded archive (alternatively, you can just do "rm -r deployments projects plugin-resources/cache work").
3) Run through the installation procedure again.

But let me add one more thing here: your best results will come from having the user that runs tomcat owning the XNAT build folder (in the installation instructions, this is referred to as XNAT_HOME), as well as the archive, prearchive, and cache folders. For example, on the VM images we use internally, we have a user named xnatdev who owns the Tomcat home folder (actually ~xnatdev IS the Tomcat home folder) and is used as the user for running the Tomcat service.

So, without messing with the Tomcat configuration and changing the user and supposing that your Tomcat user is named tomcat (you can look in the tomcat*.conf, it's probably something like /etc/tomcat6/tomcat6.conf, but I'm not sure how the RPM-installed Tomcat manages that), you could do the following:

tar xzf xnat_1_5_2.tar.gz
chown -R tomcat:tomcat xnat_1_5_2
cd xnat_1_5_2
sudo bash
su tomcat

From here, you should be able to run everything as straight up commands, e.g.:

bin/setup.sh -Ddeploy=true
StoreXML -etc blah bar:foo ad=nauseum

Then everything generated will be generated as belonging to tomcat and the conflicts should go away. When you go to run psql, you'll just do the command verbatim:

psql -U xnat -d xnat -f sql/xnat.sql

As I mentioned previously, psql has its own authentication outside of any permissions, so you generally don't need any elevation of authorization to access it, just the appropriate credentials.

Let me know if this helps you at all!

For testing purposes, here's the script I was running to test:

DROP TABLE IF EXISTS test;

CREATE TABLE test
(
thing character varying(255)
);

INSERT INTO test VALUES ('OOF');
INSERT INTO test VALUES ('UGH');
INSERT INTO test VALUES ('FILTH');
INSERT INTO test VALUES ('FLARN');
INSERT INTO test VALUES ('FILTH');

CREATE OR REPLACE FUNCTION mess_with_test()
RETURNS TEXT AS
'
declare
record RECORD;
fullText TEXT;
BEGIN
FOR record IN SELECT * FROM test WHERE thing NOT IN (\'OOF\', \'UGH\') LOOP
fullText := fullText || \' \' || record.thing;
END LOOP;
RETURN fullText;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

-----Original Message-----
From: xnat_di...@googlegroups.com [mailto:xnat_di...@googlegroups.com] On Behalf Of VS
Sent: Sunday, September 04, 2011 10:13 PM
To: xnat_discussion
Subject: [XNAT Discussion] Error Creating Database and Views

Hi,

Best,

Vanessa

--

[vanessa s]

To answer your question, in one word --> YES!  That was probably the most helpful set of instructions... ever!

I did the following:

1) stopped the postgresql server, deleted the old webapp, everything from the old xnat, the old database, created new ones.

2) figured out tomcat credentials, gave tomcat user ownership over new xnat build directory and the xnat image archive/prearchive/cache directories, and made sure the java and xnat home directories were correct and added to his path.

3) went through the install routine again as tomcat, unfortunately I hit the same error with the \'.  I tried changing the postgres.conf file and restarting the server, but that didn't help, and so I changed it back.  I wound up doing the same thing as last time... changing the symbols to " so it would run through.

4) Deployed the webapp, went to create a project, and upload images, and held my breath...

5) no more error!!  :D

6) Threw my latkas in the air!

It looks like it is fixed! (for now).  The lesson is that the tomcat user has to own the xnat installation and the folders associated with it.  My fingers are crossed that there wasn't real issue with the /' versus ", but you never know!  In the next week I will be testing most of the functionality, and all hopefully you won't see me on here again :P

Thank you thank you thank you!

EXPress -e happiness woot:woot!

[Herrick, Rick]

Careful there, latkas are a scarce resource, best to hold onto them until you really need them J

 

I’m glad this worked for you. As I said before, the permissions issues were one of the things that really bit me early on working with XNAT. The manifestations of problems there can show in all kinds of subtle (aka infuriatingly obscure) ways.

 

If you have any other issues, don’t hesitate to ask!

[Olsen, Tim]

FYI, I've modified the installation instructions to encourage UNIX users to adjust the file system permissions for the XNAT_HOME (builder) and archive/prearchive/cache directories to be the same as the user who will execute Tomcat.

 

Thanks for identifying the issue.

Tim