Error using admin tool

[mattc]

We're getting a database error when we try to use the admin to manage a user's group membership:

Traceback (most recent call last):

File "/usr/lib/python2.7/site-packages/django/core/handlers/base.py", line 132, in get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/home/omero/OMERO.server/lib/python/omeroweb/decorators.py", line 488, in wrapped
retval = f(request, *args, **kwargs)

File "/home/omero/OMERO.server/lib/python/omeroweb/decorators.py", line 538, in wrapper
context = f(request, *args, **kwargs)

File "/home/omero/OMERO.server/lib/python/omeroweb/webadmin/views.py", line 776, in manage_group
removalFails = conn.setMembersOfGroup(group, new_members)

File "/home/omero/OMERO.server/lib/python/omeroweb/webclient/webclient_gateway.py", line 1395, in setMembersOfGroup
admin_serv.addGroups(e, [group._obj])

File "/home/omero/OMERO.server/lib/python/omero/gateway/__init__.py", line 4675, in __call__
return self.handle_exception(e, *args, **kwargs)

File "/home/omero/OMERO.server/lib/python/omeroweb/webclient/webclient_gateway.py", line 2099, in handle_exception
e, *args, **kwargs)

File "/home/omero/OMERO.server/lib/python/omero/gateway/__init__.py", line 4672, in __call__
return self.f(*args, **kwargs)

File "/home/omero/OMERO.server/lib/python/omero_api_IAdmin_ice.py", line 1970, in addGroups
return _M_omero.api.IAdmin._op_addGroups.invoke(self, ((user, groups), _ctx))

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#303]; 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
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
Where: PL/pgSQL function prevent_root_deactivate_update() line 7 at SQL statement
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:117)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:108)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at ome.tools.hibernate.ProxyCleanupFilter$Interceptor.invoke(ProxyCleanupFilter.java:249)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at ome.services.util.ServiceHandler.invoke(ServiceHandler.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy89.addGroups(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at ome.security.basic.BasicSecurityWiring.invoke(BasicSecurityWiring.java:93)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at ome.services.blitz.fire.AopContextInitializer.invoke(AopContextInitializer.java:43)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy89.addGroups(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at ome.services.blitz.util.IceMethodInvoker.invoke(IceMethodInvoker.java:172)
at ome.services.throttling.Callback.run(Callback.java:56)
at ome.services.throttling.InThreadThrottlingStrategy.callInvokerWithMappedArgs(InThreadThrottlingStrategy.java:70)
at ome.services.blitz.impl.AbstractAmdServant.callInvokerOnMappedArgs(AbstractAmdServant.java:149)
at ome.services.blitz.impl.AdminI.addGroups_async(AdminI.java:111)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at omero.cmd.CallContext.invoke(CallContext.java:78)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy90.addGroups_async(Unknown Source)
at omero.api._IAdminTie.addGroups_async(_IAdminTie.java:72)
at omero.api._IAdminDisp.___addGroups(_IAdminDisp.java:1588)
at omero.api._IAdminDisp.__dispatch(_IAdminDisp.java:2202)
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.lang.Thread.run(Thread.java:745)

serverExceptionClass = ome.conditions.InternalException
message = Wrapped Exception: (org.springframework.jdbc.UncategorizedSQLException):
Hibernate operation: could not insert collection rows: [ome.model.meta.Experimenter.groupExperimenterMap#303]; 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
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
Where: PL/pgSQL function prevent_root_deactivate_update() line 7 at SQL statement
}

Any advice greatly appreciated!

[jmoore]

Hi Matt,

We're getting a database error when we try to use the admin to manage a user's group membership:
...
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#303]; 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
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
Where: PL/pgSQL function prevent_root_deactivate_update() line 7 at SQL statement

That's certainly not a very typical exception. Do you have any information on what led up to the exception? Was a database upgrade or similar performed? Did anyone access the database directly as opposed to be the admin site?

It might help of you could send us the contents of the groupexperimentermap table:

Code: Select all

psql -h localhost -U omero omero -c "copy groupexperimentermap(id, parent, child, child_index) to stdout"


Cheers,
~Josh

[mattc]

Hi Josh,

Thanks for your help. We migrated our data from Postgres 9.4 to 9.6 a short while before we started noticing this error. Our groupexperimentermap table looks like this:

0 0 0 0
1 1 0 1
2 2 1 0
3 3 2 0
4 1 2 1
53 0 2 2
278 1 259 1
55 1 52 1
57 1 53 1
279 254 260 0
103 103 102 0
104 1 102 1
153 0 52 2
154 153 152 0
155 1 152 1
156 153 52 0
54 3 52 3
203 203 202 0
204 1 202 1
58 0 53 2
253 53 252 0
254 1 252 1
256 1 253 1
257 0 253 2
258 0 252 2
259 253 254 0
260 1 254 1
261 254 255 0
262 1 255 1
263 254 252 3
280 1 260 1
313 254 306 0
267 254 256 0
268 1 256 1
269 254 257 0
270 1 257 1
271 255 258 0
272 1 258 1
273 203 53 4
274 103 53 5
275 253 53 6
276 255 53 7
277 254 259 0
281 254 261 0
282 1 261 1
56 53 53 0
264 254 53 3
283 256 53 8
284 256 253 4
285 256 252 4
286 256 262 0
287 1 262 1
288 256 263 0
289 1 263 1
303 254 302 0
304 1 302 1
305 53 303 0
306 1 303 1
307 53 304 0
308 1 304 1
309 203 305 0
310 1 305 1
311 303 53 9
312 0 303 2
314 1 306 1
315 254 303 3
316 254 307 0
317 1 307 1
318 304 308 0
319 1 308 1
320 256 309 0
321 1 309 1
322 305 310 0
323 1 310 1
324 306 311 0
325 1 311 1
326 307 312 0
327 1 312 1
328 307 252 5
329 307 303 4
330 307 253 5
331 303 313 0
332 1 313 1
333 308 314 0
334 1 314 1
335 308 303 5
336 256 315 0
337 1 315 1
353 353 352 0
354 1 352 1
355 354 353 0
356 1 353 1
357 355 354 0
358 1 354 1
359 353 355 0
360 1 355 1
361 353 356 0
266 254 253 0
362 1 356 1
363 203 253 6
364 355 253 7
365 354 253 8
366 255 303 6
367 306 303 7
368 203 303 8
369 304 303 9
370 103 303 10
371 305 303 11
372 355 303 12
373 256 303 13
374 303 303 14
375 253 303 15
376 353 303 16
377 354 303 17
255 53 253 3
378 355 252 6
379 304 252 7
380 353 252 8
381 303 252 9
382 3 252 10
383 103 252 11
384 255 252 12
385 305 252 13
386 308 252 14
387 203 252 15
388 354 252 16
389 306 252 17
390 253 252 18
391 356 357 0
392 1 357 1
403 403 402 0
404 1 402 1
453 304 253 9
454 353 253 10
455 308 253 11
456 305 253 12
457 403 253 13
458 253 253 14
459 3 253 15
460 303 253 16
461 306 253 17
462 103 253 18
463 356 253 19
464 303 254 2
465 403 303 18

Thanks,

Matt.

[mattc]

A few more details about the upgrade that I mentioned: I dumped the data using pg_dumpall v9.6 then read it back in with psql 9.6. When I tried to start up omero admin it said that it couldn't find one of the tables so I tried reading the dump in again. I thought that had done the trick until we started encountering this error. Can you see from the table output if that reread has corrupted the DB?

[mtbc]

Dear Matt,

Code: Select all

omero=> SELECT COUNT(*) FROM _roles;
count
-------
     1
(1 row)


There should only ever be one row in the _roles table. Have you multiple rows in it? If so, I would worry what else the re-read of the dump has done. You could try deleting all but one row from that table then restarting the server to see if that gets you any further along.

Cheers,
Mark

[mattc]

Thanks for your help. We did have too many roles so I recreated the database and read in the SQL again. That has sorted the issues we were seeing another problem now. I'll create a new topic.