We upgraded our kbox over the weekend, 6.3 > 6.4. Still dealing with the fallout *sigh*.
One major issue we dealt with was query (update statement) in our ticketing that the newer version of MySQL didn't like. Unfortunately for me it's a ticket rule for routing and we have 50+ across all of our queues.  I did manage to fix it on the fly but the resolution is sloppy and inefficient, so I'd like to clean it up if at all possible.

The UPDATE that was failing began like this -

update HD_TICKET set HD_TICKET.OWNER_ID =
((select DISTINCT USER_ID from (select O.ID USER_ID, O.USER_NAME, (SELECT MAX(CREATED) FROM HD_TICKET WHERE OWNER_ID = O.ID AND CREATED > CURDATE() GROUP BY OWNER_ID) MAXC
 from HD_QUEUE Q . . .
 
To correct the issue I modified it to this -

update HD_TICKET set HD_TICKET.OWNER_ID =
((select DISTINCT USER_ID from (select O.ID USER_ID, O.USER_NAME, (SELECT MAX(CREATED) FROM (SELECT HD_TICKET.* FROM HD_TICKET) AS FIX WHERE OWNER_ID = O.ID AND CREATED > CURDATE() GROUP BY OWNER_ID) MAXC
from HD_QUEUE Q . . .

This change stopped the errors and tickets are routing appropriately but I do not like using SELECT * for, anything, really. Here is the page I found the solution on -
http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

Oddly enough, this is from 2006 but it helped me resolve the issue quickly (sort of) this morning.

Any suggestions would be appreciated, thank you.
3 Comments   [ + ] Show Comments

Comments

  • What exactly are you trying to accomplish with this rule?
  • The update assigns an Owner to a ticket selected from a pool of available owners. I only posted the problem code from the update statement because it's the piece that is relevant to the problem. We've been using this specific code since 5.2.
    • And do you want the selected owner to be random or the person with the fewest tickets? You're selecting MAX(CREATED) from tickets maybe? I'm trying to understand your statement so I can see if there is another way to come at the problem.
  • It's a random selection from the pool of available agents. There are additional conditions in the update that provide a way to exclude agents in a particular label, as in a OUT OF OFFICE label or what have you. I'm going to play with this a bit more. I'll keep you posted.
Please log in to comment

Answers

0
> (SELECT HD_TICKET.* FROM HD_TICKET)  
It seems there's no way around adding this sub-select, especially according to the link you posted. Maybe there's a more efficient fix but I don't know SQL well enough to speak to this exact problem. So AFAIK the only way to optimize the query would be to refine the sub-select you had to add. You can do this by (1) selecting only the specific columns the query needs and/or (2) including a WHERE clause. Sorry I don't have any other suggestions.

Answered 12/15/2015 by: JasonEgg
Fourth Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share