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

Document ID : KB000115884
Last Modified Date : 26/09/2018
Show Technical Document Details
Issue:
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.
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