Basic (RR) routing rule is locking up the database after working for months.
In March I posted our Routing Rule in response to another question here - http://www.itninja.com/question/k1000-round-robin.
A couple of weeks ago we began to experience a system performance issue where page changes (ticket saves, new ticket so on) were taking 40 to 90 seconds to complete. Not a pleasant experience for the ticket agents or the business users. A week of triage on different components led us to the Round Robin ticket rule that has been in place since late last year.
In an effort to replace it we began testing with GillySpy’s RR rule (http://www.itninja.com/question/auto-assign-tickets-in-a-round-robin) which works flawlessly except I needed to narrow down the selection group by Label. I found another post which filled that need here (http://www.itninja.com/question/k1000-round-robin).
So I added two lines to GillySpy’s RR rule (works flawlessly) to capture only the users in the Label. This rule now hangs the system in the same manner as our existing RR rule does. I tested a little further and found this single JOIN (JOIN USER_LABEL_JT ULJ ON ULJ.USER_ID = O.ID) is spiking the CPU to 100% and sits there for the full 40 to 90 seconds and this JOIN is in our original RR rule as well.
Am I looking at database corruption?
Compilation from two Ninja forum posts - http://www.itninja.com/question/auto-assign-tickets-in-a-round-robin - GillySpy
and - http://www.itninja.com/question/k1000-round-robin - dchristian
select HD_TICKET.ID from HD_TICKET WHERE HD_TICKET.HD_QUEUE_ID=1 and HD_TICKET.CUSTOM_FIELD_VALUE2='No' and HD_TICKET.HD_STATUS_ID !=2 and (HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL) /* no owner yet */
UPDATE HD_TICKET set HD_TICKET.OWNER_ID = (select BATTERUP.ID from (select O.ID from HD_TICKET T JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID JOIN USER O ON O.ID=UL.USER_ID JOIN USER_LABEL_JT ULJ ON ULJ.USER_ID = O.ID - - HANGS WHEN THIS JOIN IS PRESENT JOIN LABEL L ON (L.ID = UL.LABEL_ID AND L.NAME = 'ticket owner label') -- add round robin label here LEFT JOIN (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID) T2 ON O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL ON OUTLABEL.ID=UL2.LABEL_ID and OUTLABEL.NAME='U-Out of office') OOO ON O.ID=OOO.USER_ID where OOO.USER_ID IS NULL and T.OWNER_ID=0 and T.ID=<TICKET_IDS> ORDER BY MAXC ASC, RAND() LIMIT 1 ) BATTERUP) WHERE HD_TICKET.ID =<TICKET_IDS>
Community Chosen Answer
I re-read your question and you said things have been working fine until recently. Are there any errors in the logs related to this ticket rule? Have you tried rebooting the K1000 with an extended database check?
There also may be a way to do the join statements a little differently that may help with the way this rule runs, I've been thinking about this since answering earlier but haven't had time to write it out.