Database Connectivity Errors

The most common problems encountered with a new JasperReports Server instance are database configuration problems. If the connection fails, perhaps the application server cannot find the driver for the data source. For example, in a default installation of JasperReports Server, Tomcat looks for data source drivers in <js‑install>/apache-tomcat/lib. If the driver is not there, put a copy of the driver in this directory and restart Tomcat.

This section contains information that may help resolve other connectivity issues.

Testing the Database Connection

The simplest database configuration problem is an incorrect user name or password. If you encounter database problems upon startup or login, check the user name and password by logging directly into your RDBMS as described in the following sections.

You can connect to your database using the database configuration settings that are found in JasperReports Server. This validates the database hostname, port, username, and password that are being used.

If you are having trouble logging into JasperReports Server on the login page, you can check the users and passwords that exist by viewing the contents of the jasperserver.JIUser table.

Logging into PostgreSQL

Run the PostgreSQL client from the command line and try to connect to the database. For example:

psql -U postgres jasperserver

Logging into MySQL

Run the MySQL client from the command line and try to log in directly using the root user, for example:

<mysql>/bin/mysql -u root -p

You are prompted for a password for the user you specified on the command line. Enter the appropriate password to login. The default password used in the sample configuration scripts is password (jasperadmin in 2.1 and earlier).

Logging into Oracle

Start SQL*Plus and try to log into Oracle directly. Three users are created during installation:

To log in as each of these users, supply the password specified during installation.

Logging into Microsoft SQL Server

Run the sqlcmd and try to log into MSSQL Server directly. For example:

sqlcmd -S localhostjasperserver -d jasperserver -U jasperadmin -P password

Case Sensitive Collation in SQL Server

In Microsoft SQL Server, setting the collation to be case-sensitive is not supported. When collation is case-sensitive in SQL Server, column and table names are also treated as case-sensitive. This can happen when setting a locale that includes case‑sensitive collation and will cause an error:

Use a different locale or remove the case-sensitivity setting.

Maximum Packet Size in MySQL

If you are upgrading or importing into a MySQL database and your repository contains large objects such as images, you may see an error such as:

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

The default max_allowed_packet on the MySQL server is 1M (one Megabyte = 1,048,576 bytes). The most effective fix is to change this value in the server configuration to accommodate the largest resource stored in your repository. The server configuration file is typically named my.cnf (or my.ini) and is located in the MySQL root directory, although this may vary. Change the configuration setting to a larger value, for example:

max_allowed_packet = 16M

For more information, see http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html.

After changing this value, restart the MySQL server. Then perform the upgrade or import step again.

Connection reset by peer MySQL Error

If you are using the MariaDB JDBC driver in order to connect to the MySQL database, you can still get the same error as the maximum packet size error seen in the section above. However, the error message displayed by the MariaDB driver is different than that displayed by the MySQL JDBC driver. If you get an error similar to the following error:

Could not send query:

Connection reset by peer: socket write error

Then you should follow the instructions in the section above.

Case Sensitivity for Table and Column Names

Some databases are case-sensitive with respect to table names and will consider “customer” and “Customer” to be two different tables. If JasperReports Server is using a case-sensitive database, it’s important that the table names specified in query strings in the JRXML file of a saved report match the actual table names found in the database. A mismatch may occur if you are transferring data from one database to another, which may cause the capitalization of table names to change.

Under Windows MySQL, table and column names are not case-sensitive.

Under Linux MySQL, table and column names are case-sensitive. Linux MySQL can be configured to be non-case-sensitive by setting the configuration parameter lower_case_table_names to 1 in the my.ini or my.cnf file. For more information search the MySQL documentation for a section about identifier case sensitivity.

Table and column names in Oracle and PostgreSQL are case-sensitive.

PostgreSQL: Job Scheduling Error

If the Quartz settings under the PostgreSQL database have not been updated to specify the driver delegate class specific to PostgreSQL you will get errors when you try and run a scheduled report. The errors would look similar to the following:

If you see this error you will need to check your Quartz properties file found at the following location:

<tomcat>/webapps/jasperserver-pro/WEB-INF/js.quartz.properties

Make sure that the following property does not have the standard driver delegate, but instead has the PostgreSQL specific driver delegate. It should look like the following for PostgreSQL:

quartz.delegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Oracle js-install Script Hangs with Oracle 10g

