changing jasper server from postgres to mysql.

0

Hi,

Is there a document I can follow that details how to change the jasper server report database instance from the default of postgres to mysql?

My purpose is to run just one database server on my jasper server. I have other MYSQL databases I need to restore to the report server and no postgres databases, therefore I might as well stick to one database server instead of running 2 on the same server.

I've been looking around but cannot find much on how to do this. I haev seen teh install guide but it's not clear.

Thanks,

OIliver

oliverleach's picture
Joined: Mar 7 2011 - 12:36am
Last seen: 7 years 2 months ago

Hi, 

I have gone through these steps,

link: https://community.jaspersoft.com/questions/543372/changing-jasper-server-postgres-mysql

And made changes accordingly, but when I run Jasper server using MySQL, the tomcat gives me these exception:

SEVERE [localhost-startStop-2] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query;SQL [select this_.id as id10_0_, this_.tenantId as tenantId10_0_, this_.tenantAlias as tenantAl3_10_0_, this_.parentId as parentId10_0_, this_.tenantName as tenantName10_0_, this_.tenantDesc as tenantDesc10_0_, this_.tenantNote as tenantNote10_0_, this_.tenantUri as tenantUri10_0_, this_.tenantFolderUri as tenantFo9_10_0_, this_.theme as theme10_0_ from JITenant this_ where this_.tenantId ilike ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:375) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1047) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1040) at com.jaspersoft.jasperserver.api.metadata.tenant.service.impl.TenantServiceImpl.getRepoTenant(TenantServiceImpl.java:115) at com.jaspersoft.jasperserver.api.metadata.tenant.service.impl.TenantServiceImpl.getPersistentTenant(TenantServiceImpl.java:440) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)

shubham.mishra - 2 years 1 month ago

6 Answers:

0

Hi, Oliver,

Sorry for my poor English, I cannot understand your questions.

1. Does your first question mean data migration from PostgreSQL to MySQL ? If so, there is many data migration tools outside, for example: http://www.lightbox.ca/pg2mysql.php

2. You said your purpose to run juse one database on you JasperServer. Which one ? PostgresSQL or MySQL.

For instance, once I install JasperServer with PostgreSQL at Tomcat, there is a database called jasperserver at postgresql.
If you have just wished your reports connecting to other MySQL database, you need to add MySQL jdbc driver into the directory $TOMCAT_HOME/lib first. All resource, user and report unit data are still stored at PostgreSQL side.

JasperServer guide is clear for normal installation only.

Ching



Post Edited by icecreaming at 2012-07-15 17:05
Ching Ice-creaming's picture
Joined: Oct 22 2011 - 7:32pm
Last seen: 6 years 4 months ago
0

Hi Ching,

Rather than having the jasperserver database running on POSTGRES, I would like to have the jasperserver database running on MYSQL. MYSQL was the database server for the jasperserver database in eariler versions of JasperReport server.

I have the MYSQL JDBC driver installed and working. I can connect to other MYSQL databases, no problem. However, I will be running MYSQL on the jasper server which will run a copy of various production databases for reporting purposes and I do not want to run 2 databases servers on the jasper report server, simply to make database administration easier.

So I would like to get rid of POSTGRES altogether. My question is how to do that and is there any documentation about this?

Thanks
Oliver

ps - your english is pretty good!

oliverleach's picture
Joined: Mar 7 2011 - 12:36am
Last seen: 7 years 2 months ago
1

I think you have some possibilities here. Here's one way, it has a lot of steps. The first thing to do is to back up your current Postgres repository, which you will then import later to another MySQL database.

- Make sure you've shutdown your Tomcat server

- Go to the buildomatic directory and do this from the command line:

./js-export.sh --everything --output-zip postgresbackup.zip

That zip file will contain everything (organizations, users, reports, schedules, etc) that JRS needs to run. Once you have the MySQL database ready to go, you'll use another script to import it to your new database.

I'm assuming that you don't want to redeploy your JRS web application, and that all you want to do is migrate over to a different database.

- Next, in the buildomatic directory, modify the default_master.properties file

This file will contain information about what database you're using. The easiest thing to do is to copy the mysql_master.properties file in the sample_conf directory, copy it to the buildomatic directory, and rename it default_master.properties, to replace the other file. Next edit that file, and put the specific details about your MySQL database in there.

- Next, run the buildomatic scripts to create the MySQL repository

These steps are the ones that you find in the installation guide. You only need to run those steps that deal with the database, so that means all of them except the last one that actually deploys the web application

./js-ant create-js-db

./js-ant create-sugarcrm-db 

./js-ant create-foodmart-db

./js-ant load-sugarcrm-db 

