New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

constraint violation in bioportal #576

Closed
mcodescu opened this Issue Jan 17, 2014 · 31 comments

Comments

Projects
None yet
7 participants
@mcodescu
Copy link

mcodescu commented Jan 17, 2014

With a number of ontologies in bioportal, e.g.
http://ontohub.org/repositories/bioportal/ontologies/300/entities?kind=Symbol
I get

failed: PG::CheckViolation: ERROR: new row for relation "ontologies" violates check constraint "logic_id_check"

@ghost ghost assigned 0robustus1 Jan 17, 2014

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Jan 17, 2014

retrying gives another error (a hets error about a duplicate ontology): http://ontohub.org/repositories/bioportal/ontologies/300/versions

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Jan 21, 2014

where do you see the Hets error? At the link that you have posted, I just see

PG::CheckViolation: ERROR: new row for relation "ontologies" violates check constraint "logic_id_check" : UPDATE "ontologies" SET "state" = 'done', "updated_at" = '2014-01-20 21:36:14.737632' WHERE "ontologies"."type" IN ('SingleOntology') AND "ontologies"."id" = 300
@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Jan 21, 2014

The hets error was solved with the latest update. Now we're back to the logic_id failure. Which actually has nothing to do with an error in the logic_id check.

The state updater tries to update the ontology to done, but also sets logic_id to nil...

I'm working on it.

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Jan 22, 2014

Ok small update:

First lets start off by saying: Yes this is a real issue and i can consistently reproduce it on my machine. However
if i trigger the parsing mechanism manually through some_ontology_version.parse in order to use some debugging tool like byebug the error disappears and the ontology is parsed correctly (with the logic being set).

And if that isn't enough: I have activated the full statement log for postgresql on my machine. I can find the
UPDATE "ontologies" SQL query where the logic_id is being set, and i can find the UPDATE "ontologies" which tries to set the state to done. However i cannot find a SQL query which sets the logic_id back to NULL
again. Also there is no other piece of code which sets the logic of an ontology than the one to be found in import.rb, which initially sets the logic - and works.

I will do some further investigation...

@corny

This comment has been minimized.

Copy link
Contributor

corny commented Jan 31, 2014

This is NOT related to Sidekiq. I can reproduce this exception in a rails console using the production environment on the server:
Ontology.find(300).versions.first.parse

@nning

This comment has been minimized.

Copy link
Contributor

nning commented Jan 31, 2014

I can not reproduce this issue on my development machine with PostgreSQL 9.3.2 on staging.

@corny

This comment has been minimized.

Copy link
Contributor

corny commented Jan 31, 2014

In case we need to update to a recent PostgreSQL version, here is the official repository:
http://www.postgresql.org/download/linux/ubuntu/

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Feb 2, 2014

Ok, when i first reproduced this error on my machine i was running PostgreSQL 9.2.4.

Now i've upgraded to 9.3.2 and migrated my database. I can still reproduce this error.

Then i've removed all my settings and databases and reinitialized the PostgreSQL database system:
Again i can reproduce this error. It might be interesting to investigate why this error
occurs on the deployment machines (Ubuntu) and on my Mac OS X machine, but not on @nning's machine.

@eugenk, @SGraef could you try to reproduce this error on your machines?
And @nning which ontology did you use, that didn't trigger the logic-failure?
I've worked with these ontologies:

Which both trigger the error (on my machine and on ontohub.org).

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Feb 6, 2014

Does this error only occur for ontologies with imports?

@nning

This comment has been minimized.

Copy link
Contributor

nning commented Feb 6, 2014

I tried the Cognitive Paradigm Ontology. And I tried it again on my desktop machine and it also worked. The Pathogen Transmission Ontology causes an exception to be raised in the OWL2Parser (java part of hets).

@nning

This comment has been minimized.

Copy link
Contributor

nning commented Feb 6, 2014

I will try a current version of Ubuntu with a current PostgreSQL version and report back.

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Feb 6, 2014

@nning You should update the OWL2Parser part of hets (i believe there was an update a few weeks ago).

@tillmo currently 47 bioportal ontologies are affected. I'm not sure if they all have imports (i can investigate), but i don't see how import and logic_id failure could be related.

@nning

This comment has been minimized.

Copy link
Contributor

nning commented Feb 6, 2014

@0robustus1 I get the same error with the most recent version of this java utilities. I ran make initialize_java, again.

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 9, 2014

@0robustus1, could you please send a description of the problem, including (the relevant parts of) a PostGres log, to a suitable PostGres mailing list?

0robustus1 referenced this issue Mar 10, 2014

unset ontology_id on setting state to failed
required to pass the logic_id_check
related to #608
@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 11, 2014

It seems that this issue is somehow caused by solr/sunspot. At least, if I comment out include Ontology::Searching from app/models/ontology.rb (and similarly for entities) and if I stop solr, then the error does not occur anymore.
Hence, I take back my accusing of Postgresql...

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Mar 11, 2014

The Sunspot part was new to me, but yesterday @eugenk and i did some investigating,
and isolated it to something related to the database-transaction block. (it worked when deactivating the transaction). It is possible that sunspot/solr indexing and transactions don't really mesh. I will investigate this further.

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Mar 11, 2014

Ok i tried to reproduce your solution @tillmo:
I applied this patch (commenting out the inclusion) on the staging branch on my local system:

diff --git a/app/models/entity.rb b/app/models/entity.rb
index f062c3d..17358e3 100644
--- a/app/models/entity.rb
+++ b/app/models/entity.rb
@@ -3,7 +3,7 @@ class Entity < ActiveRecord::Base
   extend Dagnabit::Vertex::Activation

   include Metadatable
-  include Entity::Searching
+  # include Entity::Searching
   include Entity::Readability

   belongs_to :ontology
diff --git a/app/models/ontology.rb b/app/models/ontology.rb
index 5e19e2b..3803361 100644
--- a/app/models/ontology.rb
+++ b/app/models/ontology.rb
@@ -20,7 +20,7 @@ class Ontology < ActiveRecord::Base
   include Ontology::Tasks
   include Ontology::LicenseModels
   include Ontology::FileExtensions
-  include Ontology::Searching
+  # include Ontology::Searching
   include GraphStructures::SpecificFetchers::Links

   # Multiple Class Features

Then i started sidekiq with script/start-sidekiq and the rails-server with bundle exec rails s.
(Note that i did not start sunspot/solr)
I wiped the database and seeded it again: rake db:migrate:clean && rake db:seed.
And then i uploaded the COGPO.owl into a brand-new repository.

It still failed with logic_id check failure.

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 11, 2014

Hm, strange, with the same ontology, I do not get this error (neither via sidekiq, nor via parse_full in the console). Actually, currently, I cannot reproduce the error at all. So indeed it may be that solr is not the point. Anyway, I must have found some way to circumvent the error... I thought it was disabling solr, but maybe it was something different...

@corny

This comment has been minimized.

Copy link
Contributor

corny commented Mar 14, 2014

I have just upgraded PostgreSQL on ontohub.org to version 9.3. bc499b6 updated the README.

# install the new postgres version
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
echo deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main > /etc/apt/sources.list.d/pgdg.list
apt-get update
apt-get install -y postgresql-9.1 postgresql-9.3

# upgrade the cluster
pg_dropcluster --stop 9.3 main
pg_upgradecluster 9.1 main

# clean up
pg_dropcluster 9.1 main
apt-get purge postgresql-9.1 postgresql-client-9.1
@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 21, 2014

new idea: use binary search with git-bisect to create a minimal ontology for which this error reproducably occurs.

@eugenk eugenk closed this Mar 24, 2014

@eugenk

This comment has been minimized.

Copy link
Member

eugenk commented Mar 24, 2014

I cannot reproduce this error on my machine anymore. On staging, COGPO.owl and PTRANS.obo don't fail. My postgresql version is 9.3.4 and my hets revision is r18553.

Sorry for closing this issue. I accidentally clicked on that button

@eugenk eugenk reopened this Mar 24, 2014

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 24, 2014

Have you tried foaf.owl?

@eugenk

This comment has been minimized.

Copy link
Member

eugenk commented Mar 24, 2014

I can't find foaf.owl on develop.ontohub.org and on ontohub.org. Where can I download this file?

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 24, 2014

@eugenk

This comment has been minimized.

Copy link
Member

eugenk commented Mar 25, 2014

this one passes as well.

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Mar 26, 2014

@eugenk could you provide us with some information regarding your testing setup?
(system, version of postgresql, ruby and solr (also java?), branch/commit, state of the database).

Also interestingly, foaf does not seem to fail on ontohub.org either. Instead it seems to suffer from the entities-and-sentences removed issue, also known as #664 or maybe even #655.

@eugenk

This comment has been minimized.

Copy link
Member

eugenk commented Mar 26, 2014

OS:
OS X 10.9.2

Postgres:
psql (PostgreSQL) 9.3.4

Ruby:
ruby 2.1.1p76 (2014-02-24 revision 45161) [x86_64-darwin13.0]

Solr (according to bundle show):

  • sunspot (2.0.0.pre.111215 122f7c6)
  • sunspot_rails (2.0.0.pre.111215 122f7c6)
  • sunspot_solr (2.0.0.pre.111215 122f7c6)

Java:
java version "1.6.0_65"
Java(TM) SE Runtime Environment (build 1.6.0_65-b14-462-11M4609)
Java HotSpot(TM) 64-Bit Server VM (build 20.65-b04-462, mixed mode)

Commit:
a1a90b3

Hets:
version of hets: v0.99, r18553

State of the database:
What shall I check here?

@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Mar 27, 2014

Actually state of the database meant: if you wiped the database before trying these changes. And when did you last created the whole database system (which postgres version).

However: I checked out staging (1b07203, two commits into the future from your commit), wiped the database and tried to reproduce the error.
However foaf as well as COGPO are correctly marked as done (including) entities. So this means
i too can't reproduce the error currently.

After that i tried parsing COGPO and foaf on ontohub.org (my ssh_test repository) and both failed.
Maybe we should take a look at the release notes of PostgreSQL 9.3.4 and 9.3.3 as they both talk about corrupted databases in relation to foreign-key constraints. It is possible that ontohub.org does suffer from this inconsistency and @eugenk's and my machine don't (at least not anymore).

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 27, 2014

What happens if you dump ontohub.org's database and read in the dump locally?

@SGraef SGraef referenced this issue Mar 29, 2014

Closed

Maybe fixing 576 #794

0robustus1 added a commit that referenced this issue Mar 29, 2014

use savepoint for nested sql transaction
This may be able to fix the #576 issue.

We assume that the problem was related to the fact that
the failing cycle-checker would fail the transaction
(as true nested transactions are not supported by PostgreSQL)
and the next query (the logic_id check, or now the projects query)
is still executed as part of the transaction-block. However the
transaction does not exist anymore so the system fails.

However `requires_new: true` will add a savepoint. So
if the cycle-check (the content of the transaction-block)
fails, it will only rollback to the savepoint, but the
transaction will still exist.
@0robustus1

This comment has been minimized.

Copy link
Contributor

0robustus1 commented Mar 29, 2014

Closed, probably due to this commit: 4a9fe3a.

@tillmo

This comment has been minimized.

Copy link
Member

tillmo commented Mar 30, 2014

see the commit: 4a9fe3a for a description of the cause of this bug and how to fix it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment