MySQL - illegal mix of collations (latin1_swedish_ci implicit) and (utf8_general_ci coercible for operation '='

Question

When I execute the query below i get this error:

Error Code: 1267. Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

If you need background behind this code, here is the explanation:
MySQL - How Can I Automate a View Query That Subtracts Metrics From the Most Recent Date To The Previous Day & Date Stamps The Most Recent Data

I set the 'facebook_insight' table collation to: 'utf8_general_ci,' but I still get the error. Can anyone help me?

MySQL Query:

CREATE VIEW `facebook_insights` AS  
SELECT  
  t1.id  
, t1.timestamp  
, t1.message  
, t1.posted  
, t1.permalink_url  
, t1.caption  
, t1.link  
, t1.type  
, t1.post_impressions - t2.post_impressions as Impressions  
, t1.post_impressions_organic - t2.post_impressions_organic as Post_Impressions_Organic  
, t1.post_impressions_paid - t2.post_impressions_paid as Post_Impressions_Paid  
, t1.post_engaged_users - t2.post_engaged_users as Post_Engaged_Users  
, t1.post_consumptions - t2.post_consumptions as Post_Consumptions  
, t1.post_negative_feedback - t2.post_negative_feedback as 
Post_Negative_Feedback  
, t1.post_negative_feedback_unique - t2.Post_Negative_Feedback_Unique as 
Post_Negative_Feedback_Unique  
, t1.post_impressions_fan - t2.post_impressions_fan as Post_Impressions_Fan  
, t1.post_impressions_fan_paid - t2.post_impressions_fan_paid as 
Post_Impressions_Fan_Paid  
, t1.post_engaged_fan - t2.Post_Engaged_Fan as Post_Engaged_Fan  
, t1.post_video_complete_views_organic - 
t2.post_video_complete_views_organic as Post_Video_Complete_Views_Organic  
, t1.post_video_complete_views_paid - t2.post_video_complete_views_paid as 
Post_Video_Complete_Views_Paid  
, t1.post_video_views_10s - t2.post_video_views_10s as Post_Video_Views_10s  
, t1.post_video_views_10s_unique - t2.post_video_views_10s_unique as 
Post_Video_Views_10s_Unique  
, t1.post_video_views_organic - t2.post_video_views_organic as 
Post_Video_Views_Organic  
, t1.post_video_views_paid - t2.post_video_views_paid as 
Post_Video_Views_Paid  
, t1.post_video_views_clicked_to_play - t2.post_video_views_clicked_to_play 
as Post_Video_Views_Clicked_to_Play  

FROM  
unpaid_media.facebook_insight t1  
JOIN unpaid_media.facebook_insight t2  
ON t1.id = t2.id   
and t1.timestamp  = t2.timestamp + INTERVAL 1 DAY

The collation issue isn't with the table. It's with the INTERVAL 1 DAY, you may have to change it's collation. What is the default collation for your DB? — Sloan Thrasher
@SloanThrasher , the default collation for my database is 'latin1 - default collation.' — Ulises Sotomayor

Kosh Kosh · Accepted Answer · 2017-07-06T20:41:23

As I can guess from your related question, the type of your timestamp column is not any of date, datetime or timestamp. It's probably varchar or text.

So when you try to add INTERVAL 1 DAY to the value which is not time type you get the error.

You have to convert your timestamp column to any of date, datetime or timestamp data types.

As a quick fix you can use STR_TO_DATE() function in your query:

CREATE VIEW `facebook_insights` AS
...
and STR_TO_DATE(t1.timestamp,'%m/%d/%Y') = STR_TO_DATE(t2.timestamp,'%m/%d/%Y') + INTERVAL 1 DAY

But better alter your table in order to have correct datatypes.

MySQL - illegal mix of collations (latin1_swedish_ci implicit) and (utf8_general_ci coercible for operation '='

1 Answers