We are using the following stored procedure and all the mentioned tables are using "Collation = utf8_general_ci" Still we are getting this error:
Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
Store Procedure is:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AssignCallRates`()
begin
declare countrycode varchar(8000);
declare countryname varchar(8000);
declare currencycode varchar(8000);
declare priceval varchar(8000);
declare mobileprice varchar(8000);
declare landprice varchar(8000);
declare reccnt int;
DECLARE done INT DEFAULT FALSE;
declare country_cursor cursor for select country_code,country_name from dialoone_countries;
declare aud_cursor cursor for select convert(price,char) as price from tbl_rate_aud where quick_search=1 and trim(substring_index(`place`,'-',1)) = countryname LIMIT 0,2;
declare euro_cursor cursor for select convert(price,char) as price from tbl_rate_euro where quick_search=1 and trim(substring_index(`place`,'-',1)) = countryname LIMIT 0,2;
declare gbp_cursor cursor for select convert(price,char) as price from tbl_rate_gbp where quick_search=1 and trim(substring_index(`place`,'-',1)) = countryname LIMIT 0,2;
declare usd_cursor cursor for select convert(price,char) as price from tbl_rate_dollar where quick_search=1 and trim(substring_index(`place`,'-',1)) = countryname LIMIT 0,2;
declare continue handler for not found set done=TRUE;
truncate table tbl_rates;
open country_cursor;
CountryLOOP: loop
fetch country_cursor into countrycode,countryname;
if done=TRUE then
close country_cursor;
leave CountryLOOP;
end if;
set mobileprice = "";
set landprice="";
set reccnt = 0;
set priceval = "";
open aud_cursor;
AUDLOOP: loop
fetch aud_cursor into priceval;
if done = TRUE then
set done = FALSE;
close aud_cursor;
leave AUDLOOP;
end if;
set reccnt = reccnt + 1;
if reccnt = 1 then
set landprice=priceval;
end if;
if reccnt = 2 then
set mobileprice=priceval;
end if;
end loop AUDLOOP;
insert into tbl_rates (country_code,currency_code,mobile,land) values (countrycode,"AUD",mobileprice,landprice);
set mobileprice = "";
set landprice="";
set reccnt = 0;
set priceval = "";
open euro_cursor;
EUROLOOP: loop
fetch euro_cursor into priceval;
if done = TRUE then
set done = FALSE;
close euro_cursor;
leave EUROLOOP;
end if;
set reccnt = reccnt + 1;
if reccnt = 1 then
set landprice=priceval;
end if;
if reccnt = 2 then
set mobileprice=priceval;
end if;
end loop EUROLOOP;
insert into tbl_rates (country_code,currency_code,mobile,land) values (countrycode,"EUR",mobileprice,landprice);
set mobileprice = "";
set landprice="";
set reccnt = 0;
set priceval = "";
open gbp_cursor;
GBPLOOP: loop
fetch gbp_cursor into priceval;
if done = TRUE then
set done = FALSE;
close gbp_cursor;
leave GBPLOOP;
end if;
set reccnt = reccnt + 1;
if reccnt = 1 then
set landprice=priceval;
end if;
if reccnt = 2 then
set mobileprice=priceval;
end if;
end loop GBPLOOP;
insert into tbl_rates (country_code,currency_code,mobile,land) values (countrycode,"GBP",mobileprice,landprice);
set mobileprice = "";
set landprice="";
set reccnt = 0;
set priceval = "";
open usd_cursor;
USDLOOP: loop
fetch usd_cursor into priceval;
if done = TRUE then
set done = FALSE;
close usd_cursor;
leave USDLOOP;
end if;
set reccnt = reccnt + 1;
if reccnt = 1 then
set landprice=priceval;
end if;
if reccnt = 2 then
set mobileprice=priceval;
end if;
end loop USDLOOP;
insert into tbl_rates (country_code,currency_code,mobile,land) values (countrycode,"USD",mobileprice,landprice);
end loop CountryLOOP;
select "Query Executed Successfully";
end$$
DELIMITER ;
Any updation needed to this SP?
