Mismatch number of assets in the JasperReports

book

Article ID: 225266

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

The number of devices in the All Devices Global collection is different from the Assets reports in JasperReports.

Cause

The reason your reporting database is out of sync is because of the following message error:

Jul 22, 2021 06:26:47.033 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - Failed to insert event into event table.  SQL Exception code: 1526  SQL Exception message: Table has no partition for value from column_list  Last known event time = 1580018192000
Jul 22, 2021 06:26:47.034 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - event record (mh=0x1039ed, type=0x10017, time=2021-06-08 00:00:00.0)
Jul 22, 2021 06:26:47.635 (SRM/LandscapeManager/LandscapeThread_0) (SRM_Events) - (ERROR) - Database ERROR processing events for domain xxx-p-mls01.  Will not poll domain
Jul 22, 2021 06:28:50 AM(INFO)Partition Handling Task started...
Jul 22, 2021 06:28:50 AM(INFO)In getMissingPartitions method...
Jul 22, 2021 06:28:50.233 (PartitionHandler) (com.aprisma.spectrum.app.repmgr.common.PartitionHandler) - (ERROR) - Couldn't run the Partition Handler.
Caused by: java.lang.ArrayIndexOutOfBoundsException: 58
 at com.aprisma.spectrum.app.repmgr.common.PartitionHandler.createMissingPartitions(PartitionHandler.java:137)
 at com.aprisma.spectrum.app.repmgr.common.PartitionHandler.execute(PartitionHandler.java:63)
 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.util.MethodInvoker.invoke(MethodInvoker.java:269)
 at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:257)
 at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75)
 at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
 at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

The partitioned event table is far behing.

mysql> SELECT * FROM partitionlog;

+--------------+------------+----------------+---------------------+---------------------+---------------------+
| partition_id | table_name | partition_name | values_less_than    | creation_time       | destroy_time        |
+--------------+------------+----------------+---------------------+---------------------+---------------------+
.
.
.
|           92 | event      | EVENT_P92      | 2020-04-12 00:00:00 | 2020-03-01 18:45:10 | NULL                |
|           93 | event      | EVENT_P93      | 2020-04-19 00:00:00 | 2020-03-08 18:45:10 | NULL                |
|           94 | event      | EVENT_P94      | 2020-04-26 00:00:00 | 2020-03-15 16:27:44 | NULL                |
|           95 | event      | EVENT_P95      | 2020-05-03 00:00:00 | 2020-03-22 16:27:44 | NULL                |
+--------------+------------+----------------+---------------------+---------------------+---------------------+

Environment

Release : 10.3

Component : Spectrum Reporting

Resolution

The suggestion is to initialize the reporting database, drop the event table, truncate the partitionlog table, recreate the event table, and insert data into the partitionlog table.

Here are the detailed steps:

1. Stop the Spectrum Tomcat service

cd $SPECROOT/tomcat/bin/

./stopTomcat.sh

2. Initialize the reporting database

cd $SPECROOT/bin/

./RpmgrInitializeLandscape.bat root root -initHist 45 -all (Windows)

./RpmgrInitializeLandscape.sh root root -initHist 45 -all (Linux)

Ensure it ran successfully, no error message.

3. Connect to the MySQL command prompt.

cd $SPECROOT/mysql/bin/

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting -A (Linux)
./mysql.exe -uroot -proot reporting (Windows)

4. Drop the event table.

mysql> drop table event;

5. Truncate the partitionlog table.

mysql> truncate table partitionlog;

6. Recreate the event table.

CREATE TABLE `event` (
  `event_key` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` binary(26) NOT NULL,
  `landscape_h` int(10) unsigned NOT NULL,
  `model_key` int(10) unsigned NOT NULL DEFAULT '0',
  `time` datetime NOT NULL,
  `type` int(10) unsigned NOT NULL,
  `creator_id` int(10) unsigned NOT NULL,
  `event_msg` text,
  `server_precedence` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`event_key`,`time`),
  UNIQUE KEY `event_id_model_key_idx` (`event_id`,`model_key`,`time`),
  KEY `landscape_idx` (`landscape_h`),
  KEY `model_key_idx` (`model_key`),
  KEY `time_idx` (`time`),
  KEY `landscape_and_time_idx` (`landscape_h`,`time`),
  KEY `type_idx` (`type`),
  KEY `creator_id_idx` (`creator_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11818654 DEFAULT CHARSET=utf8 MAX_ROWS=1000000000 AVG_ROW_LENGTH=500
/*!50500 PARTITION BY RANGE  COLUMNS(`time`)
(PARTITION EVENT_P1 VALUES LESS THAN ('2021-08-15 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P2 VALUES LESS THAN ('2021-08-22 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P3 VALUES LESS THAN ('2021-08-29 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P4 VALUES LESS THAN ('2021-09-05 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P5 VALUES LESS THAN ('2021-09-12 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P6 VALUES LESS THAN ('2021-09-19 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P7 VALUES LESS THAN ('2021-09-26 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P8 VALUES LESS THAN ('2021-10-03 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P9 VALUES LESS THAN ('2021-10-10 00:00:00') ENGINE = InnoDB,
 PARTITION EVENT_P10 VALUES LESS THAN ('2021-10-17 00:00:00') ENGINE = InnoDB) */;

7. Insert data into the partitionlog table.

mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (1,'event','EVENT_P1','2021-08-15 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (2,'event','EVENT_P2','2021-08-22 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (3,'event','EVENT_P3','2021-08-29 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (4,'event','EVENT_P4','2021-09-05 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (5,'event','EVENT_P5','2021-09-12 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (6,'event','EVENT_P6','2021-09-19 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (7,'event','EVENT_P7','2021-09-26 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (8,'event','EVENT_P8','2021-10-03 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (9,'event','EVENT_P9','2021-10-10 00:00:00','2021-10-01 19:00:00',NULL);
mysql> insert into partitionlog (partition_id,table_name,partition_name,values_less_than,creation_time,destroy_time) values (10,'event','EVENT_P10','2021-10-17 00:00:00','2021-10-01 19:00:00',NULL);

8. Start the Spectrum Tomcat service.

cd $SPECROOT/tomcat/bin/

./startTomcat.sh

9. It may take from hours to days to repopulate the reporting database.

First SRM will populate the model table.

mysql> select count(*) from model;

Then SRM will populate the other tables, such as event.

mysql> select count(*) from event;

Run the following MySQL query the check the synchronization status:

mysql> select * from landscape\G

Attachments