If you plan to run the js-install script when installing to an Oracle database instance, check for the appropriate JDBC driver version. The default JDBC driver used for Oracle tends to be specific for newer Oracle versions. If you are installing to an older version of Oracle then the newer JDBC driver can cause execution to hang.

The default JDBC driver for Oracle can be seen in your buildomatic/default_master.properties file:

<js-install>/buildomatic/default_master.properties (final file name and location)

<js-install>/buildomatic/sample_conf/oracle_master.properties          (original file)

The settings for the default JDBC driver for Oracle are:

# maven.jdbc.artifactId=ojdbc5

# maven.jdbc.version=11.2.0

The settings above are commented out. Nevertheless, this is the JDBC driver that is used by default. These settings are appropriate for an Oracle version 11.2 instance.

If you are using Oracle 10.2, remove the comments from these settings and change them to:

maven.jdbc.artifactId=ojdbc14

maven.jdbc.version=10.2.0

The location of the Oracle JDBC drivers is here:

<js-install>/buildomatic/conf_source/db/oracle/jdbc

Performance Issues with Oracle JDBC Queries

Setting the Oracle database localization option defaultNChar to true can substantially impact the performance of JDBC queries. When defaultNChar is set to true, the database will implicitly convert all CHAR data into NCHAR when you access CHAR columns. If you do not need to support UTF-8 for your Oracle database, you can omit this setting.

The option you need and how to set it depends on your version of Java, your application server, and how it is deployed. For information about changing a JVM option setting for your particular environment, see your application server documentation.

To change this setting on Windows, enter a command such as the following at the command line:

set JAVA_OPTS=%JAVA_OPTS% -Doracle.jdbc.defaultNChar=false

To change this setting on Linux, enter a command such as the following at the command line:

export JAVA_OPTS="$JAVA_OPTS -Doracle.jdbc.defaultNChar=false"

Using an Oracle Service Name

If your Oracle database is configured to use a service name instead of an Oracle system identifier (SID), set up the service name by updating your default_master.properties file before using buildomatic:

<js-install>/buildomatic/default_master.properties

In default_master.properties, uncomment the serviceName property and enter your Oracle service name, for example:

serviceName=ORCL

When you are using an Oracle service name, make sure not to set the SID or dbPort in the default_master.properties file.

Oracle Error on Upgrade when PL/SQL Not Enabled

If you are upgrading to JasperReports Server version 5.6 or later using the js-upgrade-samedb.sh/bat script, you can encounter an error if Oracle's Procedural Language (PL/SQL) is not enabled.

The upgrade script used to upgrade to a 4.7 database from a 4.5 database requires the PL/SQL language to be enabled. The script is located here:

buildomatic/install_resources/sql/oracle/upgrade-oracle-4.5.0-4.7.0-pro.plsql

The error you encounter might look something like the following:

[advanced-sql] PLS-00103: Encountered the symbol “end-of-file”

If your PL/SQL language is not enabled please consult the documentation for your Oracle database to enable PL/SQL.

Error Running Scheduled Report

If you setup a scheduled report, chose to run it, and chose to save it as HTML or RTF, the report size can potentially get quite large. If you are running MySQL and you get the following error:

JDBC exception on Hibernate data access

org.hibernate.exception.GenericJDBCException: could not insert

the problem may be the default size of the MySQL blob datatype. You can increase the size of this datatype by updating your my.ini or my.cnf MySQL configuration file with the following setting:

max_allowed_packet=32M

Error Running a Report

If you can log into JasperReports Server but encounter an error when running a report within it, you can browse the repository to identify and resolve the problem.

One common problem with an individual report is the data source being used. To validate a data source connection:

If the connection fails, perhaps the application server cannot find the driver for the data source. For example, in a default installation of JasperReports Server, Tomcat looks for data source drivers in <js‑install>/apache-tomcat/lib.

Save Error with DB2 Database

When using the DB2 database as your repository database, it is possible to get errors when saving longer strings (such as over 50 characters) to data entry fields in the UI. An example would be saving a resource that has a name over 50 characters long. It is possible to get an error similar to the following:

Expected status code is 200, but was 400. Response body contained:

An unexpected exception has occurred

The problem, in this case, is that DB2 handles UTF-8 characters differently than other Jaspersoft certified databases. The database columns holding these strings need to be made larger. When DB2 is used as the repository database, there is a limitation on the number of characters that can be entered into UI fields.