I am getting the error below when trying to do a select through a stored procedure in MySQL
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='.
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' or Variable_name LIKE '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 the tables are in utf8mb4_unicode_ci with the 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 INTO ROSTER_UPLOAD_SCHOOLS (
SELECT DISTINCT SCH.SchoolId, SCH.SchoolCode FROM school SCH
WHERE SCH.DistrictId = @DISTRICTID AND SCH.IsDeleted = 0
);
INSERT INTO ROSTER_UPLOAD_GRADES (
SELECT DISTINCT GRD.GRADEID, GRD.GRADENAME FROM grade GRD
);
insert ignore into 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 AND CLS.DISTRICTYEARID = @DISTRICTYEARID
INNER JOIN ROSTER_UPLOAD_SCHOOLS RUS ON CLS.SCHOOLID = RUS.SCHOOLID AND RCD.SCHOOLCODE = RUS.SCHOOLCODE
INNER JOIN rosterupload RU ON RU.ROSTERID = RCD.ROSTERID AND RU.ROSTERID = @JOBID AND RU.DISTRICTID = @DISTRICTID
SET CLS.ClassName = RCD.classname, CLS.ISDELETED = 0, CLS.UpdatedUser = RU.CreatedUser
WHERE RCD.CHUNKID = @CHUNKID;
SET CLASSESSEDITED = ROW_COUNT();
INSERT IGNORE INTO CLASS (SchoolId, DistrictYearId, ClassCode, ClassName, IsDeleted, CreatedUser) (
SELECT distinct RUS.SCHOOLID, @DISTRICTYEARID, RCD.ClassCode, RCD.CLASSNAME, 0, RU.CreatedUser
FROM ROSTERUPLOAD_CLASS_DETAILS RCD, ROSTER_UPLOAD_SCHOOLS RUS, rosterupload RU
WHERE (RCD.RosterId = @JOBID AND RCD.SchoolCode = RUS.SCHOOLCODE AND RCD.RosterId = RU.RosterId AND RCD.CHUNKID = @CHUNKID)
);
SET CLASSESADDED = ROW_COUNT();
UPDATE STUDENT STUD
INNER JOIN rosterupload_student_details RSD ON RSD.STUDENTCODE = STUD.STUDENTCODE AND STUD.DISTRICTID = @DISTRICTID
INNER JOIN rosterupload RU ON RU.ROSTERID = RSD.ROSTERID AND 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
WHERE RSD.STUDENTCODE = STUD.STUDENTCODE AND STUD.DISTRICTID = RU.DISTRICTID AND RU.DISTRICTID = @DISTRICTID
AND RSD.CHUNKID = @CHUNKID
AND RSD.STUDENTCODE NOT IN (SELECT RSD2.STUDENTCODE FROM rosterupload_student_details RSD2
WHERE RSD2.ROSTERID = @JOBID AND RSD2.CHUNKID < @CHUNKID);
SET STUDENTSEDITED = ROW_COUNT();
INSERT IGNORE INTO 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
FROM rosterupload_student_details RSD, rosterupload RU
WHERE RSD.RosterId = RU.RosterId AND RU.RosterId = @JOBID AND RSD.CHUNKID = @CHUNKID
);
SET STUDENTSADDED = ROW_COUNT();
INSERT INTO student_class_grade (StudentId, ClassId, DistrictYearId, GradeId, IsDeleted, CreatedUser) (
SELECT distinct STUD.StudentId, CLS.ClassId, @DISTRICTYEARID, RUG.GRADEID, 0, RU.CreatedUser
FROM rosterupload RU, rosterupload_student_details RSD, ROSTER_UPLOAD_SCHOOLS RUS, ROSTER_UPLOAD_GRADES RUG, STUDENT STUD, CLASS CLS
WHERE RU.ROSTERID = RSD.ROSTERID AND RSD.SCHOOLCODE = RUS.SCHOOLCODE AND RSD.GRADE = RUG.GRADENAME AND RSD.StudentCode = STUD.StudentCode
AND STUD.DistrictId = @DISTRICTID AND CLS.ClassCode = RSD.Classcode AND CLS.SchoolId = RUS.SCHOOLID AND CLS.DistrictYearId = @DISTRICTYEARID
AND 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
WHERE RTD.ROSTERID = @JOBID AND USR.DISTRICTID = @DISTRICTID AND USR.ROLEID = 5 AND RTD.CHUNKID = @CHUNKID;
SET TEACHERSEDITED = ROW_COUNT();
INSERT INTO `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
FROM rosterupload_teacher_details RTD
join ROSTER_UPLOAD_SCHOOLS RUS on RUS.SCHOOLCODE = RTD.schoolCode
join rosterupload RU on ru.rosterid = RTD.rosterid
WHERE NOT EXISTS (SELECT * FROM `USER` US
WHERE US.TeacherEmployeeCode = RTD.EmployeeCode AND US.DistrictId = @DISTRICTID AND US.ROLEID = 5)
AND RU.ROSTERID = @JOBID AND RU.DistrictId = @DISTRICTID AND 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
WHERE STAT.rosterid = @JOBID;
INSERT INTO teacher_school (UserId, SchoolId, DistrictYearId, StaffCode, IsDeleted, CreatedUser, UpdatedUser) (
SELECT distinct USR.USERID, RUS.SCHOOLID, @DISTRICTYEARID, RTD.StaffCode, 0, RU.CreatedUser, RU.CreatedUser
FROM 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
WHERE RU.ROSTERID = @JOBID
AND USR.DistrictId = @DISTRICTID
AND USR.RoleId = 5
AND RTD.CHUNKID = @CHUNKID
) ON DUPLICATE KEY UPDATE STAFFCODE = RTD.StaffCode, IsDeleted = 0, UpdatedUser = RU.CreatedUser;
INSERT INTO teacher_school_class (TeacherSchoolId,ClassId,IsDeleted,CreatedUser,UpdatedUser) (
SELECT distinct TS.TeacherSchoolId, CLS.ClassId, 0, RU.CreatedUser, RU.CreatedUser
FROM rosterupload_teacher_details RTD, ROSTER_UPLOAD_SCHOOLS RUS, `USER` USR, class CLS, teacher_school TS, rosterupload RU
WHERE RTD.ClassCode = CLS.ClassCode AND RTD.SchoolCode = RUS.SCHOOLCODE AND CLS.DistrictYearId = @DISTRICTYEARID
AND TS.UserId = USR.UserId AND TS.DistrictYearId = CLS.DistrictYearId AND TS.SchoolId = RUS.SchoolId
AND RTD.EmployeeCode = USR.TeacherEmployeeCode AND USR.RoleId = 5 AND USR.DistrictId = @DISTRICTID
AND RTD.RosterId = RU.RosterId AND RU.RosterId = @JOBID
) ON DUPLICATE KEY UPDATE IsDeleted = 0, UpdatedUser = RU.CreatedUser;
END
I see that you are using temporary tables too. Please define the same collation and character sets for temporary tables too. Use column-level collation in the SELECT query itself (also, where ever you put a join)
select * from some_table
where some_column collate
utf8_general_ci = 'Name of the City';
This should work for you.
Try to make databases and tables with same structure.
In my case I equal two db with same structure CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
, but one of them have tables with CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
and other one with CHARSET=utf8mb4
.
If you can't change the structure try with
some_column1 collate utf8_general_ci = some_column2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With