/build/static/layout/Breadcrumb_cap_w.png

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?

 

 


1 Comment   [ + ] Show comment
  • 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? - Wildwolfay 10 years ago

Answers (1)

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.
Posted by: Wildwolfay 10 years ago
Red Belt
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