[ensembl-dev] Losing connection to MySQL for large queries

[Monika Komorowska]

Hi Chris

Hopefully adding this line will solve your problem.

$registry->set_reconnect_when_lost();

Db connections will be tested and reconnected if necessary before any statements are executed.


Thanks

Monika

On 4 Oct 2011, at 09:20, Chris Raistrick wrote:

> Dear all,
> 
> Getting an error when trying to retrieve a complete list of variant
> IDs via the ensembl variation database:
> 
> [example std out]
> name = 'rs79594517', source = 'dbSNP', chr = '3'
> name = 'rs78223957', source = 'dbSNP', chr = '3'
> name = 'rs112696692', source = 'dbSNP', chr = '3'
> name = 'rs9761692', source = 'dbSNP', chr = '3'
> Chromosome 4
>        Connected to the Ensembl Registry
>        Slice adaptor generated
> DBD::mysql::st execute failed: Lost connection to MySQL server during
> query at .../ensembl/modules/Bio/EnsEMBL/DBSQL/SliceAdaptor.pm line
> 287.
> DBD::mysql::st execute failed: Lost connection to MySQL server during
> query at .../ensembl/modules/Bio/EnsEMBL/DBSQL/SliceAdaptor.pm line
> 287.
> 
> Each time I start a new chromosome using this code:
> 
> 		print "Chromosome $chr\n";
> 
> 		# connect to Ensembl registry	
> 		my $registry = 'Bio::EnsEMBL::Registry';
> 		$registry->load_registry_from_db(
> 		    -host => 'ensembldb.ensembl.org',
> 		    -user => 'anonymous'
> 		);
> 
> 		# get slice for chromosome
> 		my $slice_adaptor = $registry->get_adaptor('homo_sapiens', 'core', 'slice');
> 		my $slice = $slice_adaptor->fetch_by_region('chromosome',$chr);
> 
> 		# get variations by slice
> 		my $vf_adaptor = $registry->get_adaptor('homo_sapiens', 'variation',
> 'variationfeature');
> 		my $vfs = $vf_adaptor->fetch_all_by_Slice($slice);
> 
> 		foreach my $vf (@{$vfs}){
> 				my $var_id = $vf->variation_name;
> 				..
> 
> I read the last few messages I could find related on here and they
> said this fix would be in by release 64 (I downloaded the modules last
> week, so assume I'm on 64).
> 
> Please help, I've never had this problem using the Ensembl API (in 4 years).
> 
> Chris
> 
> _______________________________________________
> Dev mailing list    Dev at ensembl.org
> List admin (including subscribe/unsubscribe): http://lists.ensembl.org/mailman/listinfo/dev
> Ensembl Blog: http://www.ensembl.info/

Monika Komorowska
EnsEMBL Software Developer

European Bioinformatics Institute (EMBL-EBI)
tel: +44(0) 1233 494 409