Error in adding usiers SQL ERROR

[khaled196]

Hi, I am creating an Omero server on my VM. and when I wanted to add users to it I got this error,

Can you please advise

Thank you

omero-server@2023BH01:~$ omero user add khaled khaled jumah  -P something --group-name Lab1
Using session for root@localhost:4064. Idle timeout: 10 min. Current group: system
Traceback (most recent call last):
  File "/opt/omero/server/venv3/bin/omero", line 8, in <module>
    sys.exit(main())
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero/main.py", line 126, in main
    rv = omero.cli.argv()
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero/cli.py", line 1787, in argv
    cli.invoke(args[1:])
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero/cli.py", line 1225, in invoke
    stop = self.onecmd(line, previous_args)
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero/cli.py", line 1302, in onecmd
    self.execute(line, previous_args)
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero/cli.py", line 1384, in execute
    args.func(args)
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero/cli.py", line 671, in _check_admin
    return func(*args, **kwargs)
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero/plugins/user.py", line 291, in add
    id = admin.createExperimenterWithPassword(e, rstring(pasw),
  File "/opt/omero/server/venv3/lib/python3.8/site-packages/omero_api_IAdmin_ice.py", line 1866, in createExperimenterWithPassword
    return _M_omero.api.IAdmin._op_createExperimenterWithPassword.invoke(self, ((user, password, defaultGroup, groups), _ctx))
omero.InternalException: exception ::omero::InternalException
{
    serverStackTrace = ome.conditions.InternalException:  Wrapped Exception: (org.springframework.jdbc.UncategorizedSQLException):
Hibernate operation: could not insert collection rows: [ome.model.meta.Experimenter.groupExperimenterMap#17]; uncategorized SQLException for SQL [update groupexperimentermap set child=?, child_index=? where id=?]; SQL state [P0003]; error code [0]; ERROR: query returned more than one row
  Hint: Make sure the query returns a single row, or use LIMIT 1.
  Where: PL/pgSQL function prevent_root_deactivate_update() line 7 at SQL statement; nested exception is org.postgresql.util.PSQLException: ERROR: query returned more than one row
  Hint: Make sure the query returns a single row, or use LIMIT 1.
  Where: PL/pgSQL function prevent_root_deactivate_update() line 7 at SQL statement
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:428)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:414)
        at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:125)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at ome.tools.hibernate.ProxyCleanupFilter$Interceptor.invoke(ProxyCleanupFilter.java:249)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at ome.services.util.ServiceHandler.invoke(ServiceHandler.java:121)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy98.createExperimenterWithPassword(Unknown Source)
        at jdk.internal.reflect.GeneratedMethodAccessor932.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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at ome.security.basic.BasicSecurityWiring.invoke(BasicSecurityWiring.java:93)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at ome.services.blitz.fire.AopContextInitializer.invoke(AopContextInitializer.java:43)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy98.createExperimenterWithPassword(Unknown Source)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at ome.services.blitz.util.IceMethodInvoker.invoke(IceMethodInvoker.java:172)
        at ome.services.throttling.Callback.run(Callback.java:56)
        at ome.services.throttling.InThreadThrottlingStrategy.callInvokerOnRawArgs(InThreadThrottlingStrategy.java:56)
        at ome.services.blitz.impl.AbstractAmdServant.callInvokerOnRawArgs(AbstractAmdServant.java:140)
        at ome.services.blitz.impl.AdminI.createExperimenterWithPassword_async(AdminI.java:185)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at omero.cmd.CallContext.invoke(CallContext.java:85)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy99.createExperimenterWithPassword_async(Unknown Source)
        at omero.api._IAdminTie.createExperimenterWithPassword_async(_IAdminTie.java:150)
        at omero.api._IAdminDisp.___createExperimenterWithPassword(_IAdminDisp.java:1545)
        at omero.api._IAdminDisp.__dispatch(_IAdminDisp.java:2253)
        at IceInternal.Incoming.invoke(Incoming.java:221)
        at Ice.ConnectionI.invokeAll(ConnectionI.java:2536)
        at Ice.ConnectionI.dispatch(ConnectionI.java:1145)
        at Ice.ConnectionI.message(ConnectionI.java:1056)
        at IceInternal.ThreadPool.run(ThreadPool.java:395)
        at IceInternal.ThreadPool.access$300(ThreadPool.java:12)
        at IceInternal.ThreadPool$EventHandlerThread.run(ThreadPool.java:832)
        at java.base/java.lang.Thread.run(Thread.java:829)

    serverExceptionClass = ome.conditions.InternalException
    message =  Wrapped Exception: (org.springframework.jdbc.UncategorizedSQLException):
Hibernate operation: could not insert collection rows: [ome.model.meta.Experimenter.groupExperimenterMap#17]; uncategorized SQLException for SQL [update groupexperimentermap set child=?, child_index=? where id=?]; SQL state [P0003]; error code [0]; ERROR: query returned more than one row
  Hint: Make sure the query returns a single row, or use LIMIT 1.
  Where: PL/pgSQL function prevent_root_deactivate_update() line 7 at SQL statement; nested exception is org.postgresql.util.PSQLException: ERROR: query returned more than one row
  Hint: Make sure the query returns a single row, or use LIMIT 1.
  Where: PL/pgSQL function prevent_root_deactivate_update() line 7 at SQL statement
}

[joshmoore]

@khaled196, thanks for opening an issue. The underlying exception is raised when something tries to change the administrator account (root) in such a way that would lock the system. I don't understand, though, why you are seeing it while trying to create a user.

What's the name of the user you are trying to create? Has the creation of other users succeeded? Are you seeing any other exceptions with this system?

[will-moore]

Also reported at https://www.openmicroscopy.org/qa2/qa/feedback/41345/ and https://www.openmicroscopy.org/qa2/qa/feedback/41344/