On Fri, Jul 17, 2009 at 7:46 AM, Darren Oakley <[email protected]> wrote:
> 2009/07/17 15:45:56 BioMart.Web:449:INFO> Restoring existing session
> 5b5d2aef618096f01d122bd0a82f7fe6
> 2009/07/17 15:45:56 BioMart.QueryRunner:169:WARN> NO ATTRIBUTES
> 2009/07/17 15:45:56 BioMart.QueryRunner:179:WARN> NO FILTERS
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:852:INFO> MAIN TABLE:
> kermits__emi_clone__main and i IS 0
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:906:INFO> COUNT SQL: SELECT
> COUNT(*) FROM htgt_mart_alt.kermits__emi_clone__main main
> 2009/07/17 15:45:56 BioMart.QueryRunner:169:WARN> NO ATTRIBUTES
> 2009/07/17 15:45:56 BioMart.QueryRunner:174:WARN> FILTER TABLE: kermits
> status_code main
> 2009/07/17 15:45:56 BioMart.QueryRunner:174:WARN> FILTER TABLE: kermits
> centre main
>
Oooh - thats a big bug in the BioMart code. Strange it has not bitten anyone
before. Basically if two different main table filters are used for a 3 main
table mart the below code is likely to screw up.
else{
my $table = $filter->table;
$tables{$table} = 1;
if (!(($table =~ /main$/) && ($filter->attribute->key eq
($self->get('keys')->[0])))){
$joinTables{$table} = $filter->attribute->key;
}
}
i.e. for Darrens example it is storing $joinTables{'main'} = id_103_key and
all information about the id_101_key for the other main table is lost.
The fix is actually in the main query generation code already (somehow never
got propogated to the counts).
if ($table eq 'main'){
my $keys = $self->get('keys');
foreach my $key (reverse @$keys){
last if (uc($joinTables{'main'}) eq uc($key));
if (uc($filter->attribute->key) eq uc($key)){
$joinTables{'main'} = $key;
last;
}
}
}
else{# dm table
$joinTables{$table} = $filter->attribute->key;
}
needs to go in instead of
if (!(($table =~ /main$/) && ($filter->attribute->key eq
($self->get('keys')->[0])))){
$joinTables{$table} = $filter->attribute->key;
}
Does someone on the dev team want to test this and commit
Cheers
Damian
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:834:INFO> KEY: id_101_key
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:836:INFO> KEY TO MATCH:
> id_103_key
>
the match here does not occur as the hash key on main has lost the fact that
we are using id_101_key.
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:834:INFO> KEY: id_103_key
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:836:INFO> KEY TO MATCH:
> id_103_key
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:852:INFO> MAIN TABLE:
> kermits__emi_event__main and i IS 1
> 2009/07/17 15:45:56 BioMart.Dataset.TableSet:906:INFO> COUNT SQL: SELECT
> COUNT(DISTINCT main.id_102_key) FROM htgt_mart_alt.kermits__emi_event__main
> main WHERE (main.status_code = 'GC') AND (main.centre = 'WTSI')
> 2009/07/17 15:45:56 martview:142:ERROR> ERROR: caught BioMart::Exception:
> non-BioMart die(): Can't use an undefined value as an ARRAY reference at
> /software/team87/biomart/biomart-0_7-prod/lib/BioMart/Dataset/TableSet.pm
> line 917.
>
> Stacktrace:
> Exception::Class::Base::new
> /software/team87/biomart/biomart-0_7-prod/cgi-bin/martview:125
>
> ModPerl::ROOT::ModPerl::Registry::software_team87_biomart_biomart_2d0_7_2dprod_cgi_2dbin_martview::handler
>
> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:204
> (eval)
>
> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:204
> ModPerl::RegistryCooker::run
>
> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:170
> ModPerl::RegistryCooker::default_handler
>
> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/Registry.pm:31
> ModPerl::Registry::handler -e:0
> (eval) -e:0
> 2009/07/17 15:45:56 BioMart.Web:264:INFO> START PROCESSING TEMPLATE
> error.tt
> 2009/07/17 15:45:56 BioMart.Web:271:INFO> !!!! 0 to get process template
> error.tt and print to string
>
> On 17 Jul 2009, at 15:42, Damian Smedley wrote:
>
> so its cycling through the tables and keys in the correct reverse order but
> somehow not picking up the fact that status uses id_101_key. It should be
> bailing out of the loop at that stage. Can you put in one more logger
> statement in the nested loop (see below) - sorry, should have got you to do
> that straight away.
>
> OUTER:foreach my $key (reverse @$keys){
> $logger->info("KEY: $key");
> foreach my $join_table (keys %joinTables){
> $logger->info("KEY TO MATCH: $joinTables{$join_table}");
> if (uc($joinTables{$join_table}) eq uc($key)){
> last OUTER;
> }
> }
> $i--;
>
>
>
> On Fri, Jul 17, 2009 at 7:34 AM, Darren Oakley <[email protected]> wrote:
>
>> Hi Damian,
>>
>> Here's the output:
>>
>> 2009/07/17 15:33:21 BioMart.Web:271:INFO> !!!! 0.07 to get process
>> template main.tt and print to SCALAR
>> 2009/07/17 15:33:31 BioMart.Web:449:INFO> Restoring existing session
>> c572e58e911da4fc2a47d5e28c8f437c
>> 2009/07/17 15:33:31 BioMart.Web:449:INFO> Restoring existing session
>> c572e58e911da4fc2a47d5e28c8f437c
>> 2009/07/17 15:33:31 BioMart.QueryRunner:169:WARN> NO ATTRIBUTES
>> 2009/07/17 15:33:31 BioMart.QueryRunner:179:WARN> NO FILTERS
>> 2009/07/17 15:33:31 BioMart.Dataset.TableSet:851:INFO> MAIN TABLE:
>> kermits__emi_clone__main and i IS 0
>> 2009/07/17 15:33:31 BioMart.Dataset.TableSet:905:INFO> COUNT SQL: SELECT
>> COUNT(*) FROM htgt_mart_alt.kermits__emi_clone__main main
>> 2009/07/17 15:33:31 BioMart.QueryRunner:169:WARN> NO ATTRIBUTES
>> 2009/07/17 15:33:31 BioMart.QueryRunner:174:WARN> FILTER TABLE: kermits
>> status_code main
>> 2009/07/17 15:33:31 BioMart.QueryRunner:174:WARN> FILTER TABLE: kermits
>> centre main
>> 2009/07/17 15:33:31 BioMart.Dataset.TableSet:834:INFO> KEY: id_101_key
>> 2009/07/17 15:33:31 BioMart.Dataset.TableSet:834:INFO> KEY: id_103_key
>> 2009/07/17 15:33:31 BioMart.Dataset.TableSet:851:INFO> MAIN TABLE:
>> kermits__emi_event__main and i IS 1
>> 2009/07/17 15:33:31 BioMart.Dataset.TableSet:905:INFO> COUNT SQL: SELECT
>> COUNT(DISTINCT main.id_102_key) FROM htgt_mart_alt.kermits__emi_event__main
>> main WHERE (main.status_code = 'GC') AND (main.centre = 'WTSI')
>> 2009/07/17 15:33:31 martview:142:ERROR> ERROR: caught BioMart::Exception:
>> non-BioMart die(): Can't use an undefined value as an ARRAY reference at
>> /software/team87/biomart/biomart-0_7-prod/lib/BioMart/Dataset/TableSet.pm
>> line 916.
>>
>> Stacktrace:
>> Exception::Class::Base::new
>> /software/team87/biomart/biomart-0_7-prod/cgi-bin/martview:125
>>
>> ModPerl::ROOT::ModPerl::Registry::software_team87_biomart_biomart_2d0_7_2dprod_cgi_2dbin_martview::handler
>>
>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:204
>> (eval)
>>
>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:204
>> ModPerl::RegistryCooker::run
>>
>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:170
>> ModPerl::RegistryCooker::default_handler
>>
>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/Registry.pm:31
>> ModPerl::Registry::handler -e:0
>> (eval) -e:0
>> 2009/07/17 15:33:31 BioMart.Web:264:INFO> START PROCESSING TEMPLATE
>> error.tt
>> 2009/07/17 15:33:31 BioMart.Web:271:INFO> !!!! 0 to get process template
>> error.tt and print to string
>>
>>
>>
>> On 17 Jul 2009, at 15:12, Damian Smedley wrote:
>>
>>
>> in the code in TableSet.pm around like 822 (in the _getCount function) you
>> need to add a couple of extra logger calls to sort this out. Let us know
>> what output you get
>>
>> # identify the lowest key and set main accordingly
>> my $keys = $self->get('keys');
>> if (%joinTables){
>>
>> $i = scalar @$keys - 1;
>> OUTER:foreach my $key (reverse @$keys){
>> $logger->info("KEY: $key");
>> foreach my $join_table (keys %joinTables){
>> if (uc($joinTables{$join_table}) eq uc($key)){
>> last OUTER;
>> }
>> }
>> $i--;
>> }
>> }
>> else{
>> $i = 0;# for when no join tables
>> }
>>
>>
>>
>> my $mains = $self->get('mains');
>> $main = $$mains[$i];
>>
>> $logger->info("MAIN TABLE: $main and i IS $i");
>>
>>
>> On Fri, Jul 17, 2009 at 5:30 AM, Darren Oakley <[email protected]> wrote:
>>
>>> Hmmm,
>>>
>>> This is strange... Running
>>>
>>> SELECT COUNT(DISTINCT main.id_102_key) FROM
>>> htgt_mart.kermits__emi_event__main main WHERE (main.status_code = 'GC') AND
>>> (main.centre = 'WTSI')
>>>
>>>
>>> on the database actually throws an error:
>>>
>>> Unknown column 'main.status_code' in 'where clause'
>>>
>>>
>>> as the mart is looking at the wrong table. The attribute 'status_code'
>>> is only on the last table in the sequence 'kermits__emi_attempt__main' (the
>>> main tables in our mart go: clone -> event -> attempt). Why would it be
>>> looking at the wrong table? I've attached my MartEditor xmls for this
>>> dataset in case this might help...
>>>
>>>
>>> -- The Wellcome Trust Sanger Institute is operated by Genome Research
>>> Limited, a charity registered in England with number 1021457 and a company
>>> registered in England with number 2742969, whose registered office is 215
>>> Euston Road, London, NW1 2BE.
>>>
>>>
>>>
>>> The primary keys for the main tables are:
>>>
>>> clone -> id_102_key
>>> event -> id_103_key
>>> attempt -> id_101_key
>>>
>>> Cheers,
>>>
>>> Daz
>>>
>>>
>>> On 17 Jul 2009, at 12:39, Damian Smedley wrote:
>>>
>>> Hi Darren,
>>>
>>> What does that SQL do when you run it direct on the server - it looks
>>> fine but seems like $ret = ${$sth->fetchrow_arrayref}[0]; is giving the
>>> error i.e. no rows are coming back
>>>
>>> Cheers
>>> Damian
>>>
>>>
>>> On Fri, Jul 17, 2009 at 4:10 AM, Darren Oakley <[email protected]> wrote:
>>>
>>>> Hi Syed,
>>>>
>>>> Here's the dump from my log file:
>>>>
>>>> 2009/07/17 12:07:53 BioMart.Web:449:INFO> Restoring existing session
>>>> d2e13b828c9c73a18e2b0d1808c66e8c
>>>> 2009/07/17 12:07:53 BioMart.QueryRunner:169:WARN> NO ATTRIBUTES
>>>> 2009/07/17 12:07:53 BioMart.QueryRunner:179:WARN> NO FILTERS
>>>> 2009/07/17 12:07:53 BioMart.Dataset.TableSet:900:INFO> COUNT SQL:
>>>> SELECT COUNT(*) FROM htgt_mart.kermits__emi_clone__main main
>>>> 2009/07/17 12:07:53 BioMart.QueryRunner:169:WARN> NO ATTRIBUTES
>>>> 2009/07/17 12:07:53 BioMart.QueryRunner:174:WARN> FILTER TABLE: kermits
>>>> status_code main
>>>> 2009/07/17 12:07:53 BioMart.QueryRunner:174:WARN> FILTER TABLE: kermits
>>>> centre main
>>>> 2009/07/17 12:07:53 BioMart.Dataset.TableSet:900:INFO> COUNT SQL:
>>>> SELECT COUNT(DISTINCT main.id_102_key) FROM
>>>> htgt_mart.kermits__emi_event__main main WHERE (main.status_code
>>>> = 'GC') AND (main.centre = 'WTSI')
>>>> 2009/07/17 12:07:53 martview:142:ERROR> ERROR: caught
>>>> BioMart::Exception: non-BioMart die(): Can't use an undefined value as an
>>>> ARRAY reference at /software/team87/biomart/bio
>>>> mart-0_7-prod/lib/BioMart/Dataset/TableSet.pm line 911.
>>>>
>>>> Stacktrace:
>>>> Exception::Class::Base::new
>>>> /software/team87/biomart/biomart-0_7-prod/cgi-bin/martview:125
>>>>
>>>> ModPerl::ROOT::ModPerl::Registry::software_team87_biomart_biomart_2d0_7_2dprod_cgi_2dbin_martview::handler
>>>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-threa
>>>> d-multi/ModPerl/RegistryCooker.pm:204
>>>> (eval)
>>>>
>>>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:204
>>>> ModPerl::RegistryCooker::run
>>>>
>>>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/RegistryCooker.pm:170
>>>> ModPerl::RegistryCooker::default_handler
>>>>
>>>> /software/team87/HTGT/perl/lib/site_perl/5.8.8/x86_64-linux-thread-multi/ModPerl/Registry.pm:31
>>>> ModPerl::Registry::handler -e:0
>>>> (eval) -e:0
>>>> 2009/07/17 12:07:53 BioMart.Web:264:INFO> START PROCESSING TEMPLATE
>>>> error.tt
>>>> 2009/07/17 12:07:53 BioMart.Web:271:INFO> !!!! 0.03 to get process
>>>> template error.tt and print to string
>>>>
>>>> Cheers,
>>>>
>>>> Daz
>>>>
>>>>
>>>> On 17 Jul 2009, at 11:30, Syed Haider wrote:
>>>>
>>>> Hi Darren,
>>>>>
>>>>> when you hit the count button, please see what goes in the error_log.
>>>>> you would need to switch on the logging to INFO from FATAL in
>>>>> conf/log4perl.conf and reconfigure/restart.
>>>>>
>>>>> Cheers
>>>>> Syed
>>>>>
>>>>>
>>>>> Darren Oakley wrote:
>>>>>
>>>>>> Hi All,
>>>>>> Sorry to disturb, but I'm getting an error which I just can't figure
>>>>>> out when doing a count on one of our Marts...
>>>>>> Here's the URL for the search:
>>>>>>
>>>>>> http://www.sanger.ac.uk/htgt/biomart/martview?VIRTUALSCHEMANAME=default&ATTRIBUTES=kermits.default.attributes.sponsor|kermits.default.attributes.marker_symbol|kermits.default.attributes.status&FILTERS=kermits.default.filters.status_code."GC"|kermits.default.filters.centre."WTSI"&VISIBLEPANEL=resultspanel
>>>>>> The search works fine and returns results, but when I try to get a count
>>>>>> from this search (I'd like to know the number of genes with this
>>>>>> criteria),
>>>>>> the mart throws the following error (both in MartView and through the
>>>>>> APIs):
>>>>>> Query ERROR: caught BioMart::Exception: non-BioMart die(): Can't use
>>>>>> an undefined value as an ARRAY reference at
>>>>>> /software/team87/biomart/biomart-0_7-prod/lib/BioMart/Dataset/TableSet.pm
>>>>>> line 911.
>>>>>> To get the count, I'm hitting it via the rest api with the following
>>>>>> xml:
>>>>>> <?xml version="1.0" encoding="UTF-8"?>
>>>>>> <!DOCTYPE Query>
>>>>>> <Query virtualSchemaName="default" formatter="TSV" header="0"
>>>>>> uniqueRows="1" count="1" datasetConfigVersion="0.6">
>>>>>> <Dataset name="kermits" interface="default">
>>>>>> <Filter name="status_code" value="GC"/>
>>>>>> <Filter name="centre" value="WTSI"/>
>>>>>> </Dataset>
>>>>>> </Query>
>>>>>> Any idea what could be the issue here?
>>>>>> Thanks,
>>>>>> Daz
>>>>>> P.S. Forgot to let you all know - our new (preview) Mouse portal at
>>>>>> the Sanger is now running off Biomart:
>>>>>> http://www.sanger.ac.uk/mouseportal/ (well, several biomarts
>>>>>> really...) It's still in development, and things can be flaky every now
>>>>>> and
>>>>>> then, so be gentle! ;)
>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> The Wellcome Trust Sanger Institute is operated by Genome
>>>> ResearchLimited, a charity registered in England with number 1021457 and
>>>> acompany registered in England with number 2742969, whose registeredoffice
>>>> is 215 Euston Road, London, NW1 2BE.
>>>>
>>>
>>>
>>>
>>>
>>
>>
>> -- The Wellcome Trust Sanger Institute is operated by Genome Research
>> Limited, a charity registered in England with number 1021457 and a company
>> registered in England with number 2742969, whose registered office is 215
>> Euston Road, London, NW1 2BE.
>>
>
>
>
> -- The Wellcome Trust Sanger Institute is operated by Genome Research
> Limited, a charity registered in England with number 1021457 and a company
> registered in England with number 2742969, whose registered office is 215
> Euston Road, London, NW1 2BE.
>
