• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1448
  • Last Modified:

MySQL -- [nativecode=1271 ** Illegal mix of collations for operation 'concat']

I have a large query in which I'm getting this error.

The weird thing is that the error occurs when my PHP grid component passes the query, but not when I paste the query into the MySQL command line.

And all my tables have the same collation, though not all use the same engine.

mysql> Select TAble_Name, Table_Collation, Engine from TABLES Where TABLE_SCHEMA        = 'wb_dev';
+-----------------------+-------------------+--------+
| TAble_Name            | Table_Collation   | Engine |
+-----------------------+-------------------+--------+
| wt_CompanySettings    | latin1_swedish_ci | MyISAM |
| wt_Expectation        | latin1_swedish_ci | MyISAM |
| wt_ExperienceLevel    | latin1_swedish_ci | MyISAM |
| wt_Login              | latin1_swedish_ci | MyISAM |
| wt_PaymentAgreement   | latin1_swedish_ci | InnoDB |
| wt_PerformanceLevel   | latin1_swedish_ci | MyISAM |
| wt_Settings           | latin1_swedish_ci | MyISAM |
| wt_URCriterion        | latin1_swedish_ci | InnoDB |
| wt_UserHistory        | latin1_swedish_ci | InnoDB |
| wt_UserReport         | latin1_swedish_ci | InnoDB |
| wt_UserSettings       | latin1_swedish_ci | MyISAM |
| wt_bulletin_alerts    | latin1_swedish_ci | MyISAM |
| wt_collectors         | NULL              | NULL   |
| wt_monthlynumber      | latin1_swedish_ci | InnoDB |
| wt_paymenttypes       | latin1_swedish_ci | InnoDB |
| wt_postpaymententries | latin1_swedish_ci | InnoDB |
| wt_tbldata            | latin1_swedish_ci | InnoDB |
| wt_tblhistory         | latin1_swedish_ci | InnoDB |
| wt_teams              | latin1_swedish_ci | InnoDB |
| wt_usertyes           | latin1_swedish_ci | InnoDB |
+-----------------------+-------------------+--------+
20 rows in set (0.64 sec)

Any idea where I'm going wrong?

Thanks!
select 'Daily Production' as Category, 
  Concat('<div style="background-color:',Color,';">$' , Format(Value, 2),'</div>') as Value , 
  Concat('$' ,Format(Office , 2)) as Office , 
  Concat('$' ,Format(Amount, 2)) as Expectation , 
  0 as DARTME 
FROM (Select PL.Color, PL.Performance, Value, Office, X.Amount 
FROM ( Select sum( Case When U.userName = 'APAY' Then ppe.Amount else 0 End) / (17) as Value, 
sum(ppe.Amount) / (17) / 36 as Office 
from wt_postpaymententries ppe Inner Join wt_PaymentAgreement PA on ppe.AgreementID = PA.AgreementID Inner Join wt_collectors U on U.ID = PA.CollectorID 
WHERE Month(PA.EntryDate) = Month('2010-11-22') and Year(PA.EntryDate) = Year('2010-11-22') 
) V Inner Join wt_collectors U on U.UserName = 'APAY' INNER JOIN wt_Expectation X on X.Experience = U.Experience AND X.Category='Daily' INNER JOIN wt_PerformanceLevel PL on V.Value >= PL.MinPercent * X.Amount 
Order by PL.MinPercent desc Limit 1 )G

Open in new window

0
Daniel Wilson
Asked:
Daniel Wilson
  • 2
1 Solution
 
Daniel WilsonAuthor Commented:
Pulling the HTML out of the CONCAT ...
select 'Daily Production' as Category, Concat(Color,' $' , Format(Value, 2)) as Value ...
gives me a more explicit error message:
[nativecode=1270 ** Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_unicode_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE) for operation 'concat']
0
 
Daniel WilsonAuthor Commented:
Looks like CONVERT will get it for me:
select 'Daily Production' as Category, Concat(Convert(Color USING utf8),' $' , Format(Value, 2)) as Value
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now