Spectrum CABI JasperSoft AD-Hoc reports cannot be run
Article ID: 195806
Updated On:
Products
Issue/Introduction
User is unable to run Ad-Hoc Spectrum JasperSoft reports. The following error is seen:
Error Details
The server has encountered an error. Please excuse the inconvenience.
Error Message
An error occurred while creating the connection. Try again
Error Trace.
com.jaspersoft.jasperserver.api.JSException: jsexception.error.creating.connection Arguments: at
com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.createConection(JdbcDataSourceService.java:64)
....
Environment
NetOps Spectrum - Any version
CABI - JasperSoft Reports - Any version
Cause
The CABI Server does not have permissions to execute queries against the Spectrum MySQL database server:
The following error is seen in the CABI_HOME/apache-tomcat/webapps/jasperserver-pro/WEB-INF/logs/jasperserver.log
2020-07-16 17:45:54,156 ERROR errorPage_jsp,http-nio-443-exec-5:583 - stack trace of exception that redirected to errorPage.jsp
com.jaspersoft.jasperserver.api.JSException: jsexception.error.creating.connection
Arguments:
...
Caused by: java.sql.SQLException: Host '<CABI Server>' is not allowed to connect to this MySQL server
Testing the spectrum_ds data source in JasperSoft results in one of the following errors:
“Could not connect: Access denied for user 'CR_user'@'<CABI Server>' to database 'reporting'"
“Host '<CABI Server>' is not allowed to connect to this MySQL server”.
Resolution
NOTE: In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.
- Log into the Spectrum Report Manager (SRM) system as the user that owns the Spectrum installation
- If on Windows, start a bash shell by running "bash -login"
- cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD>
mysql> GRANT ALL PRIVILEGES ON reporting.* TO 'CR_user'@'<CABI Server>' IDENTIFIED BY '0n3cl1Ck';
mysql> GRANT ALL PRIVILEGES ON srmdbapi.* TO 'CR_user'@'<CABI Server>' IDENTIFIED BY '0n3cl1Ck';
mysql> FLUSH PRIVILEGES;
mysql> exit
NOTE: Replace <CABI Server> with the actual FQDN of the CABI Server.
After this try re-running the report
Additional Information
On Spectrum 22.2.2 onwards, MySQL has been upgraded to 8.x and the GRANT statement has been changed to the following:
mysql> GRANT ALL ON reporting.* TO 'CR_user'@'<CABI Server>';
mysql> GRANT ALL ON srmdbapi.* TO 'CR_user'@'<CABI Server>';
mysql> FLUSH PRIVILEGES;
mysql> exit
Note: Replace <CABI Server> with the actual FQDN of the CABI Server.
Note: You may need to create the CR_user account if it does not exist.
mysql> CREATE USER 'CR_user'@'<CABI Server>' IDENTIFIED BY '0n3cl1Ck';
If you run a syntax like this in MySQL 8.x, you will get the following error:
mysql> GRANT ALL ON *.* TO 'CR_user'@'%';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
There was a change in MySQL 8.x in regard to performance_schema db.
mysql> GRANT ALL ON performance_schema.* TO 'CR_user'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'performance_schema'
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-table-characteristics.html
27.11 Performance Schema General Table Characteristics
The name of the performance_schema database is lowercase, as are the names of tables within it. Queries should specify the names in lowercase.
Many tables in the performance_schema database are read only and cannot be modified:
mysql> TRUNCATE TABLE performance_schema.setup_instruments;
ERROR 1683 (HY000): Invalid performance_schema usage.Some of the setup tables have columns that can be modified to affect Performance Schema operation; some also permit rows to be inserted or deleted. Truncation is permitted to clear collected events, so TRUNCATE TABLE can be used on tables containing those kinds of information, such as tables named with a prefix of events_waits_.
Summary tables can be truncated with TRUNCATE TABLE. Generally, the effect is to reset the summary columns to 0 or NULL, not to remove rows. This enables you to clear collected values and restart aggregation. That might be useful, for example, after you have made a runtime configuration change. Exceptions to this truncation behavior are noted in individual summary table sections.
Privileges are as for other databases and tables:
Because only a limited set of privileges apply to Performance Schema tables, attempts to use GRANT ALL as shorthand for granting privileges at the database or table level fail with an error:
mysql> GRANT ALL ON performance_schema.*
TO 'u1'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost'
to database 'performance_schema'
mysql> GRANT ALL ON performance_schema.setup_instruments
TO 'u2'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost'
to database 'performance_schema'Instead, grant exactly the desired privileges:
mysql> GRANT SELECT ON performance_schema.*
TO 'u1'@'localhost';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT SELECT, UPDATE ON performance_schema.setup_instruments
TO 'u2'@'localhost';
Query OK, 0 rows affected (0.02 sec)Feedback
