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

SQL syntax error using MySQL 8.0.11

    Details

    • Target Version/s:
    • Verified Version/s:
    • Story Points:
      2
    • Sprint:
      2020.09 - IDM

      Description

      Manual Reproduction:

      Add the following logging properties.

      org.forgerock.openidm.repo.jdbc.impl.query.TableQueries.level=FINEST
      org.forgerock.openidm.health.HealthService.level=FINEST
      

      Append the example schedule schedule-autoPurgeAuditRecon.json to the following:

      {
          "enabled" : false,
          "type" : "simple",
          "repeatInterval" : 1000,
          "persisted" : true,
          "misfirePolicy" : "doNothing",
          "invokeService" : "script",
          "invokeContext" : {
              "script" : {
                  "type" : "text/javascript",
                  "file" : "audit/autoPurgeAuditRecon.js",
                  "input" : {
                      "mappings" : [ "%" ],
                      "purgeType" : "purgeByNumOfReconsToKeep",
                      "numOfRecons" : 3,
                      "intervalUnit" : "minutes",
                      "intervalValue" : 1
                  }
              }
          }
      }
      

      Copy the above schedule to the conf directory of the sync-two-external-resources sample.

      Start IDM using the aforementioned sample and enable the schedule.

      Launch multiple reconciliations using the following curl:

      curl --header "X-OpenIDM-Username: openidm-admin" --header 
      "X-OpenIDM-Password: openidm-admin" --header "Content-Type: 
      application/json" --request  POST http://localhost:8080/openidm/recon?_action=recon&mapping=systemLdapAccounts_managedUser&waitForCompletion=True
      

      The following appears in IDM logs:

      FINE: DB reported failure executing query org.forgerock.openidm.repo.jdbc.impl.query.TracedPreparedStatement@428d7b8b with params: {includeMapping=%%, excludeMapping=, commandId=purge-by-recon-number-of, _resource=audit/recon, numberOf=3} error code: 1064 sqlstate: 42000 message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row_number, @mapping := mapping as m FROM openidm.auditrecon WHERE mapping LIKE ' at line 1 
      java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row_number, @mapping := mapping as m FROM openidm.auditrecon WHERE mapping LIKE ' at line 1
      	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
      	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.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
      	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
      	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
      	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
      	at org.forgerock.openidm.repo.jdbc.impl.query.TracedPreparedStatement.executeUpdate(TracedPreparedStatement.java:114)
      	at org.forgerock.openidm.repo.jdbc.impl.query.TableQueries.lambda$command$1(TableQueries.java:385)
      	at org.forgerock.openidm.metrics.MetricsCollector.time(MetricsCollector.java:51)
      	at org.forgerock.openidm.metrics.MetricsCollector.time(MetricsCollector.java:68)
      	at org.forgerock.openidm.repo.jdbc.impl.query.TableQueries.command(TableQueries.java:384)
      	at org.forgerock.openidm.repo.jdbc.impl.ExplicitTableHandler.command(ExplicitTableHandler.java:588)
      	at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.lambda$handleAction$10(JDBCRepoService.java:688)
      	at org.forgerock.openidm.metrics.MetricsCollector.time(MetricsCollector.java:112)
      	at org.forgerock.openidm.repo.jdbc.impl.JDBCRepoService.handleAction(JDBCRepoService.java:669)
      	at org.forgerock.json.resource.Router.handleAction(Router.java:251)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:54)
      	at org.forgerock.json.resource.ResourceApiVersionRoutingFilter.filterAction(ResourceApiVersionRoutingFilter.java:39)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.FilterChain.handleAction(FilterChain.java:225)
      	at org.forgerock.json.resource.Router.handleAction(Router.java:251)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:54)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:44)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:44)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:44)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:44)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:44)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.openidm.audit.filter.AuditFilter.lambda$filterAction$0(AuditFilter.java:115)
      	at org.forgerock.openidm.audit.filter.AuditFilter.logAuditAccessEntry(AuditFilter.java:171)
      	at org.forgerock.openidm.audit.filter.AuditFilter.filterAction(AuditFilter.java:115)
      	at org.forgerock.openidm.router.filter.MutableFilterDecorator.filterAction(MutableFilterDecorator.java:66)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:42)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.openidm.servlet.internal.ResourceFilters$3.lambda$filterAction$0(ResourceFilters.java:170)
      	at org.forgerock.openidm.servlet.internal.ResourceFilters$3.handleRequestWithLogging(ResourceFilters.java:222)
      	at org.forgerock.openidm.servlet.internal.ResourceFilters$3.filterAction(ResourceFilters.java:170)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.openidm.filter.QueryIdToQueryFilterTransformFilter.filterAction(QueryIdToQueryFilterTransformFilter.java:154)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.openidm.router.filter.PassthroughFilter.filterAction(PassthroughFilter.java:42)
      	at org.forgerock.openidm.router.filter.MutableFilterDecorator.filterAction(MutableFilterDecorator.java:66)
      	at org.forgerock.openidm.router.filter.MutableFilterDecorator.filterAction(MutableFilterDecorator.java:66)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:44)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.Filters$ConditionalFilter.filterAction(Filters.java:44)
      	at org.forgerock.json.resource.FilterChain$Cursor.handleAction(FilterChain.java:52)
      	at org.forgerock.json.resource.FilterChain.handleAction(FilterChain.java:225)
      	at org.forgerock.json.resource.InternalConnection.actionAsync(InternalConnection.java:29)
      	at org.forgerock.json.resource.AbstractAsynchronousConnection.action(AbstractAsynchronousConnection.java:32)
      	at org.forgerock.json.resource.AbstractConnectionWrapper.action(AbstractConnectionWrapper.java:65)
      	at org.forgerock.openidm.servlet.internal.ServletConnectionFactory$InternalConnectionWrapper.lambda$action$10(ServletConnectionFactory.java:322)
      	at org.forgerock.openidm.metrics.MetricsCollector.time(MetricsCollector.java:51)
      	at org.forgerock.openidm.metrics.MetricsCollector.time(MetricsCollector.java:68)
      	at org.forgerock.openidm.servlet.internal.ServletConnectionFactory$InternalConnectionWrapper.time(ServletConnectionFactory.java:249)
      	at org.forgerock.openidm.servlet.internal.ServletConnectionFactory$InternalConnectionWrapper.action(ServletConnectionFactory.java:322)
      	at org.forgerock.json.resource.AbstractConnectionWrapper.action(AbstractConnectionWrapper.java:65)
      	at org.forgerock.openidm.script.impl.ResourceFunctions$ActionFunction.action(ResourceFunctions.java:941)
      	at org.forgerock.openidm.script.impl.ResourceFunctions$ActionFunction.call(ResourceFunctions.java:923)
      	at org.forgerock.openidm.script.impl.ResourceFunctions$ActionFunction.call(ResourceFunctions.java:825)
      	at org.forgerock.openidm.script.javascript.ScriptableFunction.call(ScriptableFunction.java:103)
      	at org.mozilla.javascript.optimizer.OptRuntime.callN(OptRuntime.java:65)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32._c_anonymous_5(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js:106)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32.call(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js)
      	at org.mozilla.javascript.optimizer.OptRuntime.call2(OptRuntime.java:55)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32._c_anonymous_1(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js:156)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32.call(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js)
      	at org.mozilla.javascript.optimizer.OptRuntime.callN(OptRuntime.java:65)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32._c_script_0(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js:28)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32.call(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js)
      	at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:412)
      	at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3545)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32.call(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js)
      	at org.mozilla.javascript.gen._home_jenkins_workspace_PyForge_PR_results_20200707_230117_scheduler_auto_purge_for_audit_sample_by_number_of_recons_IDM_openidm_bin_defaults_script_audit_autoPurgeAuditRecon_js_32.exec(/home/jenkins/workspace/PyForge-PR/results/20200707-230117/scheduler/auto_purge_for_audit_sample_by_number_of_recons/IDM/openidm/bin/defaults/script/audit/autoPurgeAuditRecon.js)
      	at org.forgerock.openidm.script.javascript.RhinoScript.eval(RhinoScript.java:231)
      	at jdk.internal.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
      	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
      	at org.forgerock.openidm.script.registry.ScriptRegistryImpl$LibraryRecord.invoke(ScriptRegistryImpl.java:553)
      	at com.sun.proxy.$Proxy55.eval(Unknown Source)
      	at org.forgerock.openidm.script.registry.ScriptRegistryImpl$ScriptImpl.eval(ScriptRegistryImpl.java:831)
      	at org.forgerock.openidm.script.registry.ScriptRegistryImpl$ScriptImpl.eval(ScriptRegistryImpl.java:853)
      	at org.forgerock.openidm.script.impl.ScriptRegistryService.execScript(ScriptRegistryService.java:810)
      	at org.forgerock.openidm.script.impl.ScriptRegistryService.execute(ScriptRegistryService.java:763)
      	at org.forgerock.openidm.quartz.SchedulerServiceJob.execute(SchedulerServiceJob.java:123)
      	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
      	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
      

       

      PyForge Test Failure:

      PyBot/OpenIDM/testcases/functional/scheduler/schedule_examples/auto_purge_for_audit_sample_by_number_of_recons.robot
      

       

      Command to Reproduce:

      ./run-pybot.py -s scheduler.schedule_examples.auto_purge_for_audit_sample_by_number_of_recons openidm 

        Attachments

          Activity

            People

            • Assignee:
              cgdrake Chris Drake
              Reporter:
              brayden.roth-white Brayden Roth-White
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: