Bug: broken upgrade of nightshade

[jamoore]

The re-indexing of the nightshade clone is currently stuck due to a bug in a very old upgrade script:

OMERO4__0/OMERO3A__11.sql:UPDATE annotation SET discriminator = '/basic/text/uri/' WHERE discriminator = '/basic/text/ur;/';

Having this invalid discriminator leads to:

2014-04-26 00:01:00,228 ERROR [             org.quartz.core.JobRunShell] (2-thread-4) Job DEFAULT.fullTextThreadRun threw an unhandled Exception:
org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'run' on target class [class ome.services.fulltext.FullTextThread] failed; nested exception is ome.conditions.ValidationException: Object with id: 56453 was not of the specified subclass: ome.model.annotations.Annotation (Discriminator: /basic/text/url/); nested exception is org.hibernate.WrongClassException: Object with id: 56453 was not of the specified subclass: ome.model.annotations.Annotation (Discriminator: /basic/text/url/)

In addition, even if the above line *had* worked properly, it would have left the entitytype column in eventlog invalid. Here's the list of classes that will lead to warnings if present in eventlog:

$ comm -2 -3 ns_event_classes current_classes 
ome.model.annotations.DatasetAnnotation
ome.model.annotations.ImageAnnotation
ome.model.annotations.UrlAnnotation
ome.model.containers.Category
ome.model.containers.CategoryGroup
ome.model.containers.CategoryGroupCategoryLink
ome.model.containers.CategoryImageLink
ome.model.core.PixelsDimensions
ome.model.display.Color

[mtbcarroll]

I don't know what needs to be done to fix this but I am happy to help with creating SQL scripts if I know what they are to do.

[jamoore]

So here's what it looks like:

• the typo above caused the discriminator for URLs not to be updated
• then this check failed to trigger:

  OMERO4.2__0/OMERO4.1__0.sql:          WHERE discriminator in (''/basic/text/uri/'', ''/basic/text/query/'', ''/type/Thumbnail/'');
  OMERO4.2__0/OMERO4.1__0.sql:        RAISE EXCEPTION ''ASSERTION ERROR: Found annotations of type: (query, thumbnail, or uri). Count=%'', rec.count;
  

That left nightshade with corrupt data.

Workaround:

omero_nightshade_upgraded=# select id, ns, textvalue from annotation where discriminator = '/basic/text/uri/';
  id   | ns |                              textvalue
-------+----+----------------------------------------------------------------------
 57970 |    | http://www.google.com
 56453 |    | http://www.designsforlifeproject.co.uk/pro_sciencecollaborators.html
 56903 |    | http://www.greatprotocols.com
(3 rows)

omero_nightshade_upgraded=# delete from imageannotationlink where child in (select id from annotation where discriminator = '/basic/text/uri/');
DELETE 4
omero_nightshade_upgraded=# delete from datasetannotationlink where child in (select id from annotation where discriminator = '/basic/text/uri/');
DELETE 1
omero_nightshade_upgraded=# delete from annotation where id in (select id from annotation where discriminator = '/basic/text/uri/');
DELETE 3

It's *conceivable* that someone else would have this issue, but highly unlikely.

A question for Kenny & JoshB: thoughts on how we could have caught the infinite loop and dealth with it better?

[jamoore]

Thanks, Mark. For the SQL side of things, I think the summary solution is:

• Remove all annotations (and their links) if the discriminator is .../uri or ../url since they should not exist.

J-m, sound correct?

[khgillen]

A question for Kenny & JoshB: thoughts on how we could have caught the
infinite loop and dealth with it better?

From my perspective, the process stuck 'percentage done' value would be a giveaway when manually looking, and Simon's bots jabbering away with repeating indexer exception in the logs were also perfectly adequate to pick it up. Maybe if the indexer logger could tail the indexer log for the last indexed ID, and if it matches the id of the current index attempt say [n] times, flag an 'ERROR: Stuck Indexer?'

[jamoore]

All good points, thanks, Kenny. Mark: when you're done with this, if you'll pass the ticket to me, I/we can take a look at improving the rest.

[mtbcarroll]

now I might be able to find it (-:

[mtbcarroll]

So, we should delete annotations and their links where the discriminator is /basic/text/uri/ or /basic/text/url/. This applies to all annotation types?

And, from ​https://github.com/openmicroscopy/openmicroscopy/pull/2369#issuecomment-41701620: where annotations are linked across groups, just delete the link, or do we have to delete the actual annotation too?

And, where an annotation X annotates an annotation Y that we have above chosen to delete, just delete the link, or do we have to delete the actual annotation X too?

(And what else am I forgetting to ask?)

[jamoore]

• all annotation types: yes
• linked across groups: there was likely a provision for deleting *some* of the annotations (if not cross linked, etc) initially, but for now I would say just remove the invalid links.
• which means we don't need to worry about deleting annotations on annotations: the link itself would be covered by the above rule.

[jamoore]

now I might be able to find it (-:

gh fail. sorry!

[mtbcarroll]

So this should go into a 5.0 → 5.1 upgrade script, or a separate utility script or something?

[jamoore]

I would think start in the upgrade script, and we can discuss an auto-fixer in startup.xml?

[mtbcarroll]

Sure.

No chance of these bad-discriminator annotations leaving behind an original file that also needs to be cleaned up?

[mtbcarroll]

This one doesn't really need to bump 5.1 patch level, but I'll assume this should go through the whole patch-bump breaking-queue process anyway.

[mtbcarroll]

Filed ​https://github.com/openmicroscopy/openmicroscopy/pull/2401 as first step. Does it look to do what was intended?

[mtbcarroll]

Also opened ​https://github.com/openmicroscopy/openmicroscopy/pull/2406 for the auto-fixer. Anything more to do on this ticket?

[jamoore]

There's the remaining "detect that the indexer is stuck" to think about.

[mtbcarroll]

Ah, should I try to dig into that, or pass to jballanco-x?

[jamoore]

The only thing you might try, Mark, is to keep a count of how often an event log ID has been tried and print that at WARN if 2 < x <10 and at ERROR beyond that. But it's also fine to pass to me for discussion with JB.

[mtbcarroll]

An infinite loop doesn't arise for me locally with,

omero admin reindex --class ome.model.annotations.Annotation

I simply get a,

org.hibernate.WrongClassException: Object with id: 1 was not of the specified subclass: ome.model.annotations.Annotation (Discriminator: /foo/)

Josh B, have you thoughts about what's going on and what should be done and how?

[Josh Moore <josh@…>]

(In [658db2581ebdfff576fa51b40c4113e2fd3a3bad/ome.git] on branch develop) Merge pull request #2401 from mtbc/trac-12242-bad-annotations

fix #12242: delete bad annotation links and annotations