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.
>
