1

I have MySQL schema with default charset utf8 and collation utf8_general_ci and in it I have mutiple tables/columns all with collation latin1_swedish_ci

I also have stored procedure that accepts VARCHAR as parameter pi_hashid. Based on this answer https://stackoverflow.com/a/21687188/1094300 I wanted to speed up the procedure execution by altering stored procedure to have

where d.hash_id = convert(pi_hashid using latin1) collate latin1_swedish_ci

but after alteration, I get the Exception for Illegal mix of collations, I altered procedure again (reverted changes) to

where d.hash_id=pi_hashid

but it still gives me the exception. Why I cannot revert changes, and is there another way to fix this without altering all tables/columns to have same collation as schema.

UPDATE: SHOW CREATE PROCEDURE company_get

company_get,,"CREATE DEFINER=`root`@`%` PROCEDURE `company_get`( IN pi_hashId varchar(50) )
BEGIN

SELECT
        ce.duns_number as duns
       ,ce.hash_id
       ,ce.company_name                 

           ,ce.Telephone_number as telephone
       ,ce.Facsimile_number as facsimile
       ,ce.Full_line_of_business_description as lob_des
       ,ce.Trading_Description as sic_lob_description
       ,concat(ce.US72_SIC1,ce.US72_SIC2,ce.US72_SIC3,ce.US72_SIC4,ce.US72_SIC5,ce.US72_SIC6) as us_sic
       ,ce.US72_SIC1
       ,ce.US72_SIC2
       ,ce.US72_SIC3
       ,ce.US72_SIC4
       ,ce.US72_SIC5
       ,ce.US72_SIC6


        ,case when cast(ce.Registration_number as decimal(10,0)) > 0
                    then concat( ce.Registration_number_prefix
                        ,lpad(cast(ce.Registration_number as decimal(10,0))
                            ,8 - length(ce.Registration_number_prefix)
                            ,'0')
                    ) 
                else ''
                end as Registration_number
       ,ce.Immediate_parent_DUNS_number as immediate_parent_DUNS
       ,ce.Immediate_parent_name 
       ,ce.Immediate_parent_country
       ,ce.Ultimate_parent_country 
       ,ce.Ultimate_parent_DUNS_number as Ultimate_parent_DUNS
       ,ce.Ultimate_parent_name 
       ,ce.Nominal_Capital
       ,ce.Issued_capital
       ,ce.Annual_Return_date
       ,ce.Pretax_Profit_1 as Pretax_Profit_1
       ,'' as pretax_loss_indicator
       ,ce.Year_Started
       ,ce.Date_Of_Incorporation as incorporation_date
       ,ce.Description_of_Brands as brands_description
       ,ce.Turnover_1
       ,ce.Net_Assets_1
       ,ce.Employees_Company_1 as no_employess1
       ,ce.Non_Audit_Fee_1
       ,ce.Auditors_Name_1 
       ,ce.Statement_date_2 as previous_statement_date
           ,c.major_sector_description
       ,c.sub_sector_description
       ,c.market_sector_description


           ,if(ce.Flag_to_indicate_branch_financials_populated_with_HQ_financials='Y',ce.Ultimate_parent_name,'')as hq_name
       ,ce.Net_Worth_1 as networth
       ,ce.Net_Profit_1  as profit
       ,ce.Annual_Local_Sales as sales
       ,ce.Year_Started as yr_start



       ,if ( (select paf_udprn from uf_dnb.companies where hash_id=sha(ce.Immediate_parent_DUNS_number))>0,sha(ce.Immediate_parent_DUNS_number),NULL  )   as immediate_parent_hash_id
       ,if ( (select paf_udprn from uf_dnb.companies where hash_id=sha(ce.Ultimate_parent_DUNS_number))>0,sha(ce.Ultimate_parent_DUNS_number),NULL  ) as ultimate_parent_hash_id
       ,if ( (select paf_udprn from uf_dnb.companies where hash_id=sha(ce.Ultimate_parent_DUNS_number))>0,sha(ce.Ultimate_parent_DUNS_number),NULL  )   as hq_hash_id

        ,ce.Gross_Profit_1
        ,ce.Operating_Profits_1
        ,ce.Total_Assets_1
        ,ce.Total_Liabilites_1
        ,ce.Working_Capital_1
        ,ce.Retained_Profits_1
        ,ce.Trading_Style_1 
       ,c.paf_udprn 
       ,c.paf_subbuild 
       ,c.paf_buildname 
       ,c.paf_buildno 
       ,c.paf_sub_street 
       ,c.paf_street 
       ,c.paf_sub_locality 
       ,c.paf_locality
       ,c.paf_post_town 
       ,c.paf_region
       ,c.paf_postcode 
       ,c.paf_postal_county 
       ,c.paf_area
       ,c.paf_district
       ,c.paf_gre
       ,c.paf_grn 

 FROM
           uf_dnb.companies_extended ce
           join uf_dnb.companies c
           on ce.hash_id=c.hash_id
   WHERE 
           ce.hash_id=pi_hashid;
END",utf8,utf8_general_ci,utf8_general_ci
Community
  • 1
  • 1
tibortru
  • 692
  • 5
  • 26
  • Please provide the relevant code. And check the `CHARACTER SET` of the Stored Procedure by doing `SHOW CREATE PROCEDURE ...`; there is an extra column that my surprise you. – Rick James Dec 22 '15 at 00:49
  • Now, let's see `SHOW CREATE TABLE` for the two tables (c, ce). I suspect one is declared utf8, one is latin1. If you are storing the output if `sha1` into the hex columns, then they may as well be `CHARACTER SET ascii`, since the value is hex. `utf8` is overkill for such. Or cut the size in half to `BINARY(20)`, then store `UNHEX(sha1(...))` into it. – Rick James Dec 22 '15 at 18:44
  • I added http://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql/34422593#34422593 so that this is now really a "duplicate" thread. – Rick James Dec 22 '15 at 18:53

0 Answers0