Any dashboard views configured to display events are failing with error

Document ID : KB000048040
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Any views that tries to access Events (such as Event list and utilization trending with Events) are seeing a red bar that states:

Query failed. See the logs for details. (Query ID: 99e2ddc1-b40e-4618-9598-224b637726b7)

The logs in PCService.log has errors like the following:

ERROR | qtp41343992-23 | 2014-07-11 12:37:57,901 | com.ca.im.portal.plugins.rib.models.RIBTableModel | RIB query failed Model ID/Type:{101005/EventRIBTableModel} Result status: {FAILED} Query ID: {99e2ddc1-b40e-4618-9598-224b637726b7} RIB source URL: { http://capc:8281/EventManager/EventManagerRibWS.asmx} Query: {SELECT .EventId, .Item.itemID, .OccurredOn, .Item.itemName, .Item.ItemTypeName, .Item.ItemSubTypeName, .EventType.TypeName, .EventSubType.SubTypeName, .Description, .Device.DeviceName FROM CA.Events.Events WHERE .OccurredOn > 1405096620 AND .OccurredOn < 1405100220 AND .Item.ItemID = 9823 AND .Item.itemName LIKE '%%' OR .Item.ItemTypeName LIKE '%%' OR .Item.ItemSubTypeName LIKE '%%' OR .EventType.TypeName LIKE '%%' OR .EventSubType.SubTypeName LIKE '%%' OR .Description LIKE '%%' OR .Device.DeviceName LIKE '%%' AND (.Locale = 'en_US') AND (.Culture = 'en-US') ORDERBY .OccurredOn DESC LIMIT 101} Reason: {SQL error evaluating query}

The mysql err log file contains entries like the following:

2014-07-10 10:28:26 11374 [ERROR] /opt/CA/MySql/bin/mysqld: Table './em/events#P#p20140710' is marked as crashed and last (automatic?) repair failed

The EMService log shows the same errors:

INFO | qtp1314763383-27 | 2014-07-11 13:26:43,527 | org.apache.cxf.binding.soap.interceptor.Soap12FaultOutInterceptor | class org.apache.cxf.binding.soap.interceptor.Soap12FaultOutInterceptor$Soap12Faul tOutInterceptorInternalapplication/soap+xml WARN | qtp1314763383-25 | 2014-07-11 13:27:38,770 | org.apache.cxf.phase.PhaseInterceptorChain | Application {

http://netqos.com/EventManagerWS}EventManagerWS#{http://netqos.com/nqevents /EventManager}GetEventListOrUpdatesWithAllProperties has thrown exception, unwinding now org.apache.cxf.interceptor.Fault: StatementCallback; uncategorized SQLException for SQL [SELECT STRAIGHT_JOIN DISTINCT e.NPCEventID, ds.ConsoleName Source, et.TypeName Type, et.SubTypeName SubType, e.Category Category, e.OccurredOn, e.State, IFNULL(el10n.Description, e.description) as Description, i.ItemName, ei2.NPCItemID, i.ItemTypeName, IFNULL(i.ItemSubType Name,'') AS ItemSubTypeName FROM events e FORCE INDEX (PRIMARY, Events_OccurredOn, Events_ReceivedOn) INNER JOIN event_items ei ON e.NPCEventID = ei.NPCEventID AND e.OccurredOn = ei.OccurredOn LEFT OUTER JOIN events_l10n el10n ON e.NPCEventId = el10n.NPCEventId and el10n.Locale='en_US' INNER JOIN item_members im on (im.ChildID = ei.NPCItemID) INNER JOIN group_scope gs on (im.ParentID=gs.DescendantID) INNER JOIN event_items ei2 ON e.NPCEventID = ei2.NPCEventID AND e.OccurredOn = ei2.OccurredOn AND ei2.ItemIndex=0 INNER JOIN data_sources ds ON e.ProducerID = ds.SourceID INNER JOIN event_types et ON et.TypeID=e.SubTypeID INNER JOIN items i ON i.ItemID=ei2.NPCItemID WHERE gs.AncestorID=370511 AND e.ReceivedOn >= 1405103198 AND e.ReceivedOn < 1405103258 AND e.OccurredOn >= 1405103198-43200 AND e.OccurredOn < 1405103258 AND (e.TypeID IN ('6')) ORDER BY OccurredOn ASC]; SQL state [HY000]; error code [144]; Table './em/events#P#p20140710' is marked as crashed and last (automatic?) repair failed; nested exception is java.sql.SQLException: Table './em/events#P#p20140710' is marked as crashed and last (automatic?) repair failed

Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT STRAIGHT_JOIN DISTINCT e.NPCEventID, ds.ConsoleName Source, et.TypeName Type, et.SubTypeName SubType, e.Category Category, e.OccurredOn, e.State, IFNULL(el10n.Description, e.description) as Description, i.ItemName, ei2.NPCItemID, i.ItemTypeName, IFNULL(i.ItemSubType Name,'') AS ItemSubTypeName FROM events e FORCE INDEX (PRIMARY, Events_OccurredOn, Events_ReceivedOn) INNER JOIN event_items ei ON e.NPCEventID = ei.NPCEventID AND e.OccurredOn = ei.OccurredOn LEFT OUTER JOIN events_l10n el10n ON e.NPCEventId = el10n.NPCEventId and el10n.Locale='en_US' INNER JOIN item_members im on (im.ChildID = ei.NPCItemID) INNER JOIN group_scope gs on (im.ParentID=gs.DescendantID) INNER JOIN event_items ei2 ON e.NPCEventID = ei2.NPCEventID AND e.OccurredOn = ei2.OccurredOn AND ei2.ItemIndex=0 INNER JOIN data_sources ds ON e.ProducerID = ds.SourceID INNER JOIN event_types et ON et.TypeID=e.SubTypeID INNER JOIN items i ON i.ItemID=ei2.NPCItemID WHERE gs.AncestorID=370511 AND e.ReceivedOn >= 1405103198 AND e.ReceivedOn < 1405103258 AND e.OccurredOn >= 1405103198-43200 AND e.OccurredOn < 1405103258 AND (e.TypeID IN ('6')) ORDER BY OccurredOn ASC]; SQL state [HY000]; error code [144]; Table './em/events#P#p20140710' is marked as crashed and last (automatic?) repair failed; nested exception is java.sql.SQLException: Table './em/events#P#p20140710' is marked as crashed and last (automatic?) repair failed Caused by: java.sql.SQLException: Table './em/events#P#p20140710' is marked as crashed and last (automatic?) repair failed

Solution:

On the Performance Center server, do the following:

  1. Stop all the capc processes (sso, eventmanager,devicemanager, console).
  2. Stop, then restart mysql (/etc/init.d/mysql stop, then /etc/init.d/mysql start) to clear any db locks.
  3. At the command prompt, run the mysql client: mysql
  4. At the mysql> prompt, switch to the em database: use em;
  5. Check the table and let me know the results: check table events; it should display something like:
    +-------------------------------------------------------- --------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+-------+----------+--------------------------------------------------------------------+| em.events | check | Error | Incorrect key file for table '.\em\events#P#p20140626'; try to repair it || em.events | check | Error | Incorrect key file for table 'events'; try to repair it | | em.events | check | error | Corrupt | +-----------+-------+----------+---------------+ 
  6. Try to repair the table:
    repair table events;

    It should hopefully repair the table. If so, restart the caperfcenter_* services in the following order (sso, eventmanager,devicemanager, console). If not, note what it says and open a case with CA Technical Support.