upgrade to 4.7 community edition : backup with js_export.sh problem : ERROR HibernateDaoImpl:58

[laurenthdl]

 Hi,

I am trying to upgrade a 4.5 community server to 4.7

Following the upgrade path, I am to use buildomatic js-export.sh script to create a backup of jasperserver reports.

see bellow for code and errors.

I guess there may be  something wrong or broken, but does anyone have any hint where I should look for more information ?

Code:

Post Edited by laurenthdl at 09/10/2012 09:40

[laurenthdl]

 More information:

The SQL commands which goes wild is :

 

2012-09-10 11:46:56 CEST INSTRUCTION :  select this_.id as id2_0_, this_.version as version2_0_, this_.name as name2_0_, this_.parent_folder as parent4_2_0_, this_.childrenFolder as children5_2_0_, this_.label as label2_0_, this_.description as descript7_2_0_, this_.creation_date as creation8_2_0_, this_.update_date as update9_2_0_, this_1_.catalog as catalog19_0_, this_1_.mondrianConnection as mondrian3_19_0_, this_2_.beanName as beanName21_0_, this_2_.beanMethod as beanMethod21_0_, this_3_.serviceClass as serviceC2_22_0_, this_4_.data as data24_0_, this_4_.file_type as file3_24_0_, this_4_.reference as reference24_0_, this_5_.reportDataSource as reportDa2_25_0_, this_5_.query as query25_0_, this_5_.mainReport as mainReport25_0_, this_5_.controlrenderer as controlr5_25_0_, this_5_.reportrenderer as reportre6_25_0_, this_5_.promptcontrols as promptco7_25_0_, this_5_.controlslayout as controls8_25_0_, this_6_.olapClientConnection as olapClie2_28_0_, this_6_.mdx_query as mdx3_28_0_, this_6_.view_options as view4_28_0_, this_7_.jndiName as jndiName29_0_, this_7_.timezone as timezone29_0_, this_9_.type as type32_0_, this_9_.maxLength as maxLength32_0_, this_9_.decimals as decimals32_0_, this_9_.regularExpr as regularE5_32_0_, this_9_.minValue as minValue32_0_, this_9_.maxValue as maxValue32_0_, this_9_.strictMin as strictMin32_0_, this_9_.strictMax as strictMax32_0_, this_10_.file_type as file3_33_0_, this_12_.catalog as catalog35_0_, this_12_.username as username35_0_, this_12_.password as password35_0_, this_12_.datasource as datasource35_0_, this_12_.uri as uri35_0_, this_13_.reportDataSource as reportDa2_36_0_, this_13_.mondrianSchema as mondrian3_36_0_, this_14_.type as type37_0_, this_14_.mandatory as mandatory37_0_, this_14_.readOnly as readOnly37_0_, this_14_.visible as visible37_0_, this_14_.data_type as data6_37_0_, this_14_.list_of_values as list7_37_0_, this_14_.list_query as list8_37_0_, this_14_.query_value_column as query9_37_0_, this_14_.defaultValue as default10_37_0_, this_15_.driver as driver39_0_, this_15_.password as password39_0_, this_15_.connectionUrl as connecti4_39_0_, this_15_.username as username39_0_, this_15_.timezone as timezone39_0_, this_16_.dataSource as dataSource40_0_, this_16_.query_language as query3_40_0_, this_16_.sql_query as sql4_40_0_, case when this_12_.id is not null then 12 when this_13_.id is not null then 13 when this_1_.id is not null then 1 when this_2_.id is not null then 2 when this_3_.id is not null then 3 when this_4_.id is not null then 4 when this_5_.id is not null then 5 when this_6_.id is not null then 6 when this_7_.id is not null then 7 when this_8_.id is not null then 8 when this_9_.id is not null then 9 when this_10_.id is not null then 10 when this_11_.id is not null then 11 when this_14_.id is not null then 14 when this_15_.id is not null then 15 when this_16_.id is not null then 16 when this_.id is not null then 0 end as clazz_0_ from JIResource this_ left outer join JIMondrianXMLADefinition this_1_ on this_.id=this_1_.id left outer join JIBeanDatasource this_2_ on this_.id=this_2_.id left outer join JICustomDatasource this_3_ on this_.id=this_3_.id left outer join JIFileResource this_4_ on this_.id=this_4_.id left outer join JIReportUnit this_5_ on this_.id=this_5_.id left outer join JIOlapUnit this_6_ on this_.id=this_6_.id left outer join JIJNDIJdbcDatasource this_7_ on this_.id=this_7_.id left outer join JIListOfValues this_8_ on this_.id=this_8_.id left outer join JIDataType this_9_ on this_.id=this_9_.id left outer join JIContentResource this_10_ on this_.id=this_10_.id left outer join JIOlapClientConnection this_11_ on this_.id=this_11_.id left outer join JIXMLAConnection this_12_ on this_.id=this_12_.id left outer join JIMondrianConnection this_13_ on this_.id=this_13_.id left outer join JIInputControl this_14_ on this_.id=this_14_.id left outer join JIJdbcDatasource this_15_ on this_.id=this_15_.id left outer join JIQuery this_16_ on this_.id=this_16_.id where (this_.name=$1 and this_.parent_folder=$2)

2012-09-10 11:47:49 CEST ERREUR:  la colonne this_9_.maxvalue n'existe pas au caractère 1238

 

[laurenthdl]

 mmm looks to me as a bug in the js-export script.

The SQL that it generates calls to some fields that are not in the database. minvalue.

Is it because my install has some failure ? 

   Table « public.jilistofvalues »

 Colonne |  Type  | Modificateurs | Stockage | Description 

---------+--------+---------------+----------+-------------

 id      | bigint | non NULL      | plain    | 

Index :

    "jilistofvalues_pkey" PRIMARY KEY, btree (id)

Contraintes de clés étrangères :

    "fk4e86a776a8bf376d" FOREIGN KEY (id) REFERENCES jiresource(id)

Référencé par :

    TABLE "jiinputcontrol" CONSTRAINT "fkcac6a51262a86f04" FOREIGN KEY (list_of_values) REFERENCES jilistofvalues(id)

    TABLE "jilistofvaluesitem" CONSTRAINT "fkd37ceba993f0e1f6" FOREIGN KEY (id) REFERENCES jilistofvalues(id)

Contient des OID: non

Since the failure sometimes dosenot show always the same table, but  points to the same field

An other table : 

\d+ JIDataType

                          Table « public.jidatatype »

   Colonne   |          Type          | Modificateurs | Stockage | Description 

-------------+------------------------+---------------+----------+-------------

 id          | bigint                 | non NULL      | plain    | 

 type        | smallint               |               | plain    | 

 maxlength   | integer                |               | plain    | 

 decimals    | integer                |               | plain    | 

 regularexpr | character varying(255) |               | extended | 

 minvalue    | bytea                  |               | extended | 

 max_value   | bytea                  |               | extended | 

 strictmin   | boolean                |               | plain    | 

 strictmax   | boolean                |               | plain    | 

Index :

    "jidatatype_pkey" PRIMARY KEY, btree (id)

Contraintes de clés étrangères :

    "fk533bcc63a8bf376d" FOREIGN KEY (id) REFERENCES jiresource(id)

Référencé par :

    TABLE "jiinputcontrol" CONSTRAINT "fkcac6a512120e06f7" FOREIGN KEY (data_type) REFERENCES jidatatype(id)

Contient des OID: non

 

imho, there is something strange in the table structure.

 

[oesina]

Hi Laurent,

looks like your references were lost, so you lost Relational integrity.

Meaning you have Foreign Keys, but the Primary Keys were already deleted. Probably you used some customizations before or some other reasons.

 

Only two approaches to restore, both very painful and carry danger to break it all:

1. Add missing primary keys

2. Delete void foreign keys

 

Maybe you can save something report-by-report or sections and migrate manually to new installation.

 

Good luck,

Olga

[laurenthdl]

 Hi Olga, 

thanks for quick reply.

Is there wome way to get a list of all the reports and then export those via command line ?

If so, could you point me the link for that ?

That would be really great.

It is quite strange for me since the jasperserver seems to be working OK. But that is really anoying not to be able to see what has gone wrong and how.

[laurenthdl]

 OK.

Now I have a brand new install for 4.7, which exports fine.

Is there a way to export a repository from web interface into a file and then re-inject ?

From command line, i tried to export project by project, and sub porject by subproject without any success.

Are the reports and repository structure stored in the postgresql database ?

Or could there be a way to re-inject some files into jasperserver ?

Could that be because some upgrade were broken ? (I started from version jasperserver 4.3)

Is it possible to take the old js-export and try ?

Code:

./js-export.sh --uris /Skeletton --output-zip ~/backup/js-skeletton-export.zip...Same errors.org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:630)	at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)	at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)	at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1055)	at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1048)	at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryServiceImpl.findByName(HibernateRepositoryServiceImpl.java:939)	at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryServiceImpl.findByURI(HibernateRepositoryServiceImpl.java:914)	at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryServiceImpl.loadResource(HibernateRepositoryServiceImpl.java:256)	at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryServiceImpl$1.execute(HibernateRepositoryServiceImpl.java:176)	at com.jaspersoft.jasperserver.api.metadata.common.service.impl.HibernateDaoImpl.executeCallback(HibernateDaoImpl.java:55)	at com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.HibernateRepositoryServiceImpl.getResourceUnsecure(HibernateRepositoryServiceImpl.java:1

Post Edited by laurenthdl at 09/10/2012 15:58

[laurenthdl]

 I added manualy the missing column in postgres, export is now done.

But when importing into a brand new 4.7 installation,

<code>

./js-upgrade-newdb-ce.sh ~/backup/js-4.5.zip

</code>

though everything looks fine,

<code>

BUILD SUCCESSFUL

Total time: 41 seconds

Checking Ant return code: OK

----------------------------------------------------------------------

</code>

When trying to log in, it show a blank screen. 

 <code>

Internal server error occurred. Please contact your system administrator.

Details:

</code>

 

catalina.out shows :

<code>

2012-09-14 17:57:22,912 ERROR LocalDataSourceJobStore,main:3875 - ClusterManager: Error managing cluster: Failure identifying failed instances when checking-in: ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc

de la transaction

org.quartz.JobPersistenceException: Failure identifying failed instances when checking-in: ERREUR: Transaction canceled

       at org.quartz.impl.jdbcjobstore.JobStoreSupport.findFailedInstances(JobStoreSupport.java:3343)

        at org.quartz.impl.jdbcjobstore.JobStoreSupport.clusterCheckIn(JobStoreSupport.java:3393)

        at org.quartz.impl.jdbcjobstore.JobStoreSupport.doCheckin(JobStoreSupport.java:3263)

        at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.manage(JobStoreSupport.java:3869)

2012-09-14 17:57:22,931 ERROR LocalDataSourceJobStore,QuartzScheduler_quartzScheduler-jasperreports176-sandbox1347638238476_MisfireHandler:3951 - MisfireHandler: Error handling misfires: Database error recovering from misfires.

org.quartz.JobPersistenceException: Database error recovering from misfires. [see nested exception: org.postgresql.util.PSQLException: ERREUR: droit refusé pour la relation qrtz_triggers]

2012-09-14 17:57:22,944 ERROR ErrorLogger,quartzScheduler_QuartzSchedulerThread:2360 - An error occurred while scanning for the next triggers to fire.

org.quartz.JobPersistenceException: Couldn't acquire next trigger: ERREUR: permission denied pour la relation qrtz_triggers [see nested exception: org.postgresql.util.PSQLException: ERREUR: droit refusé pour la relation qrtz_triggers]

2012-09-14 17:57:25,456 ERROR JDBCExceptionReporter,main:101 - ERREUR: droit refusé pour la relation jimondrianxmladefinition

2012-09-14 17:57:25,457 ERROR HibernateDaoImpl,main:58 - Hibernate DataAccessException

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select this_.id as id3_1_, this_1_.version as version3_1_, this_1_.name as name3_1_, this_1_.parent_folder as parent4_3_1_, this_1_.childrenFolder as children5_3_1_, this_1_.label as label3_1_, this_1_.description as descript7_3_1_, this_1_.resourceType as resource8_3_1_, this_1_.creation_date as creation9_3_1_, this_1_.update_date as update10_3_1_, this_.catalog as catalog23_1_, this_.mondrianConnection as mondrian3_23_1_, parent1_.id as id2_0_, parent1_.version as version2_0_, parent1_.uri as uri2_0_, parent1_.hidden as hidden2_0_, parent1_.name as name2_0_, parent1_.label as label2_0_, parent1_.description as descript7_2_0_, parent1_.parent_folder as parent8_2_0_, parent1_.creation_date as creation9_2_0_, parent1_.update_date as update10_2_0_ from JIMondrianXMLADefinition this_ inner join JIResource this_1_ on this_.id=this_1_.id inner join JIResourceFolder parent1_ on this_1_.parent_folder=parent1_.id where parent1_.hidden=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

2012-09-14 17:57:29,008 ERROR JDBCExceptionReporter,http-8080-1:101 - ERREUR: droit refusé pour la relation jitenant

2012-09-14 17:57:29,015 ERROR ThemeTag,http-8080-1:86 - could not execute query; SQL [select this_.id as id7_0_, this_.tenantId as tenantId7_0_, this_.tenantAlias as tenantAl3_7_0_, this_.parentId as parentId7_0_, this_.tenantName as tenantName7_0_, this_.tenantDesc as tenantDesc7_0_, this_.tenantNote as tenantNote7_0_, this_.tenantUri as tenantUri7_0_, this_.tenantFolderUri as tenantFo9_7_0_, this_.theme as theme7_0_ from JITenant this_ where (this_.tenantId=?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select this_.id as id7_0_, this_.tenantId as tenantId7_0_, this_.tenantAlias as tenantAl3_7_0_, this_.parentId as parentId7_0_, this_.tenantName as tenantName7_0_, this_.tenantDesc as tenantDesc7_0_, this_.tenantNote as tenantNote7_0_, this_.tenantUri as tenantUri7_0_, this_.tenantFolderUri as tenantFo9_7_0_, this_.theme as theme7_0_ from JITenant this_ where (this_.tenantId=?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:633)

        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)

        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)

        at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)

        at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1046)

        at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1039)

 

</code>

[laurenthdl]

 Hi

it seems that this post does not inspire any answer.

Is the information pasted in previous message not informative enough ? Should I post a new thread ?

Is there any other information that you would need ? It is a debian squeeze linux virtual machine running on openVZ.

And 4.7 installation is a fresh install from the installation package, using the tomcat bundled.

And I am trying to import an old 4.5 version which itself was an upgrade from 4.2

It seems that some other people have had the same issue. Is there anything I could do to unlock this thread ?

[shmee]

I fixed my issue, which was similar, by using a stand alone version of Tomcat 7 (which is the managed version in Ubuntu 12-4), and then doing a WAR installation.  And following the upgrade steps from the install guide for using a new DB.  It is also important to use the export script from the /buildomatic folder from the old version of JasperServer, so in this case the 4.5 version.