Pharmacology API calls fail - estimated execution time -762693632

[stain]

In the Docker install, the target/pharmacology/pages API calls like http://heater.cs.man.ac.uk:3002/target/pharmacology/pages?uri=http%3A%2F%2Fwww.uniprot.org%2Funiprot%2FP30542 fail with:

Internal Server Error

Sorry, there was an internal error in serving this request, possibly due to an upstream server, or a configuration error.
The SPARQL endpoint used by this URI configuration did not return a successful response.

logs/puelia.log says the SPARQL query is:

PREFIX chembl: <http://rdf.ebi.ac.uk/terms/chembl#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX bibo: <http://purl.org/ontology/bibo/>
PREFIX cheminf: <http://semanticscience.org/resource/>
PREFIX obo: <http://purl.obolibrary.org/obo/>
PREFIX qudt: <http://qudt.org/1.1/schema/qudt#>
PREFIX chembl: <http://rdf.ebi.ac.uk/terms/chembl#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX bibo: <http://purl.org/ontology/bibo/>
PREFIX cheminf: <http://semanticscience.org/resource/>
PREFIX obo: <http://purl.obolibrary.org/obo/>
PREFIX qudt: <http://qudt.org/1.1/schema/qudt#>
SELECT DISTINCT ?item ?chembl_compound WHERE {  VALUES ?chembl_target_uri { <http://rdf.ebi.ac.uk/resource/chembl/target/CHEMBL2096679> <http://rdf.ebi.ac.uk/resource/chembl/target/CHEMBL2111329> <http://rdf.ebi.ac.uk/resource/chembl/target/CHEMBL226> <http://rdf.ebi.ac.uk/resource/chembl/target/CHEMBL2096982> <http://rdf.ebi.ac.uk/resource/chembl/target/CHEMBL2095195> <http://rdf.ebi.ac.uk/resource/chembl/target/CHEMBL2096908>  } GRAPH <http://www.ebi.ac.uk/chembl> {
 ?assay_uri chembl:hasTarget ?chembl_target_uri .
 ?assay_uri chembl:hasActivity ?item .
 ?item chembl:hasMolecule ?chembl_compound .
 ?chembl_target_uri a ?target_type .
 OPTIONAL { ?chembl_target_uri dcterms:title ?target_name_chembl }
 OPTIONAL { ?chembl_target_uri chembl:organismName ?target_organism }
 OPTIONAL { ?chembl_target_uri chembl:hasTargetComponent ?protein .
 GRAPH <http://www.conceptwiki.org> {
 ?cw_target skos:exactMatch ?protein ;
 skos:prefLabel ?protein_name
 }
 }
 OPTIONAL { ?assay_uri chembl:organismName ?assay_organism }
 OPTIONAL { ?assay_uri dcterms:description ?assay_description }
 OPTIONAL { ?assay_uri chembl:assayTestType ?assay_type }
 OPTIONAL { ?item chembl:publishedType ?published_type }
 OPTIONAL { ?item chembl:publishedRelation ?published_relation }
 OPTIONAL { ?item chembl:publishedValue ?published_value }
 OPTIONAL { ?item chembl:publishedUnits ?published_unit }
 OPTIONAL { ?item chembl:standardType ?activity_type }
 OPTIONAL { ?item chembl:standardRelation ?activity_relation }
 OPTIONAL { ?item chembl:standardValue ?standard_value .
 BIND (xsd:decimal(?standard_value) as ?activity_value)}
 OPTIONAL { ?item chembl:standardUnits ?activity_unit }
 OPTIONAL { ?item chembl:hasQUDT ?qudt_uri }
 OPTIONAL { ?item chembl:pChembl ?pChembl }
 OPTIONAL { ?item chembl:activityComment ?act_comment }
 OPTIONAL { ?item chembl:hasDocument ?doc_uri .
 OPTIONAL { ?doc_uri owl:sameAs ?doi }
 OPTIONAL { ?doc_uri bibo:pmid ?pmid }
 } 
}
OPTIONAL { GRAPH <http://ops.rsc.org> {
 ?ocrs_compound skos:exactMatch ?chembl_compound .
 ?ocrs_compound cheminf:CHEMINF_000396 ?inchi;
 cheminf:CHEMINF_000399 ?inchi_key;
 cheminf:CHEMINF_000018 ?smiles .
 OPTIONAL { [] obo:IAO_0000136 ?ocrs_compound ;
 a cheminf:CHEMINF_000484 ;
 qudt:numericValue ?molweight . }
 OPTIONAL { [] obo:IAO_0000136 ?ocrs_compound ;
 a cheminf:CHEMINF_000367;
 qudt:numericValue ?num_ro5_violations . }
 OPTIONAL { GRAPH <http://www.conceptwiki.org> {
 ?cw_compound skos:exactMatch ?ocrs_compound ;
 skos:prefLabel ?compound_name .
 } } 
} } } ORDER BY ?item  LIMIT 10 OFFSET 0

Trying this at the Virtuoso instance at http://heater.cs.man.ac.uk:3003/sparql I get the error:

Virtuoso 42000 Error The estimated execution time -762693632 (sec) exceeds the limit of 400 (sec).

I am not sure what a negative execution time means.. perhaps @ghard have any ideas?

(This is Virtuoso version 07.20.3212 - I'm updating the image to see if 7.2.1 does anything different)

[stain]

Updating to 07.20.3214-pthreads seems to fix it - but I am not sure if this is because I cleared the transaction log or the update :)

[stain]

Updating virtuoso only fixed the SPARQL results

But I still get error in apache:

api_1 | 130.88.195.126 - - [26/Oct/2015:16:37:55 +0000] "GET /target/pharmacology/pages?uri=http%3A%2F%2Fwww.uniprot.org%2Funiprot%2FP30542 HTTP/1.1" 404 1437 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:41.0) Gecko/20100101 Firefox/41.0"

Puelia logs now say:

Mon Oct 26 16:40:24 2015,283 [8] DEBUG Puelia - Looking in memcache for api-config-files/03_07_targetPharma_Count.ttl
Mon Oct 26 16:40:24 2015,284 [8] DEBUG Puelia - No cached version of ConfigGraph from api-config-files/03_07_targetPharma_Count.ttl
Mon Oct 26 16:40:24 2015,284 [8] DEBUG Puelia - Checking Config file: api-config-files/03_07_targetPharma_Count.ttl
Mon Oct 26 16:40:24 2015,353 [8] DEBUG Puelia - Caching api-config-files/03_07_targetPharma_Count.ttl
Mon Oct 26 16:40:24 2015,353 [8] DEBUG Puelia - Caching api-config-files/03_07_targetPharma_Count.ttl
Mon Oct 26 16:40:24 2015,356 [8] DEBUG Puelia - Looking in memcache for api-config-files/03_08_targetPharma_List.ttl
Mon Oct 26 16:40:24 2015,356 [8] DEBUG Puelia - No cached version of ConfigGraph from api-config-files/03_08_targetPharma_List.ttl
Mon Oct 26 16:40:24 2015,356 [8] DEBUG Puelia - Checking Config file: api-config-files/03_08_targetPharma_List.ttl
Mon Oct 26 16:40:24 2015,444 [8] DEBUG Puelia - Caching api-config-files/03_08_targetPharma_List.ttl
Mon Oct 26 16:40:24 2015,444 [8] DEBUG Puelia - Caching api-config-files/03_08_targetPharma_List.ttl
Mon Oct 26 16:40:24 2015,447 [8] DEBUG Puelia - Endpoint Uri Selected: http://www.openphacts.org/api#targetPharmacologyListEndpoint
Mon Oct 26 16:40:24 2015,448 [8] DEBUG Puelia - Endpoint Default Viewer is http://www.openphacts.org/api#targetPharmacologyListViewer
Mon Oct 26 16:40:24 2015,448 [8] DEBUG Puelia - Viewer URI: http://www.openphacts.org/api#targetPharmacologyListViewer
Mon Oct 26 16:40:24 2015,452 [8] INFO Puelia - SELECT Query:http://heater.cs.man.ac.uk:3002/target/pharmacology/pages?uri=http%3A%2F%2Fwww.uniprot.org%2Funiprot%2FP30542:  <<<

PREFIX chembl: <http://rdf.ebi.ac.uk/terms/chembl#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX bibo: <http://purl.org/ontology/bibo/>
PREFIX cheminf: <http://semanticscience.org/resource/>
PREFIX obo: <http://purl.obolibrary.org/obo/>
PREFIX qudt: <http://qudt.org/1.1/schema/qudt#>
PREFIX chembl: <http://rdf.ebi.ac.uk/terms/chembl#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX bibo: <http://purl.org/ontology/bibo/>
PREFIX cheminf: <http://semanticscience.org/resource/>
PREFIX obo: <http://purl.obolibrary.org/obo/>
PREFIX qudt: <http://qudt.org/1.1/schema/qudt#>
SELECT DISTINCT ?item ?chembl_compound WHERE {  VALUES ?chembl_target_uri {'No mappings found'} GRAPH <http://www.ebi.ac.uk/chembl> {
 ?assay_uri chembl:hasTarget ?chembl_target_uri .
 ?assay_uri chembl:hasActivity ?item .
 ?item chembl:hasMolecule ?chembl_compound .
 ?chembl_target_uri a ?target_type .
 OPTIONAL { ?chembl_target_uri dcterms:title ?target_name_chembl }
 OPTIONAL { ?chembl_target_uri chembl:organismName ?target_organism }
 OPTIONAL { ?chembl_target_uri chembl:hasTargetComponent ?protein .
 GRAPH <http://www.conceptwiki.org> {
 ?cw_target skos:exactMatch ?protein ;
 skos:prefLabel ?protein_name
 }
 }
 OPTIONAL { ?assay_uri chembl:organismName ?assay_organism }
 OPTIONAL { ?assay_uri dcterms:description ?assay_description }
 OPTIONAL { ?assay_uri chembl:assayTestType ?assay_type }
 OPTIONAL { ?item chembl:publishedType ?published_type }
 OPTIONAL { ?item chembl:publishedRelation ?published_relation }
 OPTIONAL { ?item chembl:publishedValue ?published_value }
 OPTIONAL { ?item chembl:publishedUnits ?published_unit }
 OPTIONAL { ?item chembl:standardType ?activity_type }
 OPTIONAL { ?item chembl:standardRelation ?activity_relation }
 OPTIONAL { ?item chembl:standardValue ?standard_value .
 BIND (xsd:decimal(?standard_value) as ?activity_value)}
 OPTIONAL { ?item chembl:standardUnits ?activity_unit }
 OPTIONAL { ?item chembl:hasQUDT ?qudt_uri }
 OPTIONAL { ?item chembl:pChembl ?pChembl }
 OPTIONAL { ?item chembl:activityComment ?act_comment }
 OPTIONAL { ?item chembl:hasDocument ?doc_uri .
 OPTIONAL { ?doc_uri owl:sameAs ?doi }
 OPTIONAL { ?doc_uri bibo:pmid ?pmid }
 } 
}
OPTIONAL { GRAPH <http://ops.rsc.org> {
 ?ocrs_compound skos:exactMatch ?chembl_compound .
 ?ocrs_compound cheminf:CHEMINF_000396 ?inchi;
 cheminf:CHEMINF_000399 ?inchi_key;
 cheminf:CHEMINF_000018 ?smiles .
 OPTIONAL { [] obo:IAO_0000136 ?ocrs_compound ;
 a cheminf:CHEMINF_000484 ;
 qudt:numericValue ?molweight . }
 OPTIONAL { [] obo:IAO_0000136 ?ocrs_compound ;
 a cheminf:CHEMINF_000367;
 qudt:numericValue ?num_ro5_violations . }
 OPTIONAL { GRAPH <http://www.conceptwiki.org> {
 ?cw_compound skos:exactMatch ?ocrs_compound ;
 skos:prefLabel ?compound_name .
 } } 
} } } ORDER BY ?item  LIMIT 10 OFFSET 0

>>>
Mon Oct 26 16:40:24 2015,458 [8] ERROR Puelia - EmptyResponseException: The selector did not find data in the triple store

Executing this query gives a new Virtuoso error:

Virtuoso 37000 Error SP031: SPARQL compiler: Variable 'chembl_compound' can not be bound due to mutually exclusive restrictions on its value

SPARQL query:
define sql:big-data-const 0 
#output-format:text/html
define sql:signal-void-variables 1 PREFIX chembl: <http://rdf.ebi.ac.uk/terms/chembl#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX bibo: <http://purl.org/ontology/bibo/>
PREFIX cheminf: <http://semanticscience.org/resource/>
PREFIX obo: <http://purl.obolibrary.org/obo/>
PREFIX qudt: <http://qudt.org/1.1/schema/qudt#>
PREFIX chembl: <http://rdf.ebi.ac.uk/terms/chembl#>
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX bibo: <http://purl.org/ontology/bibo/>
PREFIX cheminf: <http://semanticscience.org/resource/>
PREFIX obo: <http://purl.obolibrary.org/obo/>
PREFIX qudt: <http://qudt.org/1.1/schema/qudt#>
SELECT DISTINCT ?item ?chembl_compound WHERE {  VALUES ?chembl_target_uri {'No mappings found'} GRAPH <http://www.ebi.ac.uk/chembl> {
 ?assay_uri chembl:hasTarget ?chembl_target_uri .
 ?assay_uri chembl:hasActivity ?item .
 ?item chembl:hasMolecule ?chembl_compound .
 ?chembl_target_uri a ?target_type .
 OPTIONAL { ?chembl_target_uri dcterms:title ?target_name_chembl }
 OPTIONAL { ?chembl_target_uri chembl:organismName ?target_organism }
 OPTIONAL { ?chembl_target_uri chembl:hasTargetComponent ?protein .
 GRAPH <http://www.conceptwiki.org> {
 ?cw_target skos:exactMatch ?protein ;
 skos:prefLabel ?protein_name
 }
 }
 OPTIONAL { ?assay_uri chembl:organismName ?assay_organism }
 OPTIONAL { ?assay_uri dcterms:description ?assay_description }
 OPTIONAL { ?assay_uri chembl:assayTestType ?assay_type }
 OPTIONAL { ?item chembl:publishedType ?published_type }
 OPTIONAL { ?item chembl:publishedRelation ?published_relation }
 OPTIONAL { ?item chembl:publishedValue ?published_value }
 OPTIONAL { ?item chembl:publishedUnits ?published_unit }
 OPTIONAL { ?item chembl:standardType ?activity_type }
 OPTIONAL { ?item chembl:standardRelation ?activity_relation }
 OPTIONAL { ?item chembl:standardValue ?standard_value .
 BIND (xsd:decimal(?standard_value) as ?activity_value)}
 OPTIONAL { ?item chembl:standardUnits ?activity_unit }
 OPTIONAL { ?item chembl:hasQUDT ?qudt_uri }
 OPTIONAL { ?item chembl:pChembl ?pChembl }
 OPTIONAL { ?item chembl:activityComment ?act_comment }
 OPTIONAL { ?item chembl:hasDocument ?doc_uri .
 OPTIONAL { ?doc_uri owl:sameAs ?doi }
 OPTIONAL { ?doc_uri bibo:pmid ?pmid }
 } 
}
OPTIONAL { GRAPH <http://ops.rsc.org> {
 ?ocrs_compound skos:exactMatch ?chembl_compound .
 ?ocrs_compound cheminf:CHEMINF_000396 ?inchi;
 cheminf:CHEMINF_000399 ?inchi_key;
 cheminf:CHEMINF_000018 ?smiles .
 OPTIONAL { [] obo:IAO_0000136 ?ocrs_compound ;
 a cheminf:CHEMINF_000484 ;
 qudt:numericValue ?molweight . }
 OPTIONAL { [] obo:IAO_0000136 ?ocrs_compound ;
 a cheminf:CHEMINF_000367;
 qudt:numericValue ?num_ro5_violations . }
 OPTIONAL { GRAPH <http://www.conceptwiki.org> {
 ?cw_compound skos:exactMatch ?ocrs_compound ;
 skos:prefLabel ?compound_name .
 } } 
} } } ORDER BY ?item  LIMIT 10 OFFSET 0

[stain]

The clue is here:

 WHERE {  VALUES ?chembl_target_uri {'No mappings found'}

So actually my IMS instance was down - due to mySQL upgrade.. but the PHP API didn't fail over this.

mysql_1 | 2015-10-26T16:46:08.845539Z 4 [Note] Aborted connection 4 to db: 'ims' user: 'ims' host: '172.17.0.121' (Got an error reading communication packets)
ims_1 | com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'performance_schema.session_variables' doesn't exist