<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
Hi Andy,
<div>I adjusted the code to set reconnect in what must be the right places and all is running well.</div>
<div>Thank you so much for the help!</div>
<div>Ed</div>
<br>
<br>
<div>-------- Original message --------</div>
<div>From: Andy Yates <ayates@ebi.ac.uk></div>
<div>Date:05/28/2014 7:07 PM (GMT-05:00) </div>
<div>To: Ensembl developers list <dev@ensembl.org></div>
<div>Subject: Re: [ensembl-dev] DBD::mysql::st execute failed: MySQL server has gone away at EnsEMBL/DBSQL/BaseAdaptor.pm
</div>
<div><br>
</div>
<div class="BodyFragment">
<div class="PlainText">Hi Ed,<br>
<br>
On 28 May 2014, at 23:47, Ed Gray <gray_ed@hotmail.com> wrote:<br>
<br>
> Hi Andy,<br>
> <br>
> Thanks for the info. Setting our open-files-limit seems to have resolved the earlier error.<br>
<br>
Glad to hear about that :)<br>
<br>
> <br>
> I am running into another curious error its a DBD::mysql::st execute failed: MySQL server has gone away.<br>
> I do a "$registry->set_reconnect_when_lost();" but oddly that doesn't seem to help.<br>
> <br>
> questions:<br>
> <br>
> 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?<br>
<br>
Normally after the load_registry_from_db() call as it loops through all DBAdaptors and calls reconnect_when_lost(1) on their underlying DBConnection object<br>
<br>
> <br>
> 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 <br>
> <br>
> here is an example sort of code:<br>
> <br>
> foreach my $slice (@{ $slices_ref }) {<br>
> my $all_genes_ref = $slice->get_all_Genes();<br>
> foreach my $gene ( @{ $all_genes_ref } ) { #all of the above code worked before<br>
> my @homs = @{$gene->get_all_homologous_Genes}; #may be erroring here<br>
> foreach my $all_homs (@homs){ #may be erroring here<br>
> my ($hgene, $compara, $homspecies) = @{$all_homs};<br>
> $myexternalname = $hgene->external_name;<br>
> $mydescription = $hgene->description; <br>
> $myassemblyversion = $hgene->feature_Slice->coord_system->version; #may be erroring here<br>
> <br>
> ....<br>
> 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.<br>
> <br>
> 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.<br>
> <br>
> Yikes! Does any of this make sense?<br>
<br>
This does make some sense and the "MySQL server has gone away" does seem to be a persistent error in some setups. The reason why the reconnect didn't work though is because it fires before the prepare of a SQL statement not during execution or data retrieval.
So if it goes away when we're waiting for the server to run the query we cannot reconnect. Also I don't think you're confusing MySQL though or the APIs by switching between core & compara. We did see this error appear a lot when setting up our USEast mirror
and solved it by increasing some of the timeouts. These are our current timeouts on that server:<br>
<br>
+----------------------------+-------+<br>
| Variable_name | Value |<br>
+----------------------------+-------+<br>
| net_read_timeout | 300 |<br>
| net_write_timeout | 300 |<br>
| slave_net_timeout | 3600 |<br>
| wait_timeout | 7200 |<br>
+----------------------------+-------+<br>
<br>
Can you check what your own is set to?<br>
<br>
Andy<br>
<br>
> <br>
> Ed<br>
> <br>
> <br>
> > From: ayates@ebi.ac.uk<br>
> > Date: Wed, 28 May 2014 09:23:33 +0100<br>
> > To: dev@ensembl.org<br>
> > Subject: Re: [ensembl-dev] './ensembl_compara_75/member.frm' (errno: 24)<br>
> > <br>
> > Hi,<br>
> > <br>
> > 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.<br>
> > <br>
> > 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.<br>
> > <br>
> > Andy<br>
> > <br>
> > ------------<br>
> > Andrew Yates - Ensembl Support Coordinator<br>
> > European Bioinformatics Institute (EMBL-EBI)<br>
> > European Molecular Biology Laboratory<br>
> > Wellcome Trust Genome Campus<br>
> > Hinxton<br>
> > Cambridge CB10 1SD<br>
> > Tel: +44-(0)1223-492538<br>
> > Fax: +44-(0)1223-494468<br>
> > <a href="http://www.ensembl.org/">http://www.ensembl.org/</a><br>
> > <br>
> > On 27 May 2014, at 16:02, Ed Gray <gray_ed@hotmail.com> wrote:<br>
> > <br>
> > > 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?<br>
> > > <br>
> > > Anyone encounter/fix problems with a long running perl script using the ensembl api that generates too many file handles?<br>
> > > <br>
> > > From: Stuart.Watt@oicr.on.ca<br>
> > > To: dev@ensembl.org<br>
> > > Date: Tue, 27 May 2014 14:32:42 +0000<br>
> > > Subject: Re: [ensembl-dev] './ensembl_compara_75/member.frm' (errno: 24)<br>
> > > <br>
> > > 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). <br>
> > > <br>
> > > All the best<br>
> > > Stuart<br>
> > > <br>
> > > <br>
> > > On May 27, 2014, at 10:22 AM, Ed Gray <gray_ed@hotmail.com> wrote:<br>
> > > <br>
> > > <br>
> > > strange error:<br>
> > > <br>
> > > here is what I did:<br>
> > > <br>
> > > my @homs = @{$gene->get_all_homologous_Genes};<br>
> > > <br>
> > > 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.<br>
> > > -------------------- EXCEPTION --------------------<br>
> > > 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.<br>
> > > 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<br>
> > > 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<br>
> > > 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<br>
> > > STACK Bio::EnsEMBL::Compara::MemberSet::get_all_Members /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/MemberSet.pm:443<br>
> > > STACK Bio::EnsEMBL::Compara::MemberSet::get_all_GeneMembers /usr/local/share/perl/5.12.4/ensembl-compara/modules/Bio/EnsEMBL/Compara/MemberSet.pm:489<br>
> > > STACK Bio::EnsEMBL::Gene::get_all_homologous_Genes /usr/local/share/perl/5.12.4/ensembl/modules/Bio/EnsEMBL/Gene.pm:857<br>
> > > STACK main::process_homologs newget_gene_info.pl:423<br>
> > > STACK toplevel newget_gene_info.pl:292<br>
> > > Date (localtime) = Tue May 27 12:46:41 2014<br>
> > > Ensembl API version = 75<br>
> > > ---------------------------------------------------<br>
> _______________________________________________<br>
> Dev mailing list Dev@ensembl.org<br>
> Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">
http://lists.ensembl.org/mailman/listinfo/dev</a><br>
> Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><br>
<br>
<br>
_______________________________________________<br>
Dev mailing list Dev@ensembl.org<br>
Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">
http://lists.ensembl.org/mailman/listinfo/dev</a><br>
Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><br>
</div>
</div>
</body>
</html>