Installing jasperserver 4.0 with mysql 5.5

Installing jasperserver on tomcat with mysql5.5 it isn’t as straight forward as one might think (or as their manual implies). I was trying to install the latest version of jasperserver using the war method and found the script fails.
Reason: there are two files with the wrong syntax that need to be edited (this might also affect other versions of mysql and jasperserver). The files mentioned are located in this folder:
jasperreports-server-cp-4.0.0-bin/buildomatic/install_resources/sql/mysql/ and they are called:

js-create.ddl
quartz.ddl

Both of these files have the wrong syntax for creating the tables and setting its default type. This is how you fix it and successfully install jasperserver:
Download dependencies and jasperserver:

sudo apt-get install openjdk-6-jdk

You can grab the latest jasperserver from here. This will download a file called jasperreports-server-cp-4.0.0-bin.zip, if not, just rename it, then unzip it. Now lets start configuring things:

1. Change all occurrences of type=InnoDB to ENGINE=InnoDB (the files affected are listed below) and also
2. Change the following create table statement (notice the single back ticks around `maxValue`) maxValue is a reserved word in mysql5.5 and the compile fails if you try to create a table with a field name maxValue.
So open a text editor and edit the following 2 files:

js-create.ddl
quartz.ddl

jasperreports-server-cp-4.0.0-bin/buildomatic/install_resources/sql/mysql/js-create.ddl
jasperreports-server-cp-4.0.0-bin/buildomatic/install_resources/sql/mysql/quartz.ddl

These are the changes, they apply to both files above (obviously, don’t add the comment — CORRECT!!! —- when editing the file:

WRONG:

create table JIDataType (
        id bigint not null,
        type tinyint,
        maxLength integer,
        decimals integer,
        regularExpr varchar(255),
        minValue tinyblob,
        maxValue tinyblob,       --- WRONG !!!  ---
        strictMin bit,
        strictMax bit,
        primary key (id)
    ) ENGINE=InnoDB;
 
CORRECT:
    
       create table JIDataType (
        id bigint not null,
        type tinyint,
        maxLength integer,
        decimals integer,
        regularExpr varchar(255),
        minValue tinyblob,
        `maxValue` tinyblob,       --- CORRECT !!!  -----
        strictMin bit,
        strictMax bit,
        primary key (id)
    ) ENGINE=InnoDB;

3. You should now be able to compile and install jasperserver, first start by copying and editing the config file. Assuming you have downloaded and unzipped the jasperserver file into your home directory, do the following:

cd ~/jasperreports-server-cp-4.0.0-bin/buildomatic/
cp sample_conf/mysql_master.properties default_master.properties

4. Now edit the config file default_master.properties with your correct mysql settings, it should look similar to this:

appServerType = tomcat6
appServerDir = /var/lib/tomcat6
dbType=mysql
dbHost=localhost
dbUsername=yourmysqlusername
dbPassword=yourmysqlpassword
js.dbName=jasperserver

5. After the config file above has been copied and edited, run these:

./js-ant gen-config
./js-ant create-js-db
./js-ant init-js-db-ce
./js-ant import-minimal-ce
sudo ./js-ant deploy-webapp-ce

6. Restart tomcat

sudo service tomcat6 restart

7. After jasperserver has been installed and it is up and running, we need to edit the file below in jasperserver and reload the jar because jasperserver still fails with mysql 5.5 when handling numeric fields:

cp /var/lib/tomcat6/webapps/jasperserver/WEB-INF/lib/jasperserver-repository-hibernate-4.0.0.jar ~/tmp/
cd ~/tmp/
unzip jasperserver-repository-hibernate-4.0.0.jar

8. Use your preferred text editor to edit the line for maxValue (line #19) notice the addition of column=”[maxValue]” in the file com/jaspersoft/jasperserver/api/metadata/common/service/impl/hibernate/persistent/RepoDataType.hbm.xml:

vim com/jaspersoft/jasperserver/api/metadata/common/service/impl/hibernate/persistent/RepoDataType.hbm.xml

The entries look like the following, replace the one that says WRONG with the CORRECT one:

<property name="maxValue" type="serializable"/>  ---> WRONG

<property name="maxValue" column="[maxValue]" type="serializable"/>  ---> CORRECT

9. rename the original jar to something else or just delete it and rebuild the jar with your changes:

jar cvf jasperserver-repository-hibernate-4.0.0.jar com META-INF

10. Login to Tomcat Web Application Manager and stop jasperserver
11. Replace the old jar with the new one:

cd ~/tmp/
sudo cp -r jasperserver-repository-hibernate-4.0.0.jar /var/lib/tomcat6/webapps/jasperserver/WEB-INF/lib/

12. Restart jasperserver via Tomcat Web Application Manager

13. You’re done, you should now be able to have your own jasperserver running on mysql5.5 without any errors. Hopefully this tutorial helped you. Thank you for stopping by and please share with others, after all, code should be free.foscode.com | because code should be free

8 thoughts on “Installing jasperserver 4.0 with mysql 5.5

  1. Pingback: Open iReport jrxml from ExtJs as pdf | foscode

  2. Hi there,

    Can jasper server be used as a “proxy” to get to mysql, mysql is not allowing external connections….

    We need iReports (from other places(external)) to connect to mysql to create an iReport.

    Please be so kind as to help me….

  3. @Armand Groenewald You’d need to configure the mysql server to allow connections from the jasperserver, then you’d be able to access the data.

  4. A big THANK for this providential tutorial who saved me many hours… !
    Such an help is without price!

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>