IncrementalAccountImport generating query in full CAPS

[PrashantG]

I did AccountFullImport with below xml, its working fine. The sql is executing in the same case that is configured in the xml

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
select name, `group`,entitlementvalue,status,'NPC_MySQL_DB_SS' as securitysystem, 'NPC_MYSQL_DB_App' as endpoint from mysql_saviynt_dev_db.accountfullrecon
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="group" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
</mapper>
</dataMapping>

Executing query

select name, `group`,entitlementvalue,status,'NPC_MySQL_DB_SS' as securitysystem, 'NPC_MYSQL_DB_App' as endpoint from mysql_saviynt_dev_db.accountfullrecon

BUT same query with incrementalImport (still trying though) setting, is getting executed with all CAPS and getting failed at mySQL DB level.  

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
select name, `group`,entitlementvalue,status,'NPC_MySQL_DB_SS' as securitysystem, 'NPC_MYSQL_DB_App' as endpoint from mysql_saviynt_dev_db.accountfullrecon
]]>
</sql-query>
<incrementalcondition>
<![CDATA['2023-01-01']]>
</incrementalcondition>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="noaction" deleteaccountentitlement="true" ifusernotexists="noaction" dateformat="date" incrementalcolumn="updatedate" systems="&quot;NPC_MySQL_DB_SS&quot;">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="group" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="accounts.customproperty25" sourceproperty="updatedate" type="date"/>
</mapper>
</dataMapping>

Executing Query

SELECT NAME, `GROUP`,ENTITLEMENTVALUE,STATUS,'NPC_MYSQL_DB_SS' AS SECURITYSYSTEM, 'NPC_MYSQL_DB_APP' AS ENDPOINT FROM MYSQL_SAVIYNT_DEV_DB.ACCOUNTFULLRECON

Error:

Error Code: 1142. SELECT command denied to user 'svc_mysql_db_acc'@'10.200.90.104' for table 'accountfullrecon'

any settings to suppress generation of all CAPS query...? as in production environment, every application need to change there DB settings in such case.

Hi Team,

any update on this pls

[This message has been edited by moderator to merge reply comment]

[Darshanjain]

Hi @PrashantG 

Whats the issue with CAPS, The query should be able to run with CAPS as well right or is there any restriction in the target end?

Even for full recon , the query executes with full CAPS only

Thanks
Darshan

[PrashantG]

Hi Darshan,

thanks for revert. 

- I checked in mysql query log, , for full recon, it used the same sql what is configured in the JSON (in lower case) but in case of incrementalexport, it is executing in all CAPS....

- in mysql DB there is a configuration "lower_case_table_names" = 1 to make it case insensitive. By default its 0 and cannot change it once DB is build...you will need to create a new DB with this parameter

- even my DB query is giving error due to this case sensitivity.

- Thinking loud, what if at application end this will be default setting and every application has to build the DB for this setting to work incremental export. This is not feasible for production application with years of data. 

[Darshanjain]

Hi @PrashantG 

can you provide me the snnippet of logs when its running sql query with full logs and the error.

Thanks

Darshan

[PrashantG]

PFA details...

[Darshanjain]

Hi @PrashantG 

I have checked and it seems for incremental recon its converting to uppercase in the code, i will check internally and get back to you on this on how we can proceed on this.

Thanks

Darshan

[Darshanjain]

Hi @PrashantG 

can you please raise a support ticket on this and mention this forum link.

We will track this as an issue 

Thanks

Darshan

[PrashantG]

Hi Darshan,

while I have raised support ticket, which is being looked by Saviynt team, have created another DB with case-insensitive tables.

I am still getting below error. can you pls take a look.

As per logs, can see, still full account import is getting executed with all rows getting fetched rather than incremental import. My assumption was it will take updatedate >= 2023-12-01.

Logs says, incrementalcolumn is missing error, which I have configured in the below xml

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
select name, `group`,entitlementvalue,status,'NPC_MySQL_DB_SS' as securitysystem, 'NPC_MYSQL_DB_App' as endpoint, updatedate from mysql_saviynt_dev2_db.ACCOUNTFULLRECON
]]>
</sql-query>
<incrementalcondition>
<![CDATA['2023-12-01']]>
</incrementalcondition>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="noaction" deleteaccountentitlement="false" ifusernotexists="noaction" dateformat="date" incrementalcolumn="updatedate" systems="&quot;NPC_MySQL_DB_SS&quot;">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="group" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="accounts.customproperty25" sourceproperty="updatedate" type="date"/>
</mapper>
</dataMapping>

[rushikeshvartak]

Incremental column should be. Lower case as its hardcoded in code