mysql said #1270 illegal mix of collations with concat

Question

I'm importing my sql data when this error show up I search alot of websites to solved the problem but can't find one.

Query:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
SQL SECURITY DEFINER VIEW `viewtblacctg_si_open_detl` AS 
    select `b`.`CustCode` AS `CustCode`,`s`.`SINo` AS `SINo`,
            `s`.`dtSI_Detl` AS `dtSI_Detl`,`s`.`LN` AS `LN`,
            `s`.`Qty` AS `Qty`,`s`.`UOM` AS `UOM`,
            `s`.`ItemType` AS `ItemType`,`s`.`StockCode` AS `StockCode`,
            `s`.`ItemCode` AS `ItemCode`,`s`.`ItemDesc` AS `ItemDesc`,
            `s`.`Brand` AS `Brand`,`s`.`Unit_Price` AS `Unit_Price`,
            `s`.`Gross_Amt` AS `Gross_Amt`,`s`.`Disc_Amt` AS `Disc_Amt`,
            `s`.`VAT_Amt` AS `VAT_Amt`,`s`.`Net_Amt` AS `Net_Amt`,
            `s`.`Frm_DocPref` AS `Frm_DocPref`,`s`.`Frm_DocNo` AS `Frm_DocNo`,
            `s`.`SIStatus_Detl` AS `SIStatus_Detl` 
    from (`viewtblacctg_collection_balance` `b` 
          left join `tblacctg_si_detl` `s` on((concat(`b`.`Doc_Type`,`b`.`Doc_No`) = concat(_utf8'SI',`s`.`SINo`)))) 
    where (`s`.`Qty` <> 0);

error:#1270 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'concat'

Different collations actually cause an enormous number of issues in databases. If at all possible, change all the collations for all the char fields in all the tables to be the same. — neelsg

Sharif Ahmed Sharif Ahmed · Accepted Answer · 2014-07-15T08:23:52

please check your table's collations and try to use same collation.you'r doing concat operation with different collation which may cause the issue. double check your those table again and try to use same collation. Thanks

mysql said #1270 illegal mix of collations with concat

1 Answers