So I have had my ticket rules all add 3 lines at the end of the sql that check ASSET_DATA_20 for the it's values and they work as our work hours, that way ticket emails only work during work hours.

  where (S.NAME = 'Waiting for Ticket Owner' or S.NAME = 'Closure Requested')
and DATE_SUB(NOW(), INTERVAL 1 DAY) > DATE(HD_TICKET.MODIFIED)
and (HD_TICKET.HD_QUEUE_ID = 1 or HD_TICKET.HD_QUEUE_ID = 13 or HD_TICKET.HD_QUEUE_ID = 10 or HD_TICKET.HD_QUEUE_ID = 9 or HD_TICKET.HD_QUEUE_ID = 12)
and TIME(NOW())< ASSET_DATA_20.FIELD_140 and TIME(NOW())> ASSET_DATA_20.FIELD_138
and DAYOFWEEK(NOW()) = ASSET_DATA_20.FIELD_139

Since the upgrade to 5.5, this no longer works (magically).

The error I'm getting from KACE and from MYSQL is 'Illegal mix of collations for opertation "<" '

wut?

 

 

Answer Summary:
See answer below. Can't check TIME(NOW()) against a varchar(255) so had to make it a timestamp data type, which required a rule to update the timestamp asset on a daily basis.
Cancel
1 Comment   [ + ] Show Comment

Comments

  • changing the first two AND statements from FIELD_140 and FIELD_138 to '18:00:00' and '06:00:00' allows the ticket rule to run once more, but the entire point of calling upon the assets is so I dont have to perform this time maintenance on every single rule that I make.

    If the data within the asset_data_20 table is EXACTLY but I just typed into quotations, why won't it call upon it's own table data?
Please log in to comment

Answers

1

I had to establish a work around.  Assets were varchar(255) text and something changed to where TIME(NOW()) can no longer check against that, so had to change my TIME assets to time stamp.  Problem:  Needed a date in order for it to establish a full time stamp.

 

Second solution:  Made a daily rule that runs in the morning and update the assets with 'todays' date and keeps the time the same, so that has to run every morning in order for the rules that check the assets to be able to successfully check against the assets.

In short: I needed a work around for my work around to a work around.

Answered 09/24/2013 by: Wildwolfay
Red Belt

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