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.


Comments

Post a Comment

Popular posts from this blog

Load Balancing in Bluemix using custom domain and DNS SRV records -

-
i'm trying load balancing of application in bluemix using custom domain , srv record in dns manager. created own doamin ccbluemix.dynu.com , added custom domain bluemix. created application in region , created route custom domain myapp.ccbluemix.dynu.com deployed same application in uk region , created route custom domain myapp.ccbluemix.dynu.com there well. now whenever user hits route want load balancing between , uk region alternate requests served , uk regions. for created srv records like _http._tcp.ccbluemix.dynu.com srv eu-gb.mybluemix.net [priority: 0] [weight: 50] 10 _http._tcp.ccbluemix.dynu.com srv mybluemix.net [priority: 0] [weight: 50] 10 but nothing happening , i'm not able access application using custom route @ all. if create 'a' record cc-load-bal-app.ccbluemix.dynu.com 75.126.81.68 i can access app using custom route ends hitting server. how can achieve load balancing using srv records? wrong creating srv records here? i read ne...
Read more

oracle - pls-00402 alias required in select list of cursor to avoid duplicate column names -

-
my cursor : select xzis.msisdn msisdn, xzis.in_account_number in_account_number xxobw_zte_in_sync xzis not exists (select 1 hz_contact_points hcp, hz_cust_accounts hca hcp.owner_table_id = hca.party_id , hcp.owner_table_name = 'hz_parties' , hcp.phone_number = substr (xzis.msisdn, -8) , nvl (hca.attribute12, 'nil') = xzis.in_account_number); i have given alias columns, still facing issue. suggestions? it's cursor. 1 has no such problems. problem is, when you're joining 2 tables, or apply wrong aliases in subselect, cursor may @ end return 2 columns same name/alias. the problem not need alias, no 2 columns can have same alias (the name counting alias, when no alias given). apparently has happened, far can tell, it's ...
Read more

python - Consider setting $PYTHONHOME to <prefix>[:<exec_prefix>] error -

-
Image
i getting same error when python manage.py runserver. there no such error when have ubuntu 15.10. got started when upgraded ubuntu 16.04. question might duplicate have tried solution provided question have applied command dpkg --configure -a, have done apt-get update,upgrade,dist-upgrade, clean, -f install. have reinstalled python2.7 , python3 no success though. here screenshot while trying run django application i got after re-installing python please me? bashrc file # ~/.bashrc: executed bash(1) non-login shells. # see /usr/share/doc/bash/examples/startup-files (in package bash-doc) # examples # if not running interactively, don't case $- in *i*) ;; *) return;; esac export pythonhome="/usr/bin/python" echo $virtualenvwrapper_python echo 'tushant' # don't put duplicate lines or lines starting space in history. # see bash(1) more options histcontrol=ignoreboth # append history file, don't overwrite shopt -s histappend # setting h...
Read more