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 "<" '
wut?
Answers (1)
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.
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.
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? - Wildwolfay 11 years ago