Hi Syed, Thanks for your reply: Here is where you can fire the query to: http://reactomedev.oicr.on.ca/cgi-bin/mart And the canned query you want to try is "All modified proteins in a pathway". The other canned queries are working fine. Let me know if you need further details. In the meantime, I will try your suggestions as well. Many thanks, Nelson
> Hi Nelson, not sure which server to post this query to. I tried > reactome.org:5555 but no luck. I guess the second dataset isnt there. > Anyhow, could you please do some detective work and start adding > attributes of second dataset one by one and see which one causes it > fail. Otherwise, let me have the URL of the server which has these > datasets configured so i can fire this query. > > Best, > Syed > > On 27/09/2011 16:35, [email protected] wrote: >> Hi Syed, >> Here is the XML equivalent of the query that works: >> >> http://pastebin.com/gcNrKnM8 >> >> Thanks, >> Nelson >> >>> Sorry Nelson, i might have missed few details, but could you send me >>> the >>> XML equivalent of the query ? >>> >>> >>> On 27/09/2011 15:20, [email protected] wrote: >>>> Hi Junjun& Syed, >>>> I still have problems with this earlier reported query. So I have >>>> tried >>>> to >>>> tweak a few parameters (listed below) as I generated error logs, >>>> hopefully >>>> these will be more useful to diagnosing the problem. >>>> >>>> You correctly pointed out the query involves a join between two >>>> datasets >>>> linked by exportables and importables each of which has 2 >>>> attributes/filters (based on ensembl gene and uniprot id attributes). >>>> >>>> This query works with an old version of our database but it throws the >>>> SQL >>>> syntax error with the new version of the database. >>>> I have tried to perform a few tests and saved the error_log files. I >>>> hope >>>> these will shed more light. >>>> >>>> You had mentioned that something might be missing before the 'AND' and >>>> this reminded me that there possibly might be a NULL value on one of >>>> the >>>> tables that could trigger an ERROR (I have encountered this before, >>>> and >>>> I >>>> got a patch from Damian on the FilterList.pm module to fix such a bug, >>>> he >>>> later committed this to the BioMart code). Since this mart is on a >>>> different server from the one that had the patch, I thought I would >>>> try >>>> testing with the patch as well. >>>> >>>> Like Syed had asked, the query in question is sent to the server via >>>> webservice. >>>> >>>> >>>> Some information about the error_logs from each test: >>>> >>>> ================================================= >>>> These are logs for the query when its not working >>>> ================================================= >>>> >>>> About error_log1: http://pastebin.com/3CKRzTvc >>>> 1. Deleted the two_variable_id exportable/importables both on EWAS >>>> and >>>> Pathway datasets. >>>> 2. Used new version db (v38_mart). >>>> 3. Query of interest not working. >>>> 4. Used FilterList.pm module that was altered by Damian. >>>> >>>> >>>> About error_log2: http://pastebin.com/ekVihAn9 >>>> 1. Deleted the two_variable_id exportable/importables both on EWAS >>>> and >>>> Pathway datasets. >>>> 2. Used new version db (v38_mart). >>>> 3. Query of interest not working. >>>> 4. Used original FilterList.pm module. >>>> >>>> >>>> About error_log3: http://pastebin.com/ZLagmDQh >>>> 1. Has two_variable_id exportable/importables both on EWAS and >>>> Pathway >>>> datasets. >>>> 2. Used new version db (v38_mart). >>>> 3. Query of interest not working. >>>> 4. Used FilterList.pm module that was altered by Damian. >>>> >>>> ========================================== >>>> These are logs for the query when it works >>>> ========================================== >>>> >>>> About error_log4: http://pastebin.com/prAgP3vS >>>> 1. Has two_variable_id exportable/importables both on EWAS and >>>> Pathway >>>> datasets. >>>> 2. Used old version db (v35_mart). >>>> 3. Query of interest Works. >>>> 4. Used original FilterList.pm module. >>>> >>>> >>>> About error_log5: http://pastebin.com/gS8HghkT >>>> 1. Has two_variable_id exportable/importables both on EWAS and >>>> Pathway >>>> datasets. >>>> 2. Used old version db (v35_mart). >>>> 3. Query of interest Works. >>>> 4. Used FilterList.pm module that was altered by Damian. >>>> >>>> >>>> >>>> Thanks, >>>> Nelson >>>> >>>> >>>> >>>> >>>> >>>>> This SQL doesn't look correct, something is missing right before the >>>>> 'AND' >>>>> for the part that is joined with 'OR', with the exception of the last >>>>> item >>>>> as below: >>>>> >>>>> ((pathway__referencepeptidesequence__dm.referencedatabase_uniprot = >>>>> 'P49327') AND >>>>> (pathway__referencednasequence__dm.referencedatabase_ensembl >>>>> = 'ENSG00000169710')) >>>>> >>>>> >>>>> It looks like the original query involves join between two datasets. >>>>> Usually that is not a problem when the link defined as an >>>>> exportable/importable pair, in which exportable contains one >>>>> attribute >>>>> (eg, ensembl_gene) and importable contains one filter (eg, >>>>> ensembl_gene). >>>>> The piece of SQL statement shown above makes me thinking that the >>>>> link >>>>> you >>>>> defined seemed to have two attributes and two filters (ensembl gene >>>>> and >>>>> uniprot id) for its exportable and importable. >>>>> >>>>> Although it is legitimate to create such a link, but I am afraid it >>>>> is >>>>> not >>>>> going to work in most of the cases. It works only when *one* pair of >>>>> the >>>>> values is exported from one dataset and imported to the other >>>>> dataset. >>>>> The >>>>> 'band' filter works exactly like this in Ensembl gene mart, user >>>>> specifies >>>>> *one* band to a hidden dataset which exports *one* pair (ie, one row) >>>>> of >>>>> 'chromosome' and 'coordinate' value (eg, 1, 10000303), and >>>>> ensembl_gene >>>>> dataset imports this pair and stick it in to SQL that queries against >>>>> it's >>>>> own db. >>>>> >>>>> The confirm this, you can double check the exportable/importable >>>>> settings >>>>> in your configuration. >>>>> >>>>> Hope this helps. Let us know if you have any further questions. >>>>> >>>>> Best regards, >>>>> >>>>> Junjun >>>>> >>>>> >>>>> >>>>> >>>>> On 11-09-26 9:47 AM, "[email protected]"<[email protected]> wrote: >>>>> >>>>>> Hi, >>>>>> I am getting an error from one of my queries along the lines of SQL >>>>>> syntax >>>>>> being the cause, am not sure what the real problem is though. I set >>>>>> the >>>>>> log4perl.conf to debug mode and I got back a more elaborate error >>>>>> report >>>>>> http://pastebin.com/9rqKBfWR >>>>>> Anyone know what the problem is? >>>>>> Thanks, >>>>>> Nelson >>>>>> >>>>>> Serious Error: Error during query execution: You have an error in >>>>>> your >>>>>> SQL >>>>>> syntax; check the manual that corresponds to your MySQL server >>>>>> version >>>>>> for >>>>>> the right syntax to use near 'AND >>>>>> (pathway__referencednasequence__dm.referencedatabase_ensembl = >>>>>> 'ENSG00000166' at line 1 >>>>>> >>>>>> ERROR: caught BioMart::Exception::Database: Error during query >>>>>> execution: >>>>>> You have an error in your SQL syntax; check the manual that >>>>>> corresponds >>>>>> to >>>>>> your MySQL server version for the right syntax to use near 'AND >>>>>> (pathway__referencednasequence__dm.referencedatabase_ensembl = >>>>>> 'ENSG00000166' at line 1 >>>>>> >>>>>> _______________________________________________ >>>>>> Users mailing list >>>>>> [email protected] >>>>>> https://lists.biomart.org/mailman/listinfo/users >>>>> >>>>> >>>> >>>> >>>> >>>> >>>> _______________________________________________ >>>> Users mailing list >>>> [email protected] >>>> https://lists.biomart.org/mailman/listinfo/users >>> >> >> > _______________________________________________ Users mailing list [email protected] https://lists.biomart.org/mailman/listinfo/users
