7.9.1 Can't create users in DB User Profile

JordanCClark · December 14, 2017, 7:49pm

After creating a DB user profile for testing, I can create/edit roles with no issue, but can’t create any users, either from the gateway, or the User Management component. Tried creating under a couple of different names and/or prefixes, but gives the same error (It does follow the prefixes, so I’ve got that going for me!)

Ignition Platform v7.9.1
Postgres 9.5.something-or-other

org.postgresql.util.PSQLException: ERROR: relation "dbuserprofile_users_"id"_seq" does not exist Position: 16

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeQuery(SRConnectionWrapper.java:945)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runScalarPrepQuery(SRConnectionWrapper.java:236)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runScalarQuery(SRConnectionWrapper.java:224)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.fetchGeneratedKey(SRConnectionWrapper.java:281)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepInsertGetKey(SRConnectionWrapper.java:255)

at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseAutomaticUserSource.addUser(DatabaseAutomaticUserSource.java:522)

at com.inductiveautomation.ignition.gateway.authentication.UserSourceWrapper.addUser(UserSourceWrapper.java:247)

at com.inductiveautomation.ignition.gateway.web.components.user.UserSourceEditPage.onUserAdded(UserSourceEditPage.java:279)

at com.inductiveautomation.ignition.gateway.web.components.user.UserSourceEditPage$UserActionTable$11.onUserEdited(UserSourceEditPage.java:257)

at com.inductiveautomation.ignition.gateway.web.components.user.UserEditForm.onSubmit(UserEditForm.java:690)

at com.inductiveautomation.ignition.gateway.web.components.user.UserEditForm$1.onSubmit(UserEditForm.java:193)

at org.apache.wicket.markup.html.form.Form$9.component(Form.java:1248)

at org.apache.wicket.markup.html.form.Form$9.component(Form.java:1242)

at org.apache.wicket.util.visit.Visits.visitPostOrderHelper(Visits.java:274)

at org.apache.wicket.util.visit.Visits.visitPostOrder(Visits.java:245)

at org.apache.wicket.markup.html.form.Form.delegateSubmit(Form.java:1241)

at org.apache.wicket.markup.html.form.Form.process(Form.java:923)

at org.apache.wicket.markup.html.form.Form.onFormSubmitted(Form.java:769)

at org.apache.wicket.markup.html.form.Form.onFormSubmitted(Form.java:702)

at sun.reflect.GeneratedMethodAccessor115.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.wicket.RequestListenerInterface.internalInvoke(RequestListenerInterface.java:258)

at org.apache.wicket.RequestListenerInterface.invoke(RequestListenerInterface.java:216)

at org.apache.wicket.core.request.handler.ListenerInterfaceRequestHandler.invokeListener(ListenerInterfaceRequestHandler.java:240)

at org.apache.wicket.core.request.handler.ListenerInterfaceRequestHandler.respond(ListenerInterfaceRequestHandler.java:226)

at org.apache.wicket.request.cycle.RequestCycle$HandlerExecutor.respond(RequestCycle.java:814)

at org.apache.wicket.request.RequestHandlerStack.execute(RequestHandlerStack.java:64)

at org.apache.wicket.request.cycle.RequestCycle.execute(RequestCycle.java:253)

at org.apache.wicket.request.cycle.RequestCycle.processRequest(RequestCycle.java:210)

at org.apache.wicket.request.cycle.RequestCycle.processRequestAndDetach(RequestCycle.java:281)

at org.apache.wicket.protocol.http.WicketFilter.processRequest(WicketFilter.java:188)

at org.apache.wicket.protocol.http.WicketFilter.doFilter(WicketFilter.java:245)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1668)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:581)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)

at org.eclipse.jetty.server.Server.handle(Server.java:518)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)

at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)

at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)

at java.lang.Thread.run(Thread.java:745)

pturmel · December 14, 2017, 8:41pm

Turn on query logging in your database so you can see what is really be sent for execution. It looks like a failed concatenation of the table name (speculation based on the placement of the double quotes in the first line of the error).

JordanCClark · March 22, 2018, 4:47pm

Finally got back to this. It only reminded me, because it does the same thing with writing to an alarm journal.

2018-03-21 13:58:51 UTC [1221-1] insight@audit ERROR:  relation "alarm_events_"id"_seq" does not exist at character 16
2018-03-21 13:58:51 UTC [1221-2] insight@audit STATEMENT:  SELECT currval('alarm_events_"id"_seq')

JordanCClark · March 23, 2018, 5:36pm

Same thing after upgrading to 7.9.5 (the current one at the time) and upgrading the jdbc driver. Ticket submitted.

JordanCClark · January 13, 2021, 6:18pm

One last visit to this for closure. When I made the switch to v8, I decided to recreate all my db, OPC, and device connections from scratch instead of trying to import my gateway. No more issues.

Still not sure from where it cropped up in the first place, but pretty sure there was some degradation in there, as it had come up through the ranks from the FPMI days.