Pivotal Knowledge Base

Follow

No Partition for The Partitioning Key ,Gives Error While "insert into emcconnect_history select * from _emcconnect_tail;"

Environment

Pivotal Greenplum (GPDB) on DCA hardware

Symptom

You can find the error in the master log regarding missing partitioning key for an "emcconnect_history" table when inserting data from "_emcconnect_tail".

Error Message:

2018-01-16 04:16:58.480682 PST,"gpmon","gpperfmon",p47906,th-1921259744,"[local]",,2018-01-16 04:16:37 PST,267147683,con3303450,cmd2,seg-1,,dx13793446,x267147683,sx1,"ERROR","22M01","no partition for partitioning key (seg39 sdw5.gphd.local:40007 pid=312495)",,,,,,"insert into emcconnect_history select * from _emcconnect_tail;",0,,"cdbdisp.c",1322, 

Cause

The "emcconnect_history" table displays information about ConnectEMC events and alerts. This table is pre-partitioned into monthly partitions. Partitions are automatically added in the one-month increments as needed. Administrators must drop old partitions for the months that are no longer needed. If administrator drops a partition and there are still unprocessed errors for that period, you will get an error as mentioned above.

Resolution

There are 2 ways to fix this issue:

A. If you want to keep the old data, you have to add a missing partition to the "emcconnect_history" table

1. Check existing partitions in "emcconnect_history"

psql gpperfmon -c "SELECT partitiontablename,partitionboundary from pg_partitions where tablename='emcconnect_history'"
partitiontablename | partitionboundary --------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------
emcconnect_history_1_prt_1 | START ('2010-01-01 00:00:00'::timestamp without time zone) END ('2010-02-01 00:00:00'::timestamp without time zone) EVERY ('1 mon'::inter
val)
emcconnect_history_1_prt_r1820368615 | START ('2015-09-01 00:00:00'::timestamp without time zone) END ('2015-10-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r1697446024 | START ('2015-10-01 00:00:00'::timestamp without time zone) END ('2015-11-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r1544897591 | START ('2015-11-01 00:00:00'::timestamp without time zone) END ('2015-12-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r709382015 | START ('2015-12-01 00:00:00'::timestamp without time zone) END ('2016-01-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r1216271574 | START ('2016-01-01 00:00:00'::timestamp without time zone) END ('2016-02-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r2107417173 | START ('2016-02-01 00:00:00'::timestamp without time zone) END ('2016-03-01 00:00:00'::timestamp without time zone)
(7 rows) 

 2. Check entries date range from "_emcconnect_tail" file

psql gpperfmon -c "select date_trunc('month',ctime) as month from _emcconnect_tail group by month order by month"
month
---------------------
2015-08-01 00:00:00
2015-09-01 00:00:00
2015-10-01 00:00:00
2015-11-01 00:00:00
2015-12-01 00:00:00
2016-01-01 00:00:00
(6 rows)

3. From the above example, we can see that there are entries in "_emcconnect_tail" table for the  2015-08-01 period and "emcconnect_history" partition range starts from 2015-09-01. In order to fix that, you need to add the missing partition for the 2015-08-01 range that ends on 2015-09-01 (2015-09-01 is the start of the emcconnect_history_1_prt_r1820368615 partition).

psql gpperfmon
ALTER TABLE emcconnect_history ADD PARTITION START ('2015-08-01 00:00:00'::timestamp without time zone) END ('2015-09-01 00:00:00'::timestamp without time zone) ;

B. If you don't need the old data, remove the entries from the "_emcconnect_tail.dat" file. 

psql gpperfmon -c "\d _emcconnect_tail" | grep Command
Command: cat gpperfmon/data/_emcconnect_tail.dat 2> /dev/null || true

By default the file is located in $MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat

Take a backup of the file and remove the old entries from the 2015-08 period:

cp $MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat /tmp/_emcconnect_tail.dat.bkp
sed '/^2015-08/d' <$MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat >$MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat.new
mv $MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat.new $MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat

To verify if the problem is fixed, run the following:

psql gpperfmon -c "insert into emcconnect_history select * from _emcconnect_tail"

 

Comments

Powered by Zendesk