changing jasper server from postgres to mysql.

[oliverleach]

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

[Ching Ice-creaming]

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

[oliverleach]

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!

[gdmoreno]

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

 

[oliverleach]

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..

 

 

[lnuzzo]

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

[michaelwjoyner]

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

 

[kavi_mathi]

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

[kavi_mathi]

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

[safwan.mastukee90]

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?
 

[shubham.mishra]

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]

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)