java - Getting error Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' -

i getting error below when trying select through stored procedure in mysql

illegal mix of collations (utf8mb4_unicode_ci,implicit) , (utf8mb4_general_ci,implicit) operation '='.

show variables variable_name 'character\_set\_%'   or variable_name 'collation%';      'character_set_client',     'utf8'     'character_set_connection', 'utf8'     'character_set_database',   'utf8mb4'     'character_set_filesystem', 'binary'     'character_set_results',    'utf8'     'character_set_server',     'utf8mb4'     'character_set_system',     'utf8'     'collation_connection',     'utf8_general_ci'     'collation_database',       'utf8mb4_unicode_ci'     'collation_server',         'utf8mb4_unicode_ci' 

all tables in utf8mb4_unicode_ci character set utf8mb4.

stored procedure code:

create definer=`dev_user`@`%` procedure `upload_roster_for_district`(in jobid int, in chunkid int, in districtid int, in districtyearid int) begin   declare studentsadded int default 0;  declare classesadded int default 0;  declare teachersadded int default 0;  declare studentsedited int default 0;  declare classessedited int default 0;  declare teachersedited int default 0;    set @jobid = jobid; set @districtid = districtid;  set @districtyearid = districtyearid;  set @chunkid = chunkid;  drop temporary table if exists roster_upload_schools;  drop temporary table if exists roster_upload_grades;   create temporary table roster_upload_schools (    schoolid int not null,    schoolcode varchar(50) not null,    primary key (schoolid) );  create temporary table roster_upload_grades (    gradeid int not null,    gradename varchar(50) not null,    primary key (gradeid) );  insert roster_upload_schools (   select distinct sch.schoolid, sch.schoolcode school sch    sch.districtid = @districtid , sch.isdeleted = 0  );  insert roster_upload_grades (   select distinct grd.gradeid, grd.gradename grade grd  );   insert ignore rosterstatistic (rosterid,classesadded,classesmodified,teachersadded,teachersmodified,studentsadded,studentsmodified,     totalprocessedrows,totalrows,createduser,updateduser) values (   @jobid, 0,0,0,0,0,0,0,0,null,null );  update class cls    inner join rosterupload_class_details rcd on cls.classcode = rcd.classcode , cls.districtyearid = @districtyearid    inner join roster_upload_schools rus on cls.schoolid = rus.schoolid , rcd.schoolcode = rus.schoolcode    inner join rosterupload ru on ru.rosterid = rcd.rosterid , ru.rosterid = @jobid , ru.districtid = @districtid   set cls.classname = rcd.classname, cls.isdeleted = 0, cls.updateduser = ru.createduser   rcd.chunkid = @chunkid;  set classessedited = row_count();     insert ignore class (schoolid, districtyearid, classcode, classname, isdeleted, createduser) (   select distinct rus.schoolid, @districtyearid, rcd.classcode, rcd.classname, 0, ru.createduser     rosterupload_class_details rcd, roster_upload_schools rus, rosterupload ru    (rcd.rosterid = @jobid , rcd.schoolcode = rus.schoolcode , rcd.rosterid = ru.rosterid , rcd.chunkid = @chunkid) );   set classesadded = row_count();    update student stud  inner join rosterupload_student_details rsd on rsd.studentcode = stud.studentcode , stud.districtid = @districtid  inner join rosterupload ru on ru.rosterid = rsd.rosterid , ru.rosterid = @jobid  set stud.firstname = rsd.firstname , stud.middlename = rsd.middlename, stud.lastname = rsd.lastname, stud.gender = rsd.gender,          stud.homeroom = rsd.homeroom, stud.birthdate = rsd.birthdate, stud.iepstatus = rsd.iepstatus, stud.lepstatus = rsd.lepstatus,          stud.lepyear = rsd.lepyear, stud.race = rsd.race, stud.language = rsd.language, stud.accommodation = rsd.accomodation, stud.reservedfield = rsd.reserved,          stud.isdeleted = 0, stud.updateduser = ru.createduser rsd.studentcode = stud.studentcode , stud.districtid = ru.districtid , ru.districtid = @districtid     , rsd.chunkid = @chunkid      , rsd.studentcode not in (select rsd2.studentcode rosterupload_student_details rsd2      rsd2.rosterid = @jobid , rsd2.chunkid < @chunkid);       set studentsedited = row_count();    insert ignore student ( districtid, studentcode, firstname, middlename, lastname, gender, homeroom,              birthdate, iepstatus, lepstatus, lepyear, race, language, accommodation, reservedfield,              md5hash, isdeleted, createduser) (    select distinct @districtid, studentcode, firstname, middlename, lastname, gender, homeroom,            birthdate, iepstatus, lepstatus, lepyear, race, language, accomodation, reserved, '', 0, ru.createduser    rosterupload_student_details rsd, rosterupload ru   rsd.rosterid = ru.rosterid , ru.rosterid = @jobid , rsd.chunkid = @chunkid );   set studentsadded = row_count();    insert student_class_grade (studentid, classid, districtyearid, gradeid, isdeleted, createduser) (   select distinct stud.studentid, cls.classid, @districtyearid, rug.gradeid, 0, ru.createduser    rosterupload ru, rosterupload_student_details rsd, roster_upload_schools rus, roster_upload_grades rug, student stud, class cls      ru.rosterid = rsd.rosterid ,  rsd.schoolcode = rus.schoolcode , rsd.grade = rug.gradename , rsd.studentcode = stud.studentcode    , stud.districtid = @districtid , cls.classcode = rsd.classcode , cls.schoolid = rus.schoolid , cls.districtyearid = @districtyearid    , ru.rosterid = @jobid  )   on duplicate key update gradeid = rug.gradeid, isdeleted = 0, updateduser = ru.createduser;  update `user` usr  inner join rosterupload_teacher_details rtd on rtd.employeecode = usr.teacheremployeecode  set usr.title = rtd.title, usr.firstname = rtd.firstname, usr.lastname = rtd.lastname, usr.middlename = rtd.middlename  rtd.rosterid = @jobid , usr.districtid = @districtid , usr.roleid = 5 , rtd.chunkid = @chunkid;   set teachersedited = row_count();    insert `user` (teacheremployeecode, districtid, roleid, firstname, middlename, lastname, emailaddress, title, createduser, `uuid`, activationemailpending ) (   select distinct rtd.employeecode, @districtid, 5, rtd.firstname, rtd.middlename, rtd.lastname, rtd.emailaddress, rtd.title, ru.createduser, rtd.`uuid`, true    rosterupload_teacher_details rtd   join roster_upload_schools rus on rus.schoolcode = rtd.schoolcode    join rosterupload ru on ru.rosterid = rtd.rosterid   not exists (select * `user`           us.teacheremployeecode = rtd.employeecode , us.districtid = @districtid , us.roleid = 5)    , ru.rosterid = @jobid , ru.districtid = @districtid , rtd.chunkid = @chunkid );  set teachersadded = row_count();   update rosterstatistic stat  set stat.classesadded = stat.classesadded + classesadded,      stat.classesmodified = stat.classesmodified + classessedited,      stat.teachersadded = stat.teachersadded + teachersadded,      stat.teachersmodified = stat.teachersmodified + teachersedited,      stat.studentsadded = stat.studentsadded + studentsadded,      stat.studentsmodified =  stat.studentsmodified + studentsedited stat.rosterid = @jobid;   insert teacher_school (userid, schoolid, districtyearid, staffcode, isdeleted, createduser, updateduser) (   select distinct usr.userid, rus.schoolid, @districtyearid, rtd.staffcode, 0, ru.createduser, ru.createduser      rosterupload_teacher_details rtd   join roster_upload_schools rus on rus.schoolcode = rtd.schoolcode   join rosterupload ru on ru.rosterid = rtd.rosterid   join `user` usr on usr.teacheremployeecode = rtd.employeecode   ru.rosterid = @jobid      , usr.districtid = @districtid      , usr.roleid = 5      , rtd.chunkid = @chunkid ) on duplicate key update staffcode = rtd.staffcode, isdeleted = 0, updateduser = ru.createduser;     insert teacher_school_class (teacherschoolid,classid,isdeleted,createduser,updateduser) (   select distinct ts.teacherschoolid, cls.classid, 0, ru.createduser, ru.createduser   rosterupload_teacher_details rtd, roster_upload_schools rus, `user` usr, class cls, teacher_school ts, rosterupload ru     rtd.classcode = cls.classcode , rtd.schoolcode = rus.schoolcode , cls.districtyearid = @districtyearid      , ts.userid = usr.userid , ts.districtyearid = cls.districtyearid , ts.schoolid = rus.schoolid      , rtd.employeecode = usr.teacheremployeecode , usr.roleid = 5 , usr.districtid = @districtid      , rtd.rosterid = ru.rosterid , ru.rosterid = @jobid  ) on duplicate key update isdeleted = 0, updateduser = ru.createduser;    end 

i see using temporary tables too. please define same collation , character sets temporary tables too. use column-level collation in select query (also, ever put join)

select * some_table some_column collate  utf8_general_ci = 'name of city'; 

this should work you.