Query Failed error appears in CAPC Dashboards instead of data

Document ID : KB000094859
Last Modified Date : 11/05/2018
Show Technical Document Details
Issue:
When viewing various dashboard views in CAPC,  "Query Failed" errors are generated instead of data:
 
Dashboard_View_Query_Failed 
Environment:
CAPM 3.x
Cause:
From the following errors in the Vertica logs, the problem is caused by the existence of duplicates in the poll_item table;
 
Caused by: java.sql.SQLIntegrityConstraintViolationException: [Vertica][VJDBC](3149) ERROR: Duplicate primary/unique key detected in join [(dauser.avail_rate x dauser.poll_item) using avail_rate_super_seg_b0 and poll_item_is_filtered_in_order_by_node0001 (PATH ID: 21)]; value [2343921]
 
These duplicates need to be deleted as they're causing conflict issues when CAPC queries the DB.
Resolution:
1. First login to the DR as the DR admin user and run the following to connect to the Vertica DB: 

/opt/vertica/bin/vsql -U <DA_USER_VERTICA_NAME> -w <DA_USER_PASSWORD

The user name and password will be what you setup for the DA to access the DR when you first installed it.  

2. At the SQL command prompt, run the following to identify any duplicates; 
 
SELECT item_id,count(*) FROM v_poll_item GROUP BY item_id HAVING count(*) > 1 ORDER BY 2 DESC LIMIT 10
 
If there are indeed duplicates, then you'll see output similar to; 

item_id  | count 
---------+------- 

2343914  |
(1 row) 

3. The item_id in the example above is 2343914, so using this, we can identify the poll_item; 

SELECT * FROM poll_item WHERE item_id=2343914

You should get output along the lines of or similar to (number of rows and values will obviously vary); 

item_id  | device_item_id | is_filtered
---------+----------------+-------------

2343914  | 5              | 0
2343918  | 5              | 0
2343914  | 137335         | 0
2343915  | 137335         | 0
2343916  | 137335         | 0
2343917  | 137335         | 0
2343918  | 137335         | 0
2343919  | 137335         | 0
2343920  | 137335         | 0
2343920  | 137335         | 0
2343921  | 137335         | 0
2343921  | 137335         | 0
2343922  | 137335         | 0
2343922  | 137335         | 0
2343923  | 137335         | 0
2343923  | 137335         | 0
2343916  | 1782025        | 0
2343915  | 2343915        | 0
2343919  | 2343919        | 0

4. If any values are returned, then these can then be deleted based on both the item_id and the device_item_id. The following example shows how this can be done:

WARNING: It is best to contact CA Support and get them to assist in this regards to avoid potentially corrupting the referential integrity of the DB

DELETE FROM poll_item WHERE item_id = ITEMID AND device_item_id = DEVICEITEMID;

So, in the above case, the device ID was 137335. So looking at our poll_item and device_item_ids together, we had;

SELECT * FROM poll_item WHERE item_id > 2343913 AND item_id < 2343924;

item_id  | device_item_id | is_filtered
---------+----------------+-------------
2343914  | 5              | 0
2343918  | 5              | 0
2343914  | 137335         | 0
2343915  | 137335         | 0
2343916  | 137335         | 0
2343917  | 137335         | 0
2343918  | 137335         | 0
2343919  | 137335         | 0
2343920  | 137335         | 0
2343920  | 137335         | 0
2343921  | 137335         | 0
2343921  | 137335         | 0
2343922  | 137335         | 0
2343922  | 137335         | 0
2343923  | 137335         | 0
2343923  | 137335         | 0
2343916  | 1782025        | 0
2343915  | 2343915        | 0
2343919  | 2343919        | 0

From the above output, there are four sets of duplicates, so we can eliminate them as follows;

DELETE FROM poll_item WHERE device_item_id137335 AND item_id IN (2343920 , 234392123439222343923 );