Exception creating Event partition due duplicate partition name

Document ID : KB000072257
Last Modified Date : 27/02/2018
Show Technical Document Details
Issue:
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 10.x
Cause:
There are missing entries in the partitionlog 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:

mysql> 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:
Please manually add the missing entries in the partitionlog table. From EVENT_P89 to EVENT_P105.
Note that you will need to make the appropriate changes in the MySQL syntax to fit your scenario.

1. Open a bash shell (bash -login)

2. Navigate to the $SPECROOT/mysq/bin/ directory

3. Launch the MySQL command prompt:
./mysql -uroot -proot ddmdb (Windows)
OR
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot ddmdb (Linux/Solaris)

4. Run each line at a time:

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://comm.support.ca.com/kb/event-partition-creation-is-not-being-triggered/kb000006385