JS3.5 and postgreSQL 8.2 login trouble...

[franzkrauth]

Hi all!

I am trying to run JS3.5 community using a remote DB Server and have been able to connect. But I can not manage to get the credentials through for authentication on login (for jasperadmin).

 

This is what the log spits out:

12:24:57,052 DEBUG SQL,http-8080-Processor24:393 - select this_.id as id3_0_, this_.username as username3_0_, this_.tenantId as tenantId3_0_, this_
.fullname as fullname3_0_, this_.emailAddress as emailAdd5_3_0_, this_.password as password3_0_, this_.externallyDefined as external7_3_0_, this_.e
nabled as enabled3_0_, this_.previousPasswordChangeTime as previous9_3_0_ from JIUser this_ where this_.tenantId is null and this_.username=?
 

Lots of thanks in advance! Any hints appreciated!

 

[edit 30-04-2009]

OK - I am one step further. This was a data problem. The server connects to the database just fine. I changed the password to "" and could log in. There is nothing else I can do - whenever I try to add anything (like a password for jasperadmin or a new user) it throws errors.

 

BTW: I am using the data Anandharaj provides in his blog: http://www.raj2u.net/jasperserver-installation-on-linux

I guess what I try next is migrating my mySQL from a working 3.1 server to this 3.5 using export and import.

 

Post Edited by franzkrauth at 04/29/2009 23:05

[franzkrauth]

Here the result:

Looks like my DB structure is not right...

 

No luck with anything. I could not find any upgrade script either. Is this even possible what I try to do? Jump from 3.1 MySQL to 3.5 postgreSQL? Maybe install a 3.1 on postgreSQL first? But how do I update the 3.1 postgre to 3.5 postgre...

Any hints on how to go on from here? All comments appreciated!!!

Post Edited by at 04/30/2009 02:01

Code:

root@jasper35:/opt/jasperserver-3.5/scripts# ./js-import.sh --input-dir js-catalog31 --update11:49:39,884  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:39,891  WARN JDBCExceptionReporter:55 - bind  to S_211:49:39,892  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:39,892  WARN JDBCExceptionReporter:55 - StartTransactionCommand11:49:39,893  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:39,899  WARN JDBCExceptionReporter:55 - execute S_2: COMMIT11:49:39,900  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:39,900  WARN JDBCExceptionReporter:55 - ProcessUtility11:49:39,900  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:39,901  WARN JDBCExceptionReporter:55 - CommitTransactionCommand11:49:39,901  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:39,901  WARN JDBCExceptionReporter:55 - CommitTransaction11:49:39,902  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:39,903  WARN JDBCExceptionReporter:55 - name: unnamed; blockState:           END; state: INPROGR, xid/subid/cid: 726995447/1/3, nestlvl: 1, children: <>11:49:40,543  WARN JDBCExceptionReporter:77 - SQL Error: 0, SQLState: 2350211:49:40,595 ERROR JDBCExceptionReporter:78 - ERROR: null value in column "id" violates not-null constraint11:49:40,654  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:40,655  WARN JDBCExceptionReporter:55 - parse S_3: ROLLBACK11:49:40,663  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:40,673  WARN JDBCExceptionReporter:55 - StartTransactionCommand11:49:40,673  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:40,674  WARN JDBCExceptionReporter:55 - bind  to S_311:49:40,674  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:40,675  WARN JDBCExceptionReporter:55 - execute S_3: ROLLBACK11:49:40,675  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:40,681  WARN JDBCExceptionReporter:55 - ProcessUtility11:49:40,693  WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 0000011:49:40,694  WARN JDBCExceptionReporter:55 - CommitTransactionCommand11:49:40,707 ERROR BaseExportImportCommand:45 - org.springframework.dao.DataIntegrityViolationException: could not insert: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole]; nested exception is org.hibernate.exception.ConstraintViolationException: could not insert: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole]org.springframework.dao.DataIntegrityViolationException: could not insert: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole]; nested exception is org.hibernate.exception.ConstraintViolationException: could not insert: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole]Caused by: org.hibernate.exception.ConstraintViolationException: could not insert: [com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole]        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)        at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:40)        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2108)        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2588)        at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)        at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:290)        at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:180)        at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:108)        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:98)        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)        at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)        at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)        at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)        at org.springframework.orm.hibernate3.HibernateTemplate$16.doInHibernate(HibernateTemplate.java:684)        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)        at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:681)        at com.jaspersoft.jasperserver.api.metadata.user.service.impl.UserAuthorityServiceImpl.putRole(UserAuthorityServiceImpl.java:468)        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)        at java.lang.reflect.Method.invoke(Method.java:585)        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)        at $Proxy3.putRole(Unknown Source)        at com.jaspersoft.jasperserver.export.modules.auth.AuthorityImporter.saveRole(AuthorityImporter.java:124)        at com.jaspersoft.jasperserver.export.modules.auth.AuthorityImporter.processRole(AuthorityImporter.java:107)        at com.jaspersoft.jasperserver.export.modules.auth.AuthorityImporter.importRoles(AuthorityImporter.java:93)        at com.jaspersoft.jasperserver.export.modules.auth.AuthorityImporter.process(AuthorityImporter.java:82)        at com.jaspersoft.jasperserver.export.ImporterImpl.process(ImporterImpl.java:146)        at com.jaspersoft.jasperserver.export.ImporterImpl.performImport(ImporterImpl.java:95)        at com.jaspersoft.jasperserver.export.ImportCommandImpl.process(ImportCommandImpl.java:57)        at com.jaspersoft.jasperserver.export.BaseExportImportCommand.process(BaseExportImportCommand.java:87)        at com.jaspersoft.jasperserver.export.ImportCommand.main(ImportCommand.java:43)Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:305)        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)        at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:33)        ... 38 more

Post Edited by franzkrauth at 04/30/2009 02:13

[swood]

We have just released JS CE 3.5 which includes Postgres scripts. Try that.

 

Sherman

Jaspersoft

[franzkrauth]

Will do!

[franzkrauth]

there is only upgrade-mysql-3.1.0-3.5.0-WARNING.sql but no upgrade-postgresql-3.1.0-3.5.0 script...

 

[franzkrauth]

Ok - I found the buildomatic scripts.

Configured the default-master.properties

But I think there is a bug somewhere I get the following error:

 

create-js-db:

 

BUILD FAILED

/opt/jasperserver-3.5.0/buildomatic/conf_source/db/defaultTargets.xml:19: The following error occurred while executing this line:

/opt/jasperserver-3.5.0/buildomatic/install.xml:40: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.50", user "jasperadmin", database "postgres", SSL off

 

 

configuration is: js.dbName=jasperserver

which pg_hba.conf has the right entry for...

 

any hints?

 

 

[tkavanagh]

It looks like you can't connect to your remote postgresql from the jasperserver host machine.

Postgresql is a little tricky this way - it's a security thing to that specific hosts and specific ports (I believe) must be opened.

the pg_hba.conf handles this access configuration. Perhaps try opening up everything - then tighten the access when you get everything running.

Also, look in the <js-install>/docs/JasperServer-Install-Guide.pdf. There is a section on where to find scripts that are in the buildomatic tree structure. You could create the jasperserver db by hand and run the schema (ie table and columns) creation by hand. See Section 6.2.

[franzkrauth]

I got it now!!

 

It looks like the script needs access to the postgres database to create the new jasperserver db...

I had to dump the old one as well...

 

Lets see if it runs...

[franzkrauth]

I ran the init-js-db-ce and the import-minimal-ce and can successful log in as jasperadmin...

 

Thanks for your help!

 

Now - import the 3.1 data and all is good!

[franzkrauth]

Ok - import went through too... and I can log in with my old credentials.

But trying to run a report does this:

 

org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

 

org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)

 

 

Whats wrong with the batch update?

 

[tkavanagh]

Here's a section from the Install Guide. Section 7 is the upgrade section.

Maybe clearing cached (compiled) reports would help...

 

7.10.4 Clearing the Repository Cache Table

In the jasperserver database, compiled JasperReports are cached in the JIRepositoryCache table for increased efficiency at
runtime. In some cases, you may encounter errors running reports after an upgrade. Because the JasperReports JAR is
typically updated with each new JasperServer release, old cached items can get out of date and thus cause errors at runtime. If
you encounter errors that mention a JasperReports “local class incompatible,” you should check your repository cache table.

 

To manually clear this table, run a SQL command similar to the following:

 

  update JIRepositoryCache set item_reference = null;
  delete from JIRepositoryCache;

[franzkrauth]

Didn't help. The reference was null already - so i emptied the table...

This is a complete new install... how can there be anything cached? Or will the export/import tool take the cache table as well?

I wonder if there is a configuration issue:

Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

Post Edited by franzkrauth at 05/01/2009 06:50

[tkavanagh]

Yes, check the following file:

  <tomcat>/webapps/jasperserver/WEB-INF/hibernate.properties

 

Should have a postgresql specific setting:

 

# Properties file with JDBC-related settings.
# Applied by PropertyPlaceholderConfigurer from "applicationContext-*.xml".
# Targeted at system administrators, to avoid touching the context XML files.

# Property that determines the Hibernate dialect

metadata.hibernate.dialect=com.jaspersoft.hibernate.dialect.PostgresqlNoBlobDialect

metadata.hibernate.dataSource.jndiName=jdbc/jasperserver
 

The older setting was the generic hibernate one:

 

metadata.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

 

 

[franzkrauth]

Hi tkavanagh and thanks for continuing answering my questions...

It seems that the no blob dialect is not known by hibernate:

 

ERROR [/jasperserver],main:3767 - Exception sending context initialized event to listener instance of class

com.jaspersoft.jasperserver.war.util.SpringBeanServletContextPlublisher

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in Servl

etContext resource [/WEB-INF/applicationContext.xml]: Invocation of init method failed; nested exception is org.hibernat

e.HibernateException: Dialect class not found: org.hibernate.dialect.PostgresqlNoBlobDialect

 

 

[franzkrauth]

Does anyone know what to do to make js find the PostgresqlNoBlobDialect class?

Please??

[franzkrauth]

Of course... how stupid... com.jaspersoft.hibernate.dialect.PostgresqlNoBlobDialect

All works now!!
 

Here quickly what I had to do:

Migrate from js3.1 running on a local mysql (but using data from a remote postgre 8.2) to js3.5 running on the remote postgre 8.2.

Here how it was done:

Use the export tools to create th catalogue from js3.1 (js-export.sh)
Install 3.5.0 with the installer on a new server install (ubuntu 8.04 by the way)
Update the config to point to the remote postgre server Create role and database on the postgre server
Unpack the buildomatic (doesn't come with the install) from jasperserver-3.5.0.bin.zip
Configure and generate the install scripts Install the initial database
Use the import tools (js-ant) to import my 3.1 catalogue

I used the documentation in the package. But big thanks as well to tkavanagh and Sherman (aka swood).

Post Edited by franzkrauth at 05/07/2009 04:50