KACE Product Support Question
5.5 changed MYSQL operations? (Illegal mix of Collations for operation '<')
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 "<" '
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.