ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Missing out of the box Domains in CA Business Intelligence JasperReport Server r7.1 using MySQL DB

book

Article ID: 238003

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

After installed CA Business Intelligence JasperReport Server r7.1 and integrated with Spectrum 21.2.1, It seems the following out of the box Domains are missing from JasperReport Server Repository/Domain dashboard

 

We are using MySQL as the database instead of PostgreSQL

We tried re-integrating Jaspersoft and OneClick, restarted Apache-Tomcat and MySQL services.

We also ran "Re-Deploy" from OneClick Jasersoft Integration page, to no avail.

Cause

Investigation into CABI Tomcat log located at <CABI_ROOT>/apache-tomcat/logs/catalina.out, shows an exception on import: 

25-Mar-2022 15:40:04.724 INFO [main] org.apache.catalina.startup.Catalina.start Server startup in 59283 ms
2022-03-25T15:45:16,344 ERROR SqlExceptionHelper,pool-14-thread-1:131 - (conn=252) query size (4194304) is >= to max_allowed_packet (4194304)
2022-03-25T15:45:16,372 ERROR HibernateRepositoryServiceImpl,pool-14-thread-1:688 - ******** Recovering from template.lock/saveOrUpdate ***** could not execute statement; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute statement
2022-03-25T15:45:16,377 ERROR AssertionFailure,pool-14-thread-1:31 - HHH000099: an assertion failure occurred (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session): org.hibernate.AssertionFailure: null id in com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoFileResource entry (don't flush the Session after an exception occurs)
2022-03-25T15:45:16,387 ERROR ImportRunnable,pool-14-thread-1:81 - Import failed: com.jaspersoft.jasperserver.export.service.ImportFailedException: Import failed. Reason: null id in com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoFileResource entry (don't flush the Session after an exception occurs)

Environment

Release : 21.2

Component : Jaspersoft for CA Spectrum

Resolution

The clue is the note about packet size. Error mentions query size exceeds (or equal to) max_allowed_packet:

(conn=252) query size (4194304) is >= to max_allowed_packet (4194304)

As MySQL has been installed with default settings, the max_allowed_packet parameter is set too low to import the Spectrum Domains and Reports. 

1. Solve this by increasing max_allowed_packet value in etc/mysql/my.cnf

# The MySQL server
[mysqld]
port=3306
#socket=MySQL
basedir = c:/win32app/spectrum/mysql
tmpdir = c:/win32app/spectrum/mysql/tmp
datadir = c:/win32app/spectrum/mysql/data
#slow_query_log
#long_query_time = 10
default-storage-engine=MyISAM
#skip-innodb
character-set-server=utf8
collation-server=utf8_general_ci
max_allowed_packet=16M
secure_file_priv=  
sql-mode=

 

2. Increase to 256M:

max_allowed_packet=256M

 

3. Save the file, restart MySQL service

4. Run RE-DEPLOY from OneClick > Reporting > Jaspersoft Integration

 

You should see Domains and Reports now: 

Additional Information

Jaspersoft Communities: https://community.jaspersoft.com/jasperreports-server/issues/12116

Stack Overflow: https://stackoverflow.com/questions/47232486/error-packet-for-query-is-too-large-when-i-tried-to-make-a-query-on-my-website

Attachments