Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/03/2024 11:24 AM
Receiving an error:
Error in Users Import - Error while processing data: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
This error seems to stem from the FN_EIC_REPLACE lines in the preprocessor query as when they are removed the import is functioning:
{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey, username, statuskey, firstname, lastname, middlename, termdate, comments, systemusername from USERS"
},
"COMPUTEDCOLUMNS": [
"systemusername",
"statuskey",
"customproperty27",
"customproperty28",
"customproperty29",
"customproperty30",
"customproperty31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY27 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY27,null)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY28 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY28,null)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY29 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY29,null)",
"UPDATE NEWUSERDATA INNER JOIN currentusers ON currentusers.username = newuserdata.username SET newuserdata.statuskey = 0 WHERE currentusers.comments LIKE 'User Terminated By%' AND (DATE_FORMAT(currentusers.termdate, '%Y-%m-%d') >= (DATE_FORMAT(Curdate(), '%Y-%m-%d') - INTERVAL 1 DAY))",
"UPDATE NEWUSERDATA INNER JOIN currentusers ON currentusers.username = newuserdata.username SET newuserdata.systemusername = CASE WHEN newuserdata.firstname != currentusers.firstname THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) WHEN newuserdata.lastname != currentusers.lastname THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) ELSE currentusers.systemusername END",
"UPDATE NEWUSERDATA SET newuserdata.customproperty30='N'",
"UPDATE NEWUSERDATA INNER JOIN currentusers ON currentusers.username = newuserdata.username SET newuserdata.customproperty30='Y' WHERE (DATE_FORMAT(currentusers.termdate, '%Y-%m-%d') <= (DATE_FORMAT(Curdate(), '%Y-%m-%d') - INTERVAL 90 DAY))",
"UPDATE NEWUSERDATA SET newuserdata.customproperty31 = CONCAT(newuserdata.CUSTOMPROPERTY29,'\\\\, ',newuserdata.CUSTOMPROPERTY27)"
]
}
Is there something wrong with the query?
05/03/2024 11:32 AM
@nvachhani : See the response in thread: https://forums.saviynt.com/t5/identity-governance/accented-characters-conversion/m-p/89465#M58014 and see if that works where similar issue is reported.
05/04/2024 08:02 AM
05/06/2024 08:44 AM
Hi I am seeing the same error even when adding the COLLATE utf8_general_ci in the preprocessor. Also, this is not a file upload, this error is occurring with the Workday RaaS user import job.
05/06/2024 08:52 AM
Validate if this works with file upload
05/06/2024 01:08 PM
This is working with file upload
05/06/2024 06:25 PM
Please share both logs to compare
05/07/2024 11:48 AM
Now I can see the same error in both CSV and job import when using a name with accented character:
05/07/2024 07:08 PM
{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey, username, statuskey, firstname, lastname, middlename, termdate, comments, systemusername FROM USERS"
},
"COMPUTEDCOLUMNS": [
"systemusername",
"statuskey",
"customproperty27",
"customproperty28",
"customproperty29",
"customproperty30",
"customproperty31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY27 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY27, null) COLLATE utf8_general_ci",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY28 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY28, null) COLLATE utf8_general_ci",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY29 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY29, null) COLLATE utf8_general_ci",
"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.STATUSKEY = 0 WHERE CURRENTUSERS.COMMENTS LIKE 'User Terminated By%' AND (DATE_FORMAT(CURRENTUSERS.TERMDATE, '%Y-%m-%d') >= (DATE_FORMAT(CURDATE(), '%Y-%m-%d') - INTERVAL 1 DAY))",
"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.SYSTEMUSERNAME = CASE WHEN NEWUSERDATA.FIRSTNAME != CURRENTUSERS.FIRSTNAME THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) WHEN NEWUSERDATA.LASTNAME != CURRENTUSERS.LASTNAME THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) ELSE CURRENTUSERS.SYSTEMUSERNAME END",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY30 = 'N'",
"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY30 = 'Y' WHERE (DATE_FORMAT(CURRENTUSERS.TERMDATE, '%Y-%m-%d') <= (DATE_FORMAT(CURDATE(), '%Y-%m-%d') - INTERVAL 90 DAY))",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY31 = CONCAT(NEWUSERDATA.CUSTOMPROPERTY29, '\\\\, ', NEWUSERDATA.CUSTOMPROPERTY27) COLLATE utf8_general_ci"
]
}
05/08/2024 05:37 AM
05/08/2024 08:56 PM
{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey, username, statuskey, firstname, lastname, middlename, termdate, comments, systemusername FROM USERS"
},
"COMPUTEDCOLUMNS": [
"systemusername",
"statuskey",
"customproperty27",
"customproperty28",
"customproperty29",
"customproperty30",
"customproperty31"
],
"PREPROCESSQUERIES": [
"ALTER TABLE NEWUSERDATA MODIFY COLUMN CUSTOMPROPERTY27 VARCHAR(255) COLLATE utf8_general_ci",
"ALTER TABLE NEWUSERDATA MODIFY COLUMN CUSTOMPROPERTY28 VARCHAR(255) COLLATE utf8_general_ci",
"ALTER TABLE NEWUSERDATA MODIFY COLUMN CUSTOMPROPERTY29 VARCHAR(255) COLLATE utf8_general_ci",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY27 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY27, null)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY28 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY28, null)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY29 = FN_EIC_REPLACE(NEWUSERDATA.CUSTOMPROPERTY29, null)",
"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.STATUSKEY = 0 WHERE CURRENTUSERS.COMMENTS LIKE 'User Terminated By%' AND (DATE_FORMAT(CURRENTUSERS.TERMDATE, '%Y-%m-%d') >= (DATE_FORMAT(CURDATE(), '%Y-%m-%d') - INTERVAL 1 DAY))",
"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.SYSTEMUSERNAME = CASE WHEN NEWUSERDATA.FIRSTNAME != CURRENTUSERS.FIRSTNAME THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) WHEN NEWUSERDATA.LASTNAME != CURRENTUSERS.LASTNAME THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) ELSE CURRENTUSERS.SYSTEMUSERNAME END",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY30 = 'N'",
"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY30 = 'Y' WHERE (DATE_FORMAT(CURRENTUSERS.TERMDATE, '%Y-%m-%d') <= (DATE_FORMAT(CURDATE(), '%Y-%m-%d') - INTERVAL 90 DAY))",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY31 = CONCAT(NEWUSERDATA.CUSTOMPROPERTY29, '\\\\, ', NEWUSERDATA.CUSTOMPROPERTY27)"
]
}
05/09/2024 12:29 PM
I am still seeing the same error:
2024-05-09T15:26:00-04:00-ecm-worker--null-cp928--09-May-2024 19:25:59.515 WARNING [quartzScheduler_Worker-10] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_20445 SET TEMPNEWUSERS_20445.CUSTOMPROPERTY27 = FN_EIC_REPLACE(TEMPNEWUSERS_20445.CUSTOMPROPERTY27, null) because: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
05/09/2024 07:06 PM
same with file import ?
05/10/2024 09:49 AM
Yes same with file import
05/10/2024 09:51 AM
Please raise support ticket