[ensembl-dev] core api : MySQL server has gone away
Andy Yates
ayates at ebi.ac.uk
Tue Dec 3 11:58:02 GMT 2013
Hi Catherine,
Sometimes this can happen when the MySQL server is under a high load or the client is a significant distance away from the server. The load on ensembldb seems OK at the moment and since you're at the Sanger I doubt it's an issue with distance from the server. You can try the following trick
Bio::EnsEMBL::Registry->set_reconnect_when_lost(1);
This causes the API to reconnect to the database when the connection has been lost but this will only work if the connection was lost before we issued the query. One other possible issue here is that you seem to be running this from the Starman using Dancer. We also use the Perl API from our REST API using similar technology (Plack + Catalyst) but found that connections persist between requests and timeouts can occur if settings like reconnect_when_lost is not enabled. I would also urge caution in requesting/reloading the registry every time you do a request in such as persistent environment. If Dancer supports pan-request stores of objects within a single process (similar to Java servlet's session scope) you may be best storing a flag saying you've already attempted to load the registry. Otherwise you're doing a lot of processing/database queries for minimal benefit.
Andy
------------
Andrew Yates - Ensembl Core Software Project Leader
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 3 Dec 2013, at 11:19, Catherine Leroy <cl6 at sanger.ac.uk> wrote:
> Hello,
>
>
> I'm doing :
>
> my $registry = 'Bio::EnsEMBL::Registry';
>
> $registry->load_registry_from_db(
> -host => 'ensembldb.ensembl.org', # alternatively 'useastdb.ensembl.org'
> -user => 'anonymous'
> );
>
>
> my $gene_adaptor = $registry->get_adaptor( 'Human', 'Core', 'Gene' );
> #COG6
> my $gene = $gene_adaptor->fetch_by_display_label($geneLabel);
>
> $geneLabel = COG6
>
> This was working fine last week up to Thursday afternoon but it's not working anymore.
> I get the following message pasted below.
>
> Would somebody be able to help me? :-)
>
> Thanks for your help,
> Catherine
>
>
> MSG: Detected an error whilst executing SQL 'SELECT g.gene_id, g.seq_region_id, g.seq_region_start, g.seq_region_end, g.seq_region_strand, g.analysis_id, g.biotype, g.display_xref_id, g.description, g.status, g.source, g.is
> _current, g.canonical_transcript_id, g.canonical_annotation, g.stable_id, g.version, UNIX_TIMESTAMP(g.created_date), UNIX_TIMESTAMP(g.modified_date), x.display_label, x.dbprimary_acc, x.description, x.version, exdb.db_name, exdb.status, exdb.db_release, exdb.db_display_name, x.info_type, x.info_text
> FROM (( (gene g)
> LEFT JOIN xref x ON x.xref_id = g.display_xref_id )
> LEFT JOIN external_db exdb ON exdb.external_db_id = x.external_db_id )
> WHERE x.display_label = ? AND
> g.is
> _current = 1
> ': DBD::mysql::st execute failed:
> MySQL server has gone away at /data/perl5/lib/perl5/Bio/EnsEMBL/DBSQL/BaseAdaptor.pm line 468
> .
>
> STACK Bio::EnsEMBL::DBSQL::BaseAdaptor::generic_fetch /data/perl5/lib/perl5/Bio/EnsEMBL/DBSQL/BaseAdaptor.pm:469
> STACK Bio::EnsEMBL::DBSQL::GeneAdaptor::fetch_by_display_label /data/perl5/lib/perl5/Bio/EnsEMBL/DBSQL/GeneAdaptor.pm:173
> STACK Cnv::SampleSelection::SampleSelection::__ANON__[/data/www/cl6/Cnv/lib/Cnv/SampleSelection/SampleSelection.pm:256] /data/www/cl6/Cnv/lib/Cnv/SampleSelection/SampleSelection.pm:118
> STACK Dancer::Route::execute /data/perl5/lib/perl5/Dancer/Route.pm:274
> STACK Dancer::Route::__ANON__ /data/perl5/lib/perl5/Dancer/Route.pm:177
> STACK (eval) /usr/share/perl5/Try/Tiny.pm:71
> STACK Try::Tiny::try /usr/share/perl5/Try/Tiny.pm:67
> STACK Dancer::Route::run /data/perl5/lib/perl5/Dancer/Route.pm:191
> STACK Dancer::Renderer::__ANON__ /data/perl5/lib/perl5/Dancer/Renderer.pm:134
> STACK (eval) /usr/share/perl5/Try/Tiny.pm:76
> STACK Try::Tiny::try /usr/share/perl5/Try/Tiny.pm:67
> STACK Dancer::Renderer::get_action_response /data/perl5/lib/perl5/Dancer/Renderer.pm:143
> STACK Dancer::Renderer::render_action /data/perl5/lib/perl5/Dancer/Renderer.pm:31
> STACK Dancer::Handler::__ANON__ /data/perl5/lib/perl5/Dancer/Handler.pm:84
> STACK (eval) /usr/share/perl5/Try/Tiny.pm:71
> STACK Try::Tiny::try /usr/share/perl5/Try/Tiny.pm:67
> STACK Dancer::Handler::render_request /data/perl5/lib/perl5/Dancer/Handler.pm:113
> STACK Dancer::Handler::handle_request /data/perl5/lib/perl5/Dancer/Handler.pm:76
> STACK Dancer::_start /data/perl5/lib/perl5/Dancer.pm:470
> STACK Plack::Sandbox::_2fdata_2fwww_2fcl6_2fCnv_2fapp_2epsgi::__ANON__ /data/www/cl6/Cnv/app.psgi:18
> STACK Plack::App::URLMap::call /data/perl5/lib/perl5/Plack/App/URLMap.pm:71
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK Plack::Middleware::Assets::call /data/perl5/lib/perl5/Plack/Middleware/Assets.pm:97
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK Plack::Middleware::Assets::call /data/perl5/lib/perl5/Plack/Middleware/Assets.pm:97
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK Plack::Middleware::Assets::call /data/perl5/lib/perl5/Plack/Middleware/Assets.pm:97
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK Plack::Middleware::Assets::call /data/perl5/lib/perl5/Plack/Middleware/Assets.pm:97
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK Plack::Middleware::Lint::call /data/perl5/lib/perl5/Plack/Middleware/Lint.pm:24
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK Plack::Middleware::StackTrace::__ANON__ /data/perl5/lib/perl5/Plack/Middleware/StackTrace.pm:31
> STACK (eval) /usr/share/perl5/Try/Tiny.pm:71
> STACK Try::Tiny::try /usr/share/perl5/Try/Tiny.pm:67
> STACK Plack::Middleware::StackTrace::call /data/perl5/lib/perl5/Plack/Middleware/StackTrace.pm:35
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK Plack::Middleware::AccessLog::call /data/perl5/lib/perl5/Plack/Middleware/AccessLog.pm:29
> STACK Plack::Component::__ANON__ /data/perl5/lib/perl5/Plack/Component.pm:39
> STACK (eval) /data/perl5/lib/perl5/Plack/Util.pm:143
> STACK Plack::Util::run_app /data/perl5/lib/perl5/Plack/Util.pm:143
> STACK Starman::Server::process_request /data/perl5/lib/perl5/Starman/Server.pm:256
> STACK Net::Server::run_client_connection /data/perl5/lib/perl5/Net/Server.pm:75
> STACK (eval) /data/perl5/lib/perl5/Net/Server/PreFork.pm:229
> STACK Net::Server::PreFork::run_child /data/perl5/lib/perl5/Net/Server/PreFork.pm:229
> STACK Net::Server::PreFork::run_n_children /data/perl5/lib/perl5/Net/Server/PreFork.pm:187
> STACK Net::Server::PreFork::loop /data/perl5/lib/perl5/Net/Server/PreFork.pm:111
> STACK Net::Server::run /data/perl5/lib/perl5/Net/Server.pm:61
> STACK Starman::Server::run /data/perl5/lib/perl5/Starman/Server.pm:67
> STACK Plack::Handler::Starman::run /data/perl5/lib/perl5/Plack/Handler/Starman.pm:18
> STACK Plack::Loader::Delayed::run /data/perl5/lib/perl5/Plack/Loader/Delayed.pm:20
> STACK Plack::Runner::run /data/perl5/lib/perl5/Plack/Runner.pm:277
> STACK toplevel /data/perl5/bin/starman:41
> Date (localtime) = Tue Dec 3 10:28:46 2013
> Ensembl API version = 73
> ---------------------------------------------------
>
>
> _______________________________________________
> Dev mailing list Dev at ensembl.org
> Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev
> Ensembl Blog: http://www.ensembl.info/
More information about the Dev
mailing list