Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-14193

deletePersistedTargetIds could result in SQL exception: valid column name 'reconId'

    Details

    • Target Version/s:
    • Verified Version/s:
    • Story Points:
      1
    • Sprint:
      2020.04 - IDM
    • Support Ticket IDs:
    • Zendesk ID:
      45223

      Description

      +underlined text+In repo.jdbc.json, "delete-target-ids-for-recon" uses column name reconId.

      "delete-target-ids-for-recon" : "DELETE FROM ${_dbSchema}.${_table} WHERE reconId = ${reconId}"

      This doesn't match the schema, which uses column name reconid, all lowercase.

      CREATE  TABLE  [openidm].[clusteredrecontargetids]
      (
        objectid NVARCHAR(38) NOT NULL ,
        rev NVARCHAR(38) NOT NULL ,
        reconid NVARCHAR(255) NOT NULL ,
        targetids NTEXT NOT NULL ,
        PRIMARY KEY CLUSTERED (objectid)
      );

      It becomes an issue if column names are case sensitive.  The resulting error:

      [110] Nov 28, 2019 7:32:30.829 PM org.forgerock.openidm.sync.impl.cluster.ClusteredSourcePhaseTargetIdRegistryImpl deletePersistedTargetIds
      SEVERE: Exception caught deleting target ids for recon id: <uuid>
      org.forgerock.json.resource.InternalServerErrorException: DB reported failure executing query.
       at org.forgerock.openidm.repo.jdbc.impl.query.TableQueries.command(TableQueries.java:377)
       at org.forgerock.openidm.repo.jdbc.impl.ExplicitTableHandler.command(ExplicitTableHandler.java:549)
       at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.lambda$handleAction$7(JDBCRepoService.java:623)
       at org.forgerock.openidm.metrics.MetricsCollector.time(MetricsCollector.java:112)
       at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.handleAction(JDBCRepoService.java:604)
       ...
       at org.forgerock.openidm.sync.impl.cluster.ClusteredSourcePhaseTargetIdRegistryImpl.deletePersistedTargetIds(ClusteredSourcePhaseTargetIdRegistryImpl.java:101)
       at org.forgerock.openidm.sync.ClusteredRecon.doClusteredReconFailureTasks(ClusteredRecon.java:369)
       at org.forgerock.openidm.sync.ClusteredRecon.dispatchClusteredRecon(ClusteredRecon.java:115)
      [111] Nov 29, 2019 1:47:58.046 PM org.forgerock.openidm.repo.jdbc.impl.query.TableQueries command
      FINE: DB reported failure executing query org.forgerock.openidm.repo.jdbc.impl.query.TracedPreparedStatement@2122615b with params: {commandId=delete-target-ids-for-recon, reconId=<uuid>, _resource=clusteredrecontargetids} error code: 207 sqlstate: S0001 message: Invalid column name 'reconId'. 
      com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'reconId'.

      The workaround is to modify repo.jdbc.json to use column name matching schema.

      "delete-target-ids-for-recon" : "DELETE FROM ${_dbSchema}.${_table} WHERE reconid = ${reconId}"

        Attachments

          Activity

            People

            • Assignee:
              katie.gonzalez Katie Gonzalez
              Reporter:
              yinyan.cao Yinyan Cao
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: