Spectrum MySQL instance not processing queries after using events tab in OneClick Console

Products

• CA Spectrum

Issue/Introduction

After using the Events tab in OneClick it is noticed that queries are backing up in MySQL.
Using "SHOW FULL PROCESSLIST;" in the MySQL Client shows something similar to this query:

INFO: SELECT E.model_h, M.model_name, M.mtype_h, MT.mtype_name, E.utime, E.counter, E.clk_seq, E.version, E.node_id, U.user_name, E.type, E.severity, E.vardata_string FROM event as E, model as M, model_type as MT, user_def as U WHERE E.model_h = M.model_h AND M.mtype_h = MT.mtype_h AND E.user_key = U.user_key AND E.utime  >= 1533239885  ORDER BY E.utime ASC, E.counter  LIMIT 0 , 10000

The "Time" column will show a very large value, in seconds.

Environment

Release:
Component: SPCAEM

Cause

This is caused by requesting too much data from the OneClick Events tab.

Resolution

In versions prior to 10.3, this will have to be resolved manually by logging into MySQL and killing the queries.

1. Navigate to $SPECROOT/mysql/bin
2. Type: ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot
3. At the mysql> prompt type:

show full processlist\G

Each query seen here will have an ID.

4. Type:

kill <id>;

Note - Do NOT kill any queries that are not SELECT queries.  INSERT and DELETE queries must finish or there will be risk of data loss/inconsistencies.

On Spectrum 10.3 Oracle introduced a feature in MySQL 5.7.8+ to set a max statement time. (CA Spectrum 10.3 is running 5.7.22)

1. Navigate to $SPECROOT/mysql directory
2. Open my-spectrum.cnf file in a text editor.
3. At the bottom of the file add:

max_execution_time=<time in ms>

Example:

max_execution_time=60000

This will set it to 1 minute.
4. Restart the Spectrum MySQL service

This will automatically kill any read-only SELECT query hitting that limit.
 

Additional Information

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time