Grouper Users - Open Discussion List

[Chris Hyzer <>]

You need to edit your sources.xml for postgres...  see this in the 
sources.example.xml:

             <!--  for postgres, use this query since no concat() exists:
             
             select
   subject.subjectid as id, subject.name as name,
   lfnamet.lfname as lfname, loginidt.loginid as loginid,
   desct.description as description, emailt.email as email
from
   subject
   left join (select subjectid, value as lfname from subjectattribute
     where name='name') lfnamet
     on subject.subjectid=lfnamet.subjectid
   left join (select subjectid, value as loginid from subjectattribute
     where name='loginid') loginidt
     on subject.subjectid=loginidt.subjectid
   left join (select subjectid, value as description from subjectattribute
      where name='description') desct
     on subject.subjectid=desct.subjectid
   left join (select subjectid, value as email from subjectattribute
      where name='email') emailt
     on subject.subjectid=emailt.subjectid
where
   (lower(name) like '%' || ? || '%')
   or (lower(lfnamet.lfname) like '%' || ? || '%')
   or (lower(loginidt.loginid) like '%' || ? || '%')
   or (lower(desct.description) like '%' || ? || '%')
   or (lower(emailt.email) like '%' || ? || '%')

-->

-----Original Message-----
From: 

 
[mailto:]
 On Behalf Of Keith Hazelton
Sent: Tuesday, September 25, 2012 1:52 PM
To: 

Cc: pbIam
Subject: [grouper-users] Error when running Grouper Export

I have Grouper 2.1.2 installed on a CentOS 6.2 VM and configured to use 
Postgres. The UI works fine, I've created some folders under Root:bamboo and 
wanted to do a trial export/import cycle. Here's the console log of the 
export step. 

Note that there is a java error stack near the end, but that the process 
completes with a status line and an export file is created. 
Any clue what's causing the error? 
Any idea if the resulting file is good?

 Thoughts appreciated, --Keith


----------
[root@arche
 bin]# ./gsh -xmlexport -stems Root:bamboo /home/khazelton/bmboo-1.xml
Using GROUPER_HOME: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin/..
Using GROUPER_CONF: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf
Using JAVA: /usr/lib/jvm/java-1.6.0-openjdk/bin/java
using MEMORY: 64m-750m
(note, might need to type in your response multiple times (Java stdin is 
flaky))
(note, you can whitelist or blacklist db urls and users in the 
grouper.properties)
Are you sure you want to export data to xml in db user 'grouper', db url 
'jdbc:postgresql://localhost:5432/grouperdb'? (y|n): 
y
Continuing...
Grouper starting up: version: 2.1.2, build date: 2012/08/14 15:02:01, env: 
<no label configured>
grouper.properties read from: 
/opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/grouper.properties
Grouper current directory is: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin
log4j.properties read from: 
/opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/log4j.properties
Grouper is logging to file: 
/opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin/../logs/grouper_error.log, at 
min level WARN for package: edu.internet2.middleware.grouper, based on 
log4j.properties
grouper.hibernate.properties: 
/opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/grouper.hibernate.properties
grouper.hibernate.properties: 
grouper@jdbc:postgresql://localhost:5432/grouperdb
sources.xml read from: 
/opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/sources.xml
sources.xml groupersource id: g:gsa
sources.xml groupersource id: grouperEntities
sources.xml jdbc source id: jdbc: GrouperJdbcConnectionProvider
Subject API error: error with subject source id: jdbc, name: Example JDBC 
Source Adapter, problem with search, in sources.xml: serachType search: , 
edu.internet2.middleware.subject.SourceUnavailableException: ERROR: function 
concat(character varying, unknown) does not exist, source: jdbc, sql: select
 s.subjectid as id, s.name as name,
 (select sa2.value from subjectattribute sa2 where name='name' and 
sa2.SUBJECTID = s.subjectid) as lfname,
 (select sa3.value from subjectattribute sa3 where name='loginid' and 
sa3.SUBJECTID = s.subjectid) as loginid,
 (select sa4.value from subjectattribute sa4 where name='description' and 
sa4.SUBJECTID = s.subjectid) as description,
 (select sa5.value from subjectattribute sa5 where name='email' and 
sa5.SUBJECTID = s.subjectid) as email
from 
 subject s
where
 s.subjectid in (
 select subjectid from subject where lower(name) like 
concat('%',concat(?,'%')) union
 select subjectid from subjectattribute where searchvalue like 
concat('%',concat(?,'%'))
 )
 at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.searchHelper(JDBCSourceAdapter.java:380)
 at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.search(JDBCSourceAdapter.java:300)
 at 
edu.internet2.middleware.subject.SubjectCheckConfig.checkConfig(SubjectCheckConfig.java:149)
 at 
edu.internet2.middleware.grouper.misc.GrouperCheckConfig$1.callback(GrouperCheckConfig.java:443)
 at 
edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(GrouperSession.java:801)
 at 
edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(GrouperCheckConfig.java:439)
 at 
edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartup.java:132)
 at 
edu.internet2.middleware.grouper.xml.export.XmlExportGsh.main(XmlExportGsh.java:118)
 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)
 at 
edu.internet2.middleware.grouper.app.gsh.GrouperShell.handleSpecialCase(GrouperShell.java:204)
 at 
edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:144)
 at 
edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)
Caused by: org.postgresql.util.PSQLException: ERROR: function 
concat(character varying, unknown) does not exist
 at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
 at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
 at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
 at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
 at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
 at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
 at 
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
 at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSqlResults(JDBCSourceAdapter.java:625)
 at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.searchHelper(JDBCSourceAdapter.java:346)
 ... 14 more


Starting: 458 records in the DB to be exported (not exact, might be less)
DONE: 12:42:27: exported 458 records to: /home/khazelton/bmboo-1.xml
[root@arche
 bin]# vim /home/khazelton/bmboo-1.xml