[ensembl-dev] DBD::mysql::st execute failed: MySQL server has gone away at EnsEMBL/DBSQL/BaseAdaptor.pm

Wed May 28 23:47:31 BST 2014

Hi Andy,

Thanks for the info.  Setting our open-files-limit seems to have resolved the earlier error.

I am running into another curious error its a DBD::mysql::st execute failed: MySQL server has gone away. 

I do a "$registry->set_reconnect_when_lost();" but oddly that doesn't seem to help.

questions:

1) When is the correct time to do a "$registry->set_reconnect_when_lost();", just after the $registry->load_registry_from_db( ....)? or after the $registry->get_adaptor( ...) call?

2) I am using gene->get_all_homologous_Genes() method of Gene.pm which hands me back a Homology Object.  The Gene is from a Slice Object in a "core" database adapter.  The Homology really isnt a "Core" thing but instead a "Compara" thing.  From the Gene in the Homology obect 

here is an example sort of code:

foreach my $slice (@{ $slices_ref }) {

   my $all_genes_ref = $slice->get_all_Genes();

      foreach my $gene ( @{ $all_genes_ref } ) {        #all of the above code worked before

         my @homs = @{$gene->get_all_homologous_Genes}; #may be erroring here

         foreach my $all_homs (@homs){                     #may be erroring here

            my ($hgene, $compara, $homspecies) = @{$all_homs};

            $myexternalname = $hgene->external_name;
            $mydescription = $hgene->description; 

            $myassemblyversion = $hgene->feature_Slice->coord_system->version;    #may be erroring here

....

Anyhow, after I've connected, gotten the core adapter set_reconnect_when_lost etc.  The code runs for about 20 minutes cycling therough genes and getting homologs then starts intermittently spitting out these: DBD::mysql::st execute failed: MySQL server has gone away at /...../EnsEMBL/DBSQL/BaseAdaptor.pm line 482.  These seem to come a couple at a time then a wait then then later the script just seems to abruptly stop abnormally without any real error.

So I think I may be confusing the system perhaps by starting with a "core" connection, jumping to a "compara" connection and then back to another "core" in another species.

Yikes!  Does any of this make sense?

Ed

> From: ayates at ebi.ac.uk
> Date: Wed, 28 May 2014 09:23:33 +0100
> To: dev at ensembl.org
> Subject: Re: [ensembl-dev] './ensembl_compara_75/member.frm' (errno: 24)
> 
> Hi,
> 
> In the past we've seen this kind of behaviour linked to code which connects & disconnects from a database too often. MySQL traditionally assigns a connection to a linux process. Later versions of MySQL (5.6 enterprise), MariaDB & Percona Server have switched to using a pool of threads IIRC which does alleviate the problem.
> 
> Anyway going back to Ensembl. I've seen the issue appear because the disconnect_if_idle flag is turned on in a DBConnection object. Of course this maybe your intended behaviour as otherwise you are responsible for cleaning up the connections you create and filling up a MySQL server with idle connections. At any rate raising open-files-limit is a good way to alleviate this. To give you an idea our servers are normally set to 132,106.
> 
> Andy
> 
> ------------
> Andrew Yates - Ensembl Support Coordinator
> European Bioinformatics Institute (EMBL-EBI)
> European Molecular Biology Laboratory
> Wellcome Trust Genome Campus
> Hinxton
> Cambridge CB10 1SD
> Tel: +44-(0)1223-492538
> Fax: +44-(0)1223-494468
> http://www.ensembl.org/
> 
> On 27 May 2014, at 16:02, Ed Gray <gray_ed at hotmail.com> wrote:
> 
> > Good info Stuart. We have very few users and connections. It would seem to me though that the root problem might be not destroying an object properly eh?
> > 
> > Anyone encounter/fix problems with a long running perl script using the ensembl api that generates too many file handles?
> > 
> > From: Stuart.Watt at oicr.on.ca
> > To: dev at ensembl.org
> > Date: Tue, 27 May 2014 14:32:42 +0000
> > Subject: Re: [ensembl-dev] './ensembl_compara_75/member.frm' (errno: 24)
> > 
> > I’ve hit that one before with MySQL running out of file handles. See: http://stackoverflow.com/questions/11390213/1016-cant-open-file-database-name-sql-38f-36aa-frm-errno-24. You can usually set the server variable open-files-limit (IIRC, by default in many configs, it’s set low). 
> > 
> > All the best
> > Stuart
> > 
> > 
> > On May 27, 2014, at 10:22 AM, Ed Gray <gray_ed at hotmail.com> wrote:
> > 
> > 
> > strange error:
> > 
> > here is what I did:
> > 
> > my @homs = @{$gene->get_all_homologous_Genes};
> > 
> > DBD::mysql::st execute failed: Can't open file: './ensembl_compara_75/member.frm' (errno: 24) at /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/DBSQL/BaseAdaptor.pm line 168.
> > -------------------- EXCEPTION --------------------
> > MSG: Detected an error whilst executing SQL 'SELECT m.member_id, m.source_name, m.stable_id, m.version, m.taxon_id, m.genome_db_id, m.description, m.chr_name, m.chr_start, m.chr_end, m.chr_strand, m.sequence_id, m.gene_member_id, m.canonical_member_id, m.display_label, hm.cigar_line, hm.perc_cov, hm.perc_id, hm.perc_pos FROM (member m, homology_member hm) WHERE hm.homology_id = ? AND m.member_id = hm.peptide_member_id ': DBD::mysql::st execute failed: Can't open file: './ensembl_compara_75/member.frm' (errno: 24) at /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/DBSQL/BaseAdaptor.pm line 168.
> > STACK Bio::EnsEMBL::Compara::DBSQL::BaseAdaptor::generic_fetch /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/DBSQL/BaseAdaptor.pm:169
> > STACK Bio::EnsEMBL::Compara::DBSQL::AlignedMemberAdaptor::fetch_all_by_Homology /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/DBSQL/AlignedMemberAdaptor.pm:120
> > STACK Bio::EnsEMBL::Compara::DBSQL::AlignedMemberAdaptor::fetch_all_by_AlignedMemberSet /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/DBSQL/AlignedMemberAdaptor.pm:88
> > STACK Bio::EnsEMBL::Compara::MemberSet::get_all_Members /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/MemberSet.pm:443
> > STACK Bio::EnsEMBL::Compara::MemberSet::get_all_GeneMembers /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/MemberSet.pm:489
> > STACK Bio::EnsEMBL::Gene::get_all_homologous_Genes /usr/local/share/perl/5.12.4/ensembl/modules/Bio/EnsEMBL/Gene.pm:857
> > STACK main::process_homologs newget_gene_info.pl:423
> > STACK toplevel newget_gene_info.pl:292
> > Date (localtime) = Tue May 27 12:46:41 2014
> > Ensembl API version = 75
> > ---------------------------------------------------

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20140528/b5ba87d7/attachment.html>