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
15 Answers:
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 --update
11:49:39,884 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:39,891 WARN JDBCExceptionReporter:55 - bind <unnamed> to S_2
11:49:39,892 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:39,892 WARN JDBCExceptionReporter:55 - StartTransactionCommand
11:49:39,893 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:39,899 WARN JDBCExceptionReporter:55 - execute S_2: COMMIT
11:49:39,900 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:39,900 WARN JDBCExceptionReporter:55 - ProcessUtility
11:49:39,900 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:39,901 WARN JDBCExceptionReporter:55 - CommitTransactionCommand
11:49:39,901 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:39,901 WARN JDBCExceptionReporter:55 - CommitTransaction
11:49:39,902 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11: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: 23502
11:49:40,595 ERROR JDBCExceptionReporter:78 - ERROR: null value in column "id" violates not-null constraint
11:49:40,654 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:40,655 WARN JDBCExceptionReporter:55 - parse S_3: ROLLBACK
11:49:40,663 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:40,673 WARN JDBCExceptionReporter:55 - StartTransactionCommand
11:49:40,673 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:40,674 WARN JDBCExceptionReporter:55 - bind <unnamed> to S_3
11:49:40,674 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:40,675 WARN JDBCExceptionReporter:55 - execute S_3: ROLLBACK
11:49:40,675 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:40,681 WARN JDBCExceptionReporter:55 - ProcessUtility
11:49:40,693 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState: 00000
11:49:40,694 WARN JDBCExceptionReporter:55 - CommitTransactionCommand
11: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
</td></tr></tbody></table><br><br>Post Edited by franzkrauth at 04/30/2009 02:13 |
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.
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;
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
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
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