./js-ant load-foodmart-db

./js-ant update-foodmart-db

./js-ant init-js-db-pro

./js-ant import-minimal-pro

./js-ant import-sample-data-pro

Only the commands in bold are absolutely necessary; the others load sample databases and reports. It's up to you whether you want them or not.

- Next, run the import script to populate your MySQL database

From the same buildomatic directory, do this from the command line:

./js-import.sh --input-zip postgresbackup.zip

The script now uses your modified default_master.properties file, and imports the backup file to 

- Next, modify your context.xml file

Your context.xml file, which is under [TOMCAT_HOME]/webapps/jasperserver/META-INF, will contain JNDI entries that point to the old Postgres database. You'll need to modify the entries there with the right JDBC URL's,  and the right username/password.

Also, make sure to delete the [TOMCAT_HOME]/conf/Catalina/localhost/jasperserver.xml file (if it's there), because Tomcat will check for the existence of this file before reading in the context.xml file you just modified.

- Start up your Tomcat server

- Log in (Hopefully this works)

- Modify JDBC datasources where necessary

If you used the Postgres database as a datasource as well as a repository, you'll need to modify JDBC datasources and modify them so that they point to your MySQL databases.

As you can see, it's a lot of manual steps, with potential room for mistakes

 

gdmoreno's picture
13281
Joined: Oct 26 2010 - 3:23pm
Last seen: 2 months 2 weeks ago

Hi gdmoreno! nice post!
I would ask you if there a place I'd find ctl.sh script for mysql, because ctlscript.sh prepared in jasperserver works only for postgresql using directory with the same name...
service jasperserver mysql restart does not work if you not have prepared directory for such functionallity

lnuzzo - 7 years 3 days ago

This post is AWESOME!!!!!!!!!!!

michaelwjoyner - 6 years 9 months ago

Hi gdmoreno,
I have gone through the above steps and made changes accordingly,
but when I run Jasper server using MySQL, the tomcat gives me these exception:

SEVERE [localhost-startStop-2] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select this_.id as id10_0_, this_.tenantId as tenantId10_0_, this_.tenantAlias as tenantAl3_10_0_, this_.parentId as parentId10_0_, this_.tenantName as tenantName10_0_, this_.tenantDesc as tenantDesc10_0_, this_.tenantNote as tenantNote10_0_, this_.tenantUri as tenantUri10_0_, this_.tenantFolderUri as tenantFo9_10_0_, this_.theme as theme10_0_ from JITenant this_ where this_.tenantId ilike ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:375) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1047) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1040) at com.jaspersoft.jasperserver.api.metadata.tenant.service.impl.TenantServiceImpl.getRepoTenant(TenantServiceImpl.java:115) at com.jaspersoft.jasperserver.api.metadata.tenant.service.impl.TenantServiceImpl.getPersistentTenant(TenantServiceImpl.java:440) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy19.getPersistentTenant(Unknown Source) at com.jaspersoft.jasperserver.api.metadata.user.service.impl.ProfileAttributeServiceImpl.applyProfileAttributes(ProfileAttributeServiceImpl.java:752) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy18.applyProfileAttributes(Unknown Source) at com.jaspersoft.jasperserver.api.metadata.common.service.impl.ServerSettingsInitialization.onApplicationEvent(ServerSettingsInitialization.java:39) at com.jaspersoft.jasperserver.api.metadata.common.service.impl.ServerSettingsInitialization.onApplicationEvent(ServerSettingsInitialization.java:32) at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:96) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:334) at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:950) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482) at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:410) at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306) at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:112) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4853) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5314) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:753) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:729) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717) at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092) at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

shubham.mishra - 2 years 1 month ago
0

Thanks I will give that a shot and let you know how I get on. It a proof of concept so I can snapshot the vm for rollback purposes so happy to give this a try..

 

 

oliverleach's picture
Joined: Mar 7 2011 - 12:36am
Last seen: 7 years 2 months ago

We are using MariaDB for our application , we expect Jasper to make use of it . Will it be possible?

kavi_mathi - 6 years 4 months ago
0

We are using MariaDB for our application , we expect Jasperserver to make use of it . Will it be possible?

kavi_mathi's picture
Joined: May 29 2009 - 6:22am
Last seen: 5 years 3 months ago
0

Hi guys...
I want to ask about installing jasperserver war file for oracle instead of mysql and postgresql but it's getting error.
It cannot defined the oracle.
Can someone tell me the exact way to installing it in oracle?

 

safwan.mastukee90's picture
Joined: May 29 2014 - 7:31pm
Last seen: 5 years 8 months ago
Feedback
randomness