Upgrading from BASE 3.7 to 3.8 fails

[Nicklas Nordborg]

Stack trace:

[79%]    --Updating schema version: 131 -> 132...
15:28:50,009 ERROR Update:1800 - updateToSchemaVersion132: FAILED
org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
    at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:194)
    at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1373)
    at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:373)
    at net.sf.basedb.core.Update.updateToSchemaVersion132(Update.java:1784)
    at net.sf.basedb.core.Update.updateDatabase(Update.java:529)
    at net.sf.basedb.install.InitDB.main(InitDB.java:86)
Caused by: org.postgresql.util.PSQLException: 
  ERROR: update or delete on table "Annotations" violates 
    foreign key constraint "fk_r5upiflba3g19y4efwjic9kkm" on table "Annotations"
  Detail: Key (id)=(38555) is still referenced from table "Annotations".
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:461)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 6 more 

The schema update from 131->132 is related to #1992. The foreign key constraint violation is due to an inherited annotation that is referencing an annotation with empty values. The most likely solution to the problem is that the inherited annotation also should be removed.

It is not clear how this situation has appeared. The issue described in #1992 was first discovered during the implementation of ​http://baseplugins.thep.lu.se/ticket/525. Obviously there have been other cases in the past that has led to empty annotations being created. If that happened before #1941 (BASE 3.6) the inherited annotation could have been created due to changes made in [6910] and [6917] (which transformed inherited annotation sets to inherited annotations).

[Nicklas Nordborg]

I have made some investigations on the production server now. I found 2 integer and 434 string annotations with empty values. SQL commands used:

select a.*, v.value, ast.id, ast.item_id, ast.item_type, at.name
from "Annotations" a 
inner join "AnnotationTypes" at on at.id=a.annotationtype_id
inner join "AnnotationSets" ast on ast.id=a.annotationset_id

--left join "IntegerValues" v on v.id=a.value_id where at.value_type = 1
left join "StringValues" v on v.id=a.value_id where at.value_type = 5

and v.id is null and not a.value_id is null

Its only possible to check one type of values at a time, change 'left join...' part of the SQL accordingly. The other value types return 0 hits.

A second query is used to find out which annotations that are inheriting the empty annotations (once again, a separate query is needed for each value type):

select a.id from "Annotations" a where a.inherited_id in
(
  select a.id
  from "Annotations" a 
  inner join "AnnotationTypes" at on at.id=a.annotationtype_id
  left join "IntegerValues" v on v.id=a.value_id where at.value_type = 1
  and v.id is null and not a.value_id is null
)

This reveals that the integer annotations have been inherited to 4 items (2 each) and the string annotation to 186 items. All inherited annotations are from sample to raw bioassay. All items have a very low ID value and no entry_date value which indicates that they are older than #1166.

I guess it is safe to also let the update delete the inherited annotation entries that refer to the empty annotations.

[Nicklas Nordborg]

It is not trivial to create a test case for this issue since the core api will no longer allow you to insert data in this manner. In order to not spend too much time on this I decided to manually execute some SQL commands to create a similar scenario. We will create 1 annotation with empty values and 1 annotation that is inheriting from it.

1. Install BASE 3.7.2
2. Run the latest/test/roles/index.html test program so that the database fills up with some items.
3. Run the 3 SQL commands below and make a note of the ID values they return:

select annotationset_id as sid from "BioMaterials" where name='Sample A.ref'
select annotationset_id as rid from "RawBioAssays" where name='Raw bioassay A.00h'
select id as aid from "AnnotationTypes" where name='Time'

4. Run the SQL commands below to create the fake annotations. Susbstitute the <parameters> with the ID values from the 3 SQL statements above.

   insert into "ParameterValues" 
     (id, discriminator, version) 
     values (1, 1, 1)
   insert into "Annotations" 
     (id, version, annotationset_id, annotationtype_id, source, value_id) 
     values(1, 1, <sid>, <aid>, 0, 1)
   insert into "Annotations" 
     (id, version, annotationset_id, annotationtype_id, source, inherited_id)
     values(2, 1, <rid>, <aid>, 1, 1) 
   

5. Try running the upgrade to BASE 3.9. It should fail with the error from the ticket description.

[Nicklas Nordborg]

(In [7236]) Fixes #2044: Upgrading from BASE 3.7 to 3.8 fails

Note that fix is made in BASE 3.9 so the upgrade between BASE 3.7 and 3.8 will still not work. If this issue has been encountered the upgrade must go from BASE 3.7 to BASE 3.9.

(Manually added here since the commit message had incorrect ticket number)