Saviynt Forums

nvachhani · ‎05/03/2024

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?

Saathvik · ‎05/03/2024

@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.

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rushikeshvartak · ‎05/04/2024

Validate file format
{
"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)) COLLATE utf8_general_ci",
"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 COLLATE utf8_general_ci",
"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)) COLLATE utf8_general_ci",
"UPDATE NEWUSERDATA SET newuserdata.customproperty31 = CONCAT(newuserdata.CUSTOMPROPERTY29,'\\\\, ',newuserdata.CUSTOMPROPERTY27) COLLATE utf8_general_ci"
]
}

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

nvachhani · ‎05/06/2024

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.

rushikeshvartak · ‎05/06/2024

Validate if this works with file upload

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

nvachhani · ‎05/06/2024

This is working with file upload

rushikeshvartak · ‎05/06/2024

Please share both logs to compare

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

nvachhani · ‎05/07/2024

Now I can see the same error in both CSV and job import when using a name with accented character:

2024-05-07T14:46:01-04:00-ecm--null-tr4qx--07-May-2024 18:46:01.325 WARNING [http-nio-8080-exec-1] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_19886 SET TEMPNEWUSERS_19886.CUSTOMPROPERTY29 = FN_EIC_REPLACE(TEMPNEWUSERS_19886.CUSTOMPROPERTY29, null) COLLATE utf8_general_ci because: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

2024-05-07T14:46:01-04:00-ecm-services.ImportSAvDataUserService-http-nio-8080-exec-1-tr4qx-ERROR-Error while processing data:

2024-05-07T14:46:01-04:00-ecm--null-tr4qx--java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8710) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1714) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

rushikeshvartak · ‎05/07/2024

{
    "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"
    ]
}

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

nvachhani · ‎05/08/2024

Unfortunately I am seeing the same error.

2024-05-08T08:32:08-04:00-ecm--null-tr4qx--08-May-2024 12:32:07.886 WARNING [http-nio-8080-exec-6] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_20043 SET TEMPNEWUSERS_20043.CUSTOMPROPERTY29 = FN_EIC_REPLACE(TEMPNEWUSERS_20043.CUSTOMPROPERTY29, null) COLLATE utf8_general_ci because: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

rushikeshvartak · ‎05/08/2024

{
    "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)"
    ]
}

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

nvachhani · ‎05/09/2024

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 '='

rushikeshvartak · ‎05/09/2024

same with file import ?

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

nvachhani · ‎05/10/2024

Yes same with file import

rushikeshvartak · ‎05/10/2024

Please raise support ticket

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.