IdentityIQ 8.5 upgrade script syntax errors

Feedback Bugs
1

What problem are you observing?

IdentityIQ 8.5 upgrade script syntax errors: Three out of the four upgrade scripts (.sqlserver, .mysql, and .oracle) have the same syntax issue; the .postgresql script looks fine.

upgrade_identityiq_tables.sqlserver
upgrade_identityiq_tables.oracle
upgrade_identityiq_tables.mysql

Today, I performed the upgrade from IIQ 8.4 to IIQ 8.5 and found basic syntax errors, for example, in upgrade_identityiq_tables.sqlserver

Observed wrong syntax:

EXEC sp_rename N'table_name.new_index_name', N'old_index_name', N'INDEX';
GO

–– ACCESS HISTORY upgrade begin

USE identityiqah
GO

– DO NOT REMOVE OR MODIFY BLOCK
– CONTEXT-SWITCH: dataSourceAccessHistory
– DO NOT REMOVE OR MODIFY BLOCK

EXEC sp_rename N'identityiqah.spt_hist_certification.uk_kesw6k26csy7qxitgtloos7y2', N'UK_jnf8asso0unr7868bv24coqyn', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_mattr.UK_sgmfjmeagljgj36cgi3o0cltw', N'UK_rle9g3il01fvqdeklw5hc5l5h', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_role.spt_hist_role_displayname_ci', N'spt_hist_role_displayName_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_role.uk_kem9ulqu2gybfqdtq226k3htc', N'UK_i8w6vo76pdttiu6urwhbcaf9r', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_capability.spt_hist_cap_displayname_ci', N'spt_hist_cap_displayName_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_capability.uk_q19cl120ixe3opavvwxoi0ahu', N'UK_pid42ojf0r3fmg5jcr1e9mj09', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_policy_violation_remediation_entitlements.fkjie8pn4nfhcfi3t92br291fc1', N'FKjie8pn4nfhcfi3t92br291fc1', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_accounts.fk605klb45cwnh86urs1aw07cue', N'FK605klb45cwnh86urs1aw07cue', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup.spt_hist_wg_displayname_ci', N'spt_hist_wg_displayName_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_capture.spt_hwc_displayname_ci', N'spt_hwc_displayName_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_event.spt_hist_wg_evt_evt_dt', N'spt_hist_wg_event_evt_dt', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_event.spt_hist_wg_evt_evt_cat', N'spt_hist_wg_event_evt_cat', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_event.spt_hist_wg_evt_evt_type', N'spt_hist_wg_event_evt_type', N'INDEX';
GO

What is the correct behavior?

The correct syntax:

EXEC sp_rename N'table_name.old_index_name', N'new_index_name', N'INDEX';
GO

–– ACCESS HISTORY upgrade begin

USE identityiqah
GO

– DO NOT REMOVE OR MODIFY BLOCK
– CONTEXT-SWITCH: dataSourceAccessHistory
– DO NOT REMOVE OR MODIFY BLOCK

EXEC sp_rename N'identityiqah.spt_hist_certification.UK_jnf8asso0unr7868bv24coqyn', N'uk_kesw6k26csy7qxitgtloos7y2', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_mattr.UK_rle9g3il01fvqdeklw5hc5l5h', N'UK_sgmfjmeagljgj36cgi3o0cltw', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_role.spt_hist_role_displayName_ci', N'spt_hist_role_displayname_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_role.UK_i8w6vo76pdttiu6urwhbcaf9r', N'uk_kem9ulqu2gybfqdtq226k3htc', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_capability.spt_hist_cap_displayName_ci', N'spt_hist_cap_displayname_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_capability.UK_pid42ojf0r3fmg5jcr1e9mj09', N'uk_q19cl120ixe3opavvwxoi0ahu', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_policy_violation_remediation_entitlements.FKjie8pn4nfhcfi3t92br291fc1', N'fkjie8pn4nfhcfi3t92br291fc1', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_accounts.FK605klb45cwnh86urs1aw07cue', N'fk605klb45cwnh86urs1aw07cue', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup.spt_hist_wg_displayName_ci', N'spt_hist_wg_displayname_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_capture.spt_hwc_displayName_ci', N'spt_hwc_displayname_ci', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_event.spt_hist_wg_event_evt_dt', N'spt_hist_wg_evt_evt_dt', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_event.spt_hist_wg_event_evt_cat', N'spt_hist_wg_evt_evt_cat', N'INDEX';
GO

EXEC sp_rename N'identityiqah.spt_hist_workgroup_event.spt_hist_wg_event_evt_type', N'spt_hist_wg_evt_evt_type', N'INDEX';
GO

What product feature is this related to?

Access History upgrade script syntax failures.

What are the steps to reproduce the issue?

Just followed the SailPoint document steps to perform the migration.

during database upgrade step- we will encounter the syntax errors.

Do you have any other information about your environment that may help?

Tomcat 9.x.x, JDK11.x.x, SQL Server(.sqlserver), this is the same case for .oracle, .mysql upgrade scripts.

2 Likes
2

MYSQL - DataBase: upgrade script name:
upgrade_identityiq_tables.mysql

wrong syntax observed :

ALTER TABLE identityiqah.table_name
    RENAME INDEX new_index_name TO old_index_name;

--
-- ACCESS HISTORY upgrade begin
--

USE identityiqah;

-- DO NOT REMOVE OR MODIFY BLOCK
-- CONTEXT-SWITCH: dataSourceAccessHistory
-- DO NOT REMOVE OR MODIFY BLOCK

ALTER TABLE identityiqah.spt_hist_certification
    RENAME INDEX uk_kesw6k26csy7qxitgtloos7y2 TO UK_jnf8asso0unr7868bv24coqyn;

ALTER TABLE identityiqah.spt_hist_role
    RENAME INDEX spt_hist_role_displayname_ci TO spt_hist_role_displayName_ci;

ALTER TABLE identityiqah.spt_hist_role
    RENAME INDEX uk_kem9ulqu2gybfqdtq226k3htc TO UK_i8w6vo76pdttiu6urwhbcaf9r;

ALTER TABLE identityiqah.spt_hist_mattr
    RENAME INDEX UK_sgmfjmeagljgj36cgi3o0cltw TO UK_rle9g3il01fvqdeklw5hc5l5h;

ALTER TABLE identityiqah.spt_hist_capability
    RENAME INDEX UK_q19cl120ixe3opavvwxoi0ahu TO UK_pid42ojf0r3fmg5jcr1e9mj09;

ALTER TABLE identityiqah.spt_hist_workgroup
    RENAME INDEX spt_hist_wg_displayname_ci TO spt_hist_wg_displayName_ci;

ALTER TABLE identityiqah.spt_hist_workgroup_capture
    RENAME INDEX spt_hwc_displayname_ci TO spt_hwc_displayName_ci;

ALTER TABLE identityiqah.spt_hist_workgroup_event
    RENAME INDEX spt_hist_wg_evt_evt_dt TO spt_hist_wg_event_evt_dt;

ALTER TABLE identityiqah.spt_hist_workgroup_event
    RENAME INDEX spt_hist_wg_evt_evt_cat TO spt_hist_wg_event_evt_cat;

ALTER TABLE identityiqah.spt_hist_workgroup_event
    RENAME INDEX spt_hist_wg_evt_evt_type TO spt_hist_wg_event_evt_type;

The correct syntax:

ALTER TABLE identityiqah.table_name
    RENAME INDEX old_index_name TO new_index_name;
1 Like
3

Hi @iiq-isc

That’s a good observation about the index syntax mistake. It’s a minor error, but it’ll have a big impact since it’s for the major release of IdentityIQ 8.5.

I’ve validated this in my local MySQL setup. Tomorrow, I’ll try to fix it in the SQL Server (upgrade_identityiq_tables.sqlserver) script and give you an update.

image
image721×261 61.8 KB

ALTER TABLE identityiqah.spt_hist_certification
    RENAME INDEX uk_kesw6k26csy7qxitgtloos7y2 TO UK_jnf8asso0unr7868bv24coqyn;

I’m still unable to understand how come SailPoint’s team could have missed such a basic syntax error in a major release like IdentityIQ 8.5.

4

Thanks @iiq-isc, the recommended index syntax change works fine.

It would be better to wait for official confirmation from SailPoint before utilizing the same fix.

5

Within the IdentityIQ Installation Guide, under ‘Upgrade the IdentityIQ Database’ you will find that these errors are expected. Please review the 8.5 documentation at your convenience, specifically

image
image889×600 48.9 KB

1 Like
7

Dear @mike_hovis

Thanks for the quick response and pointing out the relevant document reference which will clarify most of the errors during upgrade process, i agree and align with your comments,no doubts except below INDEX renaming portion}

As per my understanding there is an attempt to rename index naming to make a uniformity of index naming convention to align with existing index naming convention.

In this process mistakenly renaming From New_index To Old_index

Instead of renaming From Old_index To New_index

Kindly clarify particularly about the above 13 lines of sqlserver code.

8 
ALTER TABLE identityiqah.spt_hist_certification
    RENAME INDEX uk_kesw6k26csy7qxitgtloos7y2 TO UK_jnf8asso0unr7868bv24coqyn;

uk_kesw6k26csy7qxitgtloos7y2 is the old index. UK_jnf8asso0unr7868bv24coqyn is the new index. This expression is correct.

ALTER TABLE identityiqah.spt_hist_role
    RENAME INDEX spt_hist_role_displayname_ci TO spt_hist_role_displayName_ci;

spt_hist_role_displayname_ci is the old index. spt_hist_role_displayName_ci is the new index. This expression is correct.

ALTER TABLE identityiqah.spt_hist_role
    RENAME INDEX uk_kem9ulqu2gybfqdtq226k3htc TO UK_i8w6vo76pdttiu6urwhbcaf9r;

uk_kem9ulqu2gybfqdtq226k3htc is the old index. UK_i8w6vo76pdttiu6urwhbcaf9r is the new index. This expression is correct.

Instead of going through the remaining cited expressions, I’ll simply ask of you to correlate any of the remaining expressions on your own. Any you still determine to be invalid expression should be brought up with Support.

9

Your comment is not entirely convincing, as I am asking you to check the IdentityIQ 8.4 index name, which we are referring to as old_index. However, you seem to be diverting the discussion to a different database type. I kindly request that you focus on the SQL Server script: upgrade_identityiq_tables.sqlserver.

It appears you have not even reviewed the current 8.4 index from the Access History Database.

If you examine the index names of IdentityIQ 8.4, you will surely realize the significant mistake in the 8.5 upgrade_identityiq_tables.sqlserver code, specifically from line numbers 104 to 141.

A total of 13 indexes from 9 tables were incorrectly handled. The code attempts to rename them to match the 8.4 index names instead of using the new INDEX naming convention to ensure uniformity, as I mentioned in my previous comment.

image
image1404×700 275 KB

I hope the provided details help you find the mistake.

10

The index names you are citing are the correct and current index names for both 8.5 and 8.4. We had to address and upgrade issue that arose in 8.4 and as such, these corrections will cause warnings and confusion. This is why we felt the need to document them in the Installation Guide.

11

Hi @Trey

If we just swap the right side index to the left side and the left side index to the right side, then there are no warnings. Everything will be fine, and that’s what I’m expecting as a code fix. No one agrees with my point.

Sorry, I don’t understand when you say the 8.4 and 8.5 indexes are the same.

If both versions’ indexes are the same, then what’s the point of keeping these 13 lines of code in the upgrade_identityiq_tables.sqlserver file?

12

Here’s an exercise for you and other readers:

Start at 8.3 and then upgrade to 8.4. Take a look at those indexes and then observe how the upgrade to 8.5 goes.

13

Acess History Database introduced in identityIQ 8.4 version. IdentityIQ 8.3 we dont have access history DB.

14

Hi @Trey

I got clarity on these 13 lines and there’s no bug or no syntax issues.

I just went through the db2 upgrade_identityiq_tables.db2 file and found these INDEX values.

So these errors/warnings are expected. As you mentioned, we can ignore them and proceed with the migration/upgradation.