• Views
  • Iteration Report
  • My Iteration Report
  •  
OMERO.server
  • Login
  • Help/Guide
  • About Trac
  • Preferences
  • Wiki
  • Timeline
  • Roadmap
  • Browse Source
  • View Tickets
  • Search

Context Navigation

  • ← Previous Ticket
  • Next Ticket →

Ticket #1056 (closed defect: fixed)

Opened 3 months ago

Last modified 3 months ago

Database upgrade OMERO3A__9 breaks IAdmin.setDefaultGroup, et al.

Reported by: jmoore Owned by: jmoore
Priority: blocker Milestone: 3.0-Beta3.1
Component: Services Version: 3.0-M1
Keywords: dbupgrade, hibernate Cc: atarkowska, callan

Description (last modified by jmoore) (diff)

Hibernate does not properly handle re-ordering of lists like Pixels->Channels or Experimenter->GroupExperimenterMap when there is a unique constraint on (parent, parent_index) as OMERO3A__9 added for performance reasons (#1047).

See HHH-3160 for more information. Currently looking for a workaround.

Change History

Changed 3 months ago by jmoore

  • description modified (diff) 
41916      [      main] INFO           ome.services.util.ServiceHandler  -  Meth:	interface ome.api.IAdmin.setDefaultGroup
41916      [      main] INFO           ome.services.util.ServiceHandler  -  Args:	[ome.model.meta.Experimenter:Id_6912, ome.model.meta.ExperimenterGroup:Id_3663]
41982      [      main] INFO            ome.security.basic.EventHandler  -  Auth:	user=0,group=0,event=134007(Test)
43554      [      main] ERROR  org.hibernate.util.JDBCExceptionReporter  - ERROR: duplicate key violates unique constraint "groupexperimentermap_child_key"
43584      [      main] ERROR  .event.def.AbstractFlushingEventListener  - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: could not update collection rows: [ome.model.meta.Experimenter.groupExperimenterMap#6912]
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.persister.collection.OneToManyPersister.doUpdateRows(OneToManyPersister.java:285)
	at org.hibernate.persister.collection.AbstractCollectionPersister.updateRows(AbstractCollectionPersister.java:1519)
	at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:65)
	at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:170)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
	at org.springframework.orm.hibernate3.HibernateTemplate$27.doInHibernate(HibernateTemplate.java:818)
	at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
	at org.springframework.orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:816)
	at ome.logic.UpdateImpl.afterUpdate(UpdateImpl.java:287)
	at ome.logic.UpdateImpl.doAction(UpdateImpl.java:298)
	at ome.logic.UpdateImpl.saveAndReturnObject(UpdateImpl.java:158)
	at ome.logic.AdminImpl$SecureUpdate.updateObject(AdminImpl.java:133)
	at ome.security.basic.BasicSecuritySystem.doAction(BasicSecuritySystem.java:433)
	at ome.logic.AdminImpl.setDefaultGroup(AdminImpl.java:643)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at ome.security.basic.EventHandler.invoke(EventHandler.java:103)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:111)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at ome.tools.hibernate.ProxyCleanupFilter$Interceptor.invoke(ProxyCleanupFilter.java:169)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at ome.services.util.ServiceHandler.invoke(ServiceHandler.java:86)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy57.setDefaultGroup(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at ome.server.itests.LoginInterceptor.invoke(LoginInterceptor.java:35)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy57.setDefaultGroup(Unknown Source)
	at ome.server.itests.sec.AdminTest.testSetDefaultGroupThrowsDuplicateKey(AdminTest.java:556)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.testng.internal.MethodHelper.invokeMethod(MethodHelper.java:580)
	at org.testng.internal.Invoker.invokeMethod(Invoker.java:478)
	at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:617)
	at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:885)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:126)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:110)
	at org.testng.TestRunner.runWorkers(TestRunner.java:712)
	at org.testng.TestRunner.privateRun(TestRunner.java:582)
	at org.testng.TestRunner.run(TestRunner.java:477)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:324)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:319)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:292)
	at org.testng.SuiteRunner.run(SuiteRunner.java:198)
	at org.testng.TestNG.createAndRunSuiteRunners(TestNG.java:823)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:790)
	at org.testng.TestNG.run(TestNG.java:708)
	at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:73)
	at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:124)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "groupexperimentermap_child_key"
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:307)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
	at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
	at org.hibernate.persister.collection.OneToManyPersister.doUpdateRows(OneToManyPersister.java:259)
	... 70 more

Changed 3 months ago by jmoore

  • status changed from new to assigned

I've found a workaround via Postgres triggers (necessary since Postgres doesn't yet support deferred unique constraints, the suggested workaround from HHH-3160): 

  CREATE OR REPLACE FUNCTION groupexperimentermap_child_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.child = old.child AND new.child_index = old.child_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM groupexperimentermap
       WHERE child = new.child AND child_index = new.child_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping groupexperimentermap % via (-1 - oldvalue )'', duplicate;
          UPDATE groupexperimentermap SET child_index = -1 - child_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER groupexperimentermap_child_index_trigger
        BEFORE UPDATE ON groupexperimentermap
        FOR EACH ROW EXECUTE PROCEDURE groupexperimentermap_child_index_move ();

This can become OMERO3A__11 but JDBC can't handle this syntax, and so our current "ant setup-db" routine is no longer viable.

Currently looking into replacing with python.

Changed 3 months ago by jmoore

r2783 fixes this. Apparently, the Postgres JDBC driver separates all statements on semicolons and passes them individually to database. The solution for #1047 includes plpgsql stored procedures which themselves have ";"'s leading to syntax exceptions.

Though this is something of a hack, I've changed ant's setup-db target to use ";;" as a delimiter which allows views.sql to be loaded. This means that each file is being loaded as one statement: schema.sql, data.sql, and views.sql. This may cause issues on some Postgres versions, but on 8.3 it is now working.

Testing needed on other versions.

Changed 3 months ago by atarkowska 

sql_run] Executing resource: /Users/Ola/J2EP/OMERO/dist/target/views.sql
  [sql_run] Failed to execute:    BEGIN;  DROP TABLE (...)

BUILD FAILED org.postgresql.util.PSQLException: ERROR: language "plpgsql" does not exist

Changed 3 months ago by jmoore

If this is related to the problem in your email, Ola: 

Ola:~/J2EP/OMERO/dist Ola$ dropdb omero3 && createdb omero3
DROP DATABASE
CREATE DATABASE
Ola:~/J2EP/OMERO/dist Ola$ createlang plpgsql omero3
Ola:~/J2EP/OMERO/dist Ola$ psql omero3 < sql/psql/OMERO3A__11/views.sql
BEGIN
ERROR:  table "count_plate_screenlinks_by_owner" does not exist
ERROR:  current transaction is aborted, commands ignored until end of  
transaction block
(...)
ROLLBACK

then the issue is that you didn't run schema.sql and data.sql, when doing this manually.

But with r2783, you can return to using ant setup-db just that createlang is now necessary: 

  dropdb omero3
  createdb omero3
  createlang plpgsql omero3
  ant setup-db

Changed 3 months ago by atarkowska

ok, it works, my mistake

Changed 3 months ago by jmoore

  • status changed from assigned to closed
  • resolution set to fixed

Now with several of us using this, I'll consider it fixed. Let's open up new tickets for any particular performance or regression issues.

Note: See TracTickets for help on using tickets.

Download in other formats:

  • Comma-delimited Text
  • Tab-delimited Text
  • RSS Feed
Trac Powered

Powered by Trac 0.11
By Edgewall Software.

Visit the Trac open source project at
http://trac.edgewall.org/