Debugging biomart queries?

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

Debugging biomart queries?

Richard Cote
Hi mart-dev

Is there a way to access the raw sql queries that are being generated by a
biomart? I have a problem where the data is in the right place in the database,
the filters and attributes seem ok, but no data is coming out, which is very
frustrating and any help would be appreciated.

Cheers,
Rc

--
Richard Cote
Software Engineer - PRIDE Project Team (Sequence Database Group)
European Bioinformatics Institute
Wellcome Trust Genome Campus                 [hidden email]
Hinxton, Cambridge CB10 1SD                  Phone: (+44) 1223 492610
United Kingdom                               Fax  : (+44) 1223 494468

Reply | Threaded
Open this post in threaded view
|

Re: Debugging biomart queries?

nndegwa
Hi Richard,
If  you change the settings in biomart-perl/conf/log4perl.conf to set
log4perl.logger to DEBUG, then restart your server and check the
error_log in logs you should get some meaningful error messages, of
interest, the whole SQL statement that may be causing problems - helpful
to really check under the hood when there are problems.
Nelson



Richard Cote wrote:

> Hi mart-dev
>
> Is there a way to access the raw sql queries that are being generated
> by a biomart? I have a problem where the data is in the right place in
> the database, the filters and attributes seem ok, but no data is
> coming out, which is very frustrating and any help would be appreciated.
>
> Cheers,
> Rc
>

Reply | Threaded
Open this post in threaded view
|

Re: Debugging biomart queries?

Syed Haider
In reply to this post by Richard Cote
Hi Richard,

please modify the file biomart-perl/conf/log4perl.conf

and change it as shown below:

log4perl.logger  = DEBUG,Screen

then redo perl bin/configure.pl -r conf/yourRegistry.xml --clean

kill/restart apache.

now all the sql queries will appear in logs/error_log file.

HTH,
Syed

On 10/12/2010 11:11, Richard Cote wrote:

> Hi mart-dev
>
> Is there a way to access the raw sql queries that are being generated by a
> biomart? I have a problem where the data is in the right place in the database,
> the filters and attributes seem ok, but no data is coming out, which is very
> frustrating and any help would be appreciated.
>
> Cheers,
> Rc
>
Reply | Threaded
Open this post in threaded view
|

Re: Debugging biomart queries?

Leandro Hermida-2
Hi Richard,

To add to what Syed wrote one thing that sometimes can happen is you forget to create an necessary index during mart creation and for a large dataset when you run a query BioMart will seem to hang when in actuality it is waiting for the DBMS to return the results and the DBMS is going slowly because it is missing the index and having to do a table scan.

Take the SQL queries that you see BioMart running and with the MySQL or Pg client put EXPLAIN in front of the query and execute it, then it will show you what the DBMS is trying to do and you will see quickly if an index is missing.

-Leandro

On Tue, Dec 14, 2010 at 1:17 PM, Syed Haider <[hidden email]> wrote:
Hi Richard,

please modify the file biomart-perl/conf/log4perl.conf

and change it as shown below:

log4perl.logger  = DEBUG,Screen

then redo perl bin/configure.pl -r conf/yourRegistry.xml --clean

kill/restart apache.

now all the sql queries will appear in logs/error_log file.

HTH,
Syed

On 10/12/2010 11:11, Richard Cote wrote:
Hi mart-dev

Is there a way to access the raw sql queries that are being generated by a
biomart? I have a problem where the data is in the right place in the database,
the filters and attributes seem ok, but no data is coming out, which is very
frustrating and any help would be appreciated.

Cheers,
Rc