[BioMart Users] SQL syntax Error during query execution

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

[BioMart Users] SQL syntax Error during query execution

nndegwa
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
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

Syed Haider
Hi Nelson,

how do you compile this query from the interface/web service ?

Best,
Syed

On 26/09/2011 14:47, [hidden email] 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
> [hidden email]
> https://lists.biomart.org/mailman/listinfo/users
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

Junjun Zhang
In reply to this post by nndegwa
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, "[hidden email]" <[hidden email]> 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
>[hidden email]
>https://lists.biomart.org/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

nndegwa
In reply to this post by Syed Haider
Hi Syed,
Yes, this query comes from a canned query sent via web service.
Cheers,
Nelson


> Hi Nelson,
>
> how do you compile this query from the interface/web service ?
>
> Best,
> Syed
>
> On 26/09/2011 14:47, [hidden email] 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
>> [hidden email]
>> https://lists.biomart.org/mailman/listinfo/users
>


_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

nndegwa
In reply to this post by Junjun Zhang
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, "[hidden email]" <[hidden email]> 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
>>[hidden email]
>>https://lists.biomart.org/mailman/listinfo/users
>
>




_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

Syed Haider
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, [hidden email] 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, "[hidden email]"<[hidden email]>  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
>>> [hidden email]
>>> https://lists.biomart.org/mailman/listinfo/users
>>
>>
>
>
>
>
> _______________________________________________
> Users mailing list
> [hidden email]
> https://lists.biomart.org/mailman/listinfo/users
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

nndegwa
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, [hidden email] 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, "[hidden email]"<[hidden email]>  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
>>>> [hidden email]
>>>> https://lists.biomart.org/mailman/listinfo/users
>>>
>>>
>>
>>
>>
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> https://lists.biomart.org/mailman/listinfo/users
>


_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

Syed Haider
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, [hidden email] 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, [hidden email] 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, "[hidden email]"<[hidden email]>   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
>>>>> [hidden email]
>>>>> https://lists.biomart.org/mailman/listinfo/users
>>>>
>>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> Users mailing list
>>> [hidden email]
>>> https://lists.biomart.org/mailman/listinfo/users
>>
>
>
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] SQL syntax Error during query execution

nndegwa
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, [hidden email] 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, [hidden email] 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, "[hidden email]"<[hidden email]>   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
>>>>>> [hidden email]
>>>>>> https://lists.biomart.org/mailman/listinfo/users
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> Users mailing list
>>>> [hidden email]
>>>> https://lists.biomart.org/mailman/listinfo/users
>>>
>>
>>
>


_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users