Exception creating Event partition due duplicate partition name
Article ID: 72257
Updated On:
Products
Issue/Introduction
The Partition Handling Task is not able to create a new partition in the reporting database.
The following error was found in the Tomcat log file:
jan 25, 2018 02:58:31 PM(INFO)Partition Handling Task started...
jan 25, 2018 02:58:31 PM(INFO)In getMissingPartitions method...
jan 25, 2018 02:58:31 PM(INFO) The last partition name is : EVENT_P88
jan 25, 2018 02:58:31 PM(INFO) We need to create 55 number of partition(s).
Jan 25, 2018 14:58:31.216 (PartitionHandler) (com.aprisma.spectrum.app.repmgr.common.PartitionHandler) - (ERROR) - Couldn't run the Partition Handler.
Caused by: java.sql.SQLException: SQLException while trying to find missing partitions. Errorcode: 0
at com.aprisma.spectrum.app.repmgr.common.PartitionHandler.createMissingPartitions(Unknown Source)
at com.aprisma.spectrum.app.repmgr.common.PartitionHandler.execute(Unknown Source)
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)
Caused by: java.sql.SQLException: SQLException while creating paritions. Errorcode: 1517
at com.aprisma.spectrum.app.repmgr.common.PartitionHandler.createPartitions(Unknown Source)
... 11 more
Caused by: java.sql.SQLException: Duplicate partition name EVENT_P89
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1648)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1567)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
... 12 more
Environment
CA Spectrum Any
Cause
NOTE: In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.
There are missing entries in the partition log table.
The Partition Handling Task is not able to create the next partition event table due it already exists.
It is trying to create the EVENT_P89 event table based on the last entry in the partitionlog table, but the last partition event table file is event_p105.ibd. This last partition event table is displayed in the following MySQL query:
- Log into the SRM system as the user that owns the SRM installation
- If on Windows, start a bash shell by running "bash -login"
- Enter the following command to log into mysq;
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting
- Enter the following command at the mysql prompt
show create table event;
| event | 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=18132569 DEFAULT CHARSET=utf8 MAX_ROWS=1000000000 AVG_ROW_LENGTH=500
/*!50500 PARTITION BY RANGE COLUMNS(`time`)
(PARTITION EVENT_P15 VALUES LESS THAN ('2016-05-22 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P16 VALUES LESS THAN ('2016-05-29 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P17 VALUES LESS THAN ('2016-06-05 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P18 VALUES LESS THAN ('2016-06-12 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P19 VALUES LESS THAN ('2016-06-19 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P20 VALUES LESS THAN ('2016-06-26 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P21 VALUES LESS THAN ('2016-07-03 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P22 VALUES LESS THAN ('2016-07-10 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P23 VALUES LESS THAN ('2016-07-17 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P24 VALUES LESS THAN ('2016-07-24 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P25 VALUES LESS THAN ('2016-07-31 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P26 VALUES LESS THAN ('2016-08-07 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P27 VALUES LESS THAN ('2016-08-14 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P28 VALUES LESS THAN ('2016-08-21 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P29 VALUES LESS THAN ('2016-08-28 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P30 VALUES LESS THAN ('2016-09-04 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P31 VALUES LESS THAN ('2016-09-11 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P32 VALUES LESS THAN ('2016-09-18 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P33 VALUES LESS THAN ('2016-09-25 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P34 VALUES LESS THAN ('2016-10-02 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P35 VALUES LESS THAN ('2016-10-09 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P36 VALUES LESS THAN ('2016-10-16 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P37 VALUES LESS THAN ('2016-10-23 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P38 VALUES LESS THAN ('2016-10-30 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P39 VALUES LESS THAN ('2016-11-06 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P40 VALUES LESS THAN ('2016-11-13 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P41 VALUES LESS THAN ('2016-11-20 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P42 VALUES LESS THAN ('2016-11-27 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P43 VALUES LESS THAN ('2016-12-04 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P44 VALUES LESS THAN ('2016-12-11 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P45 VALUES LESS THAN ('2016-12-18 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P46 VALUES LESS THAN ('2016-12-25 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P47 VALUES LESS THAN ('2017-01-01 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P48 VALUES LESS THAN ('2017-01-08 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P49 VALUES LESS THAN ('2017-01-15 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P50 VALUES LESS THAN ('2017-01-22 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P51 VALUES LESS THAN ('2017-01-29 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P52 VALUES LESS THAN ('2017-02-05 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P53 VALUES LESS THAN ('2017-02-12 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P54 VALUES LESS THAN ('2017-02-19 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P55 VALUES LESS THAN ('2017-02-26 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P56 VALUES LESS THAN ('2017-03-05 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P57 VALUES LESS THAN ('2017-03-12 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P58 VALUES LESS THAN ('2017-03-19 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P59 VALUES LESS THAN ('2017-03-26 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P60 VALUES LESS THAN ('2017-04-02 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P61 VALUES LESS THAN ('2017-04-09 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P62 VALUES LESS THAN ('2017-04-16 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P63 VALUES LESS THAN ('2017-04-23 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P64 VALUES LESS THAN ('2017-04-30 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P65 VALUES LESS THAN ('2017-05-07 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P66 VALUES LESS THAN ('2017-05-14 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P67 VALUES LESS THAN ('2017-05-21 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P68 VALUES LESS THAN ('2017-05-28 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P69 VALUES LESS THAN ('2017-06-04 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P70 VALUES LESS THAN ('2017-06-11 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P71 VALUES LESS THAN ('2017-06-18 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P72 VALUES LESS THAN ('2017-06-25 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P73 VALUES LESS THAN ('2017-07-02 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P74 VALUES LESS THAN ('2017-07-09 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P75 VALUES LESS THAN ('2017-07-16 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P76 VALUES LESS THAN ('2017-07-23 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P77 VALUES LESS THAN ('2017-07-30 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P78 VALUES LESS THAN ('2017-08-06 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P79 VALUES LESS THAN ('2017-08-13 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P80 VALUES LESS THAN ('2017-08-20 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P81 VALUES LESS THAN ('2017-08-27 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P82 VALUES LESS THAN ('2017-09-03 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P83 VALUES LESS THAN ('2017-09-10 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P84 VALUES LESS THAN ('2017-09-17 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P85 VALUES LESS THAN ('2017-09-24 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P86 VALUES LESS THAN ('2017-10-01 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P87 VALUES LESS THAN ('2017-10-08 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P88 VALUES LESS THAN ('2017-10-15 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P89 VALUES LESS THAN ('2017-10-22 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P90 VALUES LESS THAN ('2017-10-29 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P91 VALUES LESS THAN ('2017-11-05 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P92 VALUES LESS THAN ('2017-11-12 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P93 VALUES LESS THAN ('2017-11-19 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P94 VALUES LESS THAN ('2017-11-26 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P95 VALUES LESS THAN ('2017-12-03 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P96 VALUES LESS THAN ('2017-12-10 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P97 VALUES LESS THAN ('2017-12-17 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P98 VALUES LESS THAN ('2017-12-24 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P99 VALUES LESS THAN ('2017-12-31 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P100 VALUES LESS THAN ('2018-01-07 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P101 VALUES LESS THAN ('2018-01-14 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P102 VALUES LESS THAN ('2018-01-21 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P103 VALUES LESS THAN ('2018-01-28 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P104 VALUES LESS THAN ('2018-02-04 00:00:00') ENGINE = InnoDB,
PARTITION EVENT_P105 VALUES LESS THAN ('2018-02-11 00:00:00') ENGINE = InnoDB) */ |
List of files under the $SPECROOT/mysql/data/reporting directory:
19/01/2018 13:47 212.992 event#p#event_p79.ibd
19/01/2018 13:47 212.992 event#p#event_p80.ibd
19/01/2018 13:47 212.992 event#p#event_p81.ibd
19/01/2018 13:47 212.992 event#p#event_p82.ibd
19/01/2018 13:47 212.992 event#p#event_p83.ibd
19/01/2018 13:47 212.992 event#p#event_p84.ibd
19/01/2018 13:47 212.992 event#p#event_p85.ibd
19/01/2018 13:47 212.992 event#p#event_p86.ibd
19/01/2018 13:47 212.992 event#p#event_p87.ibd
19/01/2018 13:47 212.992 event#p#event_p88.ibd
19/01/2018 13:47 212.992 event#p#event_p89.ibd
19/01/2018 13:47 212.992 event#p#event_p90.ibd
19/01/2018 13:47 212.992 event#p#event_p91.ibd
19/01/2018 13:47 212.992 event#p#event_p92.ibd
19/01/2018 13:47 212.992 event#p#event_p93.ibd
19/01/2018 13:47 212.992 event#p#event_p94.ibd
19/01/2018 13:47 212.992 event#p#event_p95.ibd
20/01/2018 01:10 293.601.280 event#p#event_p96.ibd
20/01/2018 01:10 465.567.744 event#p#event_p97.ibd
20/01/2018 01:14 423.624.704 event#p#event_p98.ibd
20/01/2018 01:15 197.132.288 event#p#event_p99.ibd
19/01/2018 13:47 212.992 event#p#event_p100.ibd
20/01/2018 23:10 2.998.927.360 event#p#event_p101.ibd
27/01/2018 23:40 2.969.567.232 event#p#event_p102.ibd
03/02/2018 23:00 3.267.362.816 event#p#event_p103.ibd
10/02/2018 23:00 230.686.720 event#p#event_p104.ibd
18/02/2018 23:00 218.103.808 event#p#event_p105.ibd
In the partitionlog table, the last partition name is: EVENT_P88
mysql> SELECT * FROM partitionlog;
+--------------+------------+----------------+---------------------+---------------------+---------------------+
| partition_id | table_name | partition_name | values_less_than | creation_time | destroy_time |
+--------------+------------+----------------+---------------------+---------------------+---------------------+
| 1 | event | EVENT_P1 | 2015-06-14 00:00:00 | 2016-06-07 17:37:58 | 2016-06-14 15:10:39 |
| 2 | event | EVENT_P2 | 2015-06-21 00:00:00 | 2016-06-07 17:37:58 | 2016-06-21 14:10:26 |
| 3 | event | EVENT_P3 | 2015-06-28 00:00:00 | 2016-06-07 17:37:58 | 2016-06-28 03:49:34 |
.
.
| 78 | event | EVENT_P78 | 2016-12-04 00:00:00 | 2016-10-23 15:25:39 | NULL |
| 79 | event | EVENT_P79 | 2016-12-11 00:00:00 | 2016-10-30 15:25:39 | NULL |
| 80 | event | EVENT_P80 | 2016-12-18 00:00:00 | 2016-11-06 15:25:39 | NULL |
| 81 | event | EVENT_P81 | 2016-12-25 00:00:00 | 2016-11-13 15:25:39 | NULL |
| 82 | event | EVENT_P82 | 2017-01-01 00:00:00 | 2016-11-20 15:25:39 | NULL |
| 83 | event | EVENT_P83 | 2017-01-08 00:00:00 | 2016-11-27 15:25:39 | NULL |
| 84 | event | EVENT_P84 | 2017-01-15 00:00:00 | 2016-12-04 11:33:10 | NULL |
| 85 | event | EVENT_P85 | 2017-01-22 00:00:00 | 2016-12-11 11:33:09 | NULL |
| 86 | event | EVENT_P86 | 2017-01-29 00:00:00 | 2016-12-19 11:33:10 | NULL |
| 87 | event | EVENT_P87 | 2017-02-05 00:00:00 | 2016-12-25 11:33:09 | NULL |
| 88 | event | EVENT_P88 | 2017-02-12 00:00:00 | 2017-01-01 11:33:09 | NULL |
+--------------+------------+----------------+---------------------+---------------------+---------------------+
Resolution
NOTE: In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.
Please manually add the missing entries in the partitionlog table. From EVENT_P89 to EVENT_P105 in the above example. Yours will be different..
Note that you will need to make the appropriate changes in the MySQL syntax to fit your scenario.
- Log into the SRM system as the user that owns the SRM installation
- If on Windows, start a bash shell by running "bash -login"
- Enter the following command to log into mysql;
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting
- Run each line at a time at the mysql prompt for each missing
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P89','2017-10-22 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P90','2017-10-29 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P91','2017-11-05 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P92','2017-11-12 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P93','2017-11-19 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P94','2017-11-26 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P95','2017-12-03 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P96','2017-12-10 00:00:00','2018-01-20 01:10:29');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P97','2017-12-17 00:00:00','2018-01-20 01:10:46');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P98','2017-12-24 00:00:00','2018-01-20 01:14:42');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P99','2017-12-31 00:00:00','2018-01-20 01:15:19');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P100','2018-01-07 00:00:00','2018-01-19 13:47:21');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P101','2018-01-14 00:00:00','2018-01-20 11:10:29');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P102','2018-01-21 00:00:00','2018-01-27 11:40:29');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P103','2018-01-28 00:00:00','2018-02-03 11:00:03');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P104','2018-02-04 00:00:00','2018-02-10 11:00:23');
INSERT INTO partitionlog(table_name,partition_name,values_less_than,creation_time) values ('event','EVENT_P105','2018-02-11 00:00:00','2018-02-18 11:00:21');
Additional Information
The Partition Handling Task is not started when the Tomcat service is bounced.
If you need to trigger the Partition Handling Task after the Tomcat service restart, follow the article below:
https://knowledge.broadcom.com/external/article?articleId=6385
Feedback
