[ensembl-dev] Perl programmatic access to MySQL server failed
Natalie Willhoft
nwillhoft at ebi.ac.uk
Tue Jun 4 15:44:22 BST 2024
Hi Timothé,
Thanks for your email.
There appears to be networking maintenance work on some Ensembl-related services today, which may have had an impact here. This work is only scheduled for this afternoon; please could you check your script again tomorrow and let us know if it’s still causing issues?
Thanks very much and best wishes,
Natalie
Natalie Willhöft
Ensembl Infrastructure Team
EMBL-EBI
nwillhoft at ebi.ac.uk
> On 4 Jun 2024, at 15:06, Timothé van Meter <timothe.vm at posteo.net> wrote:
>
> Hello,
>
>
> I am using a perl script to access programmatically the ensembl database for the mouse's genome. This script which was previously working for a while without any warnings is now not functioning anymore with the following error message:
>
> "
> DBI connect('host=ensembldb.ensembl.org;port=3306','anonymous',...) failed: Can't connect to MySQL server on 'ensembldb.ensembl.org:3306' (110) at /home/timothe/src/ensembl/modules/Bio/EnsEMBL/Registry.pm line 1771.
>
> -------------------- EXCEPTION --------------------
> MSG: Cannot connect to the Ensembl MySQL server at ensembldb.ensembl.org:3306; check your settings & DBI error message: Can't connect to MySQL server on 'ensembldb.ensembl.org:3306' (110)
> STACK Bio::EnsEMBL::Registry::load_registry_from_db /home/timothe/src/ensembl/modules/Bio/EnsEMBL/Registry.pm:1771
> STACK toplevel get.associated.genes.pl:31
> Date (localtime) = Tue Jun 4 15:41:31 2024
> Ensembl API version = 110
> ---------------------------------------------------
> "
>
> I copied the entire script I am using, get.associated.genes.pl, at the end of this email.
>
> Below I am reporting the output of the recommended commands for debugging the Ensembl API hoping they might be useful.
>
> calling ping -c 5 in the terminal returns the following:
> PING hl-ensembl-databases.ebi.ac.uk (193.62.192.245) 56(84) bytes of data.
>
> --- hl-ensembl-databases.ebi.ac.uk ping statistics ---
> 5 packets transmitted, 0 received, 100% packet loss, time 4084ms
>
>
> Below the output of the perl ping_ensembl.pl script advised on the "Debugging Ensembl API Connections" webpage:
>
> -------------------- EXCEPTION --------------------
> MSG: Cannot connect to the Ensembl MySQL server at ensembldb.ensembl.org:3306; check your settings & DBI error message: Can't connect to MySQL server on 'ensembldb.ensembl.org:3306' (110)
> STACK Bio::EnsEMBL::Registry::load_registry_from_db /home/timothe/src/ensembl/modules/Bio/EnsEMBL/Registry.pm:1771
> STACK (eval) ping_ensembl.pl:122
> STACK toplevel ping_ensembl.pl:113
> Date (localtime) = Tue Jun 4 11:52:08 2024
> Ensembl API version = 110
> ---------------------------------------------------
>
>
> The output of the "perl -V" command is written at the end of the message.
>
> The output of the "perl -MDBI -e 'warn $DBI::VERSION'" command: 1.643 at -e line 1.
>
> The output of the "perl -MDBD::mysql -e 'warn $DBD::mysql::VERSION'" command: 5.003 at -e line 1.
>
> I am available to answer any questions that might further help solve this problem. Thank you in advance for your consideration.
>
>
> Best regards,
> Timothé van Meter
>
>
>
>
> # ---------------------------------------------------------------------
> Contents of the script get.associated.genes.pl
> # ---------------------------------------------------------------------
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> use Data::Dumper;
> use Bio::EnsEMBL::Registry;
> use Bio::Tools::Run::Ensembl;
> use Try::Tiny;
> use Term::ProgressBar;
>
> # my $filename = '/data/blasting/out.awk';
> my $filename = $ARGV[0];
> my $extension = 4000;
>
> # # Name of the output file with enhancers
> my $output = 'test.genes.associated';
>
> my $registry = "Bio::EnsEMBL::Registry";
> $registry->load_registry_from_db(
> -HOST => 'ensembldb.ensembl.org',
> -USER => 'anonymous',
> # -PORT => '3306'
> # -PORT => '5306'
> -VERBOSE => 1
> );
>
> sub parsePromoters {
> my ($filename) = @_;
> my @promoters;
> open(FH, '<', $filename) or die $!;
> while(<FH>){
> chomp;
> push @promoters, [ split(/ /, $_) ];
> }
> close(FH);
> return @promoters;
> }
>
>
> my $slice_adaptor = $registry->get_adaptor( "Mouse", 'Core', 'Slice' );
> # # my $slice = $slice_adaptor->fetch_by_region( 'chromosome', '10', 3700000, 3830000 );
> my @genes;
>
> # # OPEN THE OUTPUT FILE AND PRINT THE
> # # RESULTS DIRECTLY IN IT
> open(FH, '>', $output) or die $!;
>
> my $i = 0;
>
> my @testGenes;
>
> # LOOP OVER ALL GENES LOADED FROM FILE
> for $i (0 .. $#promoters) {
> print "$promoters[$i][0]\n";
> # if( exists( $hash{$gene} )) {
> # my $slice;
> my $start = $promoters[$i][3] - $extension;
> my $end = $promoters[$i][4] + $extension;
> try {
>
> print "region = ", $promoters[$i][2], " : ", $start, " - ", $end, "\n";
>
> my $slice = $slice_adaptor->fetch_by_region( 'chromosome', $promoters[$i][2], $start, $end );
> # my $slice = $slice_adaptor->fetch_by_region( 'chromosome', '10', 3770000, 3790000 );
> foreach my $gene ( @{ $slice->get_all_Genes() } ) {
> print "Gene ID: ", $gene->stable_id, "\n";
> # print "Gene Name: ", $gene->display_id, "\n";
> push @testGenes, [ $promoters[$i][0], $gene->stable_id, $gene->seq_region_start(), $gene->seq_region_end() ];
> }
>
> # print "@{ $slice->get_all_Genes() }\n";
>
> # push @genes, @templist;
>
> # print "$genes[0][0]\n";
>
> # print FH "\n>", $genes[0], "\n";
>
> } catch {
> next;
> }
> # } else {
> # next;
> # }
> # $progress_bar->update($i);
> # $i++;
> }
> print "\n---------------\n";
>
> # foreach my gene (@testGenes) {
> # print $gene[0]," ",$gene[1]," ",$gene[2],"\n";
> # }
> for my $i (0 .. $#testGenes) {
> # print FH $testGenes[$i][0]," ",$testGenes[$i][1]," ",$testGenes[$i][2],"\n";
> print FH $testGenes[$i][0]," ",$testGenes[$i][1],"\n";
> }
> # # CLOSE STREAM TO OUTPUT FILE
> close(FH);
>
> print "DONE!\n\n";
> # ---------------------------------------------------------------------
> # ---------------------------------------------------------------------
>
>
> # ---------------------------------------------------------------------
> Output of perl -V
> # ---------------------------------------------------------------------
> Summary of my perl5 (revision 5 version 34 subversion 0) configuration:
>
> Platform:
> osname=linux
> osvers=4.19.0
> archname=x86_64-linux-gnu-thread-multi
> uname='linux localhost 4.19.0 #1 smp debian 4.19.0 x86_64 gnulinux '
> config_args='-Dmksymlinks -Dusethreads -Duselargefiles -Dcc=x86_64-linux-gnu-gcc -Dcpp=x86_64-linux-gnu-cpp -Dld=x86_64-linux-gnu-gcc -Dccflags=-DDEBIAN -Wdate-time -D_FORTIFY_SOURCE=2 -g -O2 -ffile-prefix-map=/dummy/build/dir=. -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -Dldflags= -Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Dlddlflags=-shared -Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Dcccdlflags=-fPIC -Darchname=x86_64-linux-gnu -Dprefix=/usr -Dprivlib=/usr/share/perl/5.34 -Darchlib=/usr/lib/x86_64-linux-gnu/perl/5.34 -Dvendorprefix=/usr -Dvendorlib=/usr/share/perl5 -Dvendorarch=/usr/lib/x86_64-linux-gnu/perl5/5.34 -Dsiteprefix=/usr/local -Dsitelib=/usr/local/share/perl/5.34.0 -Dsitearch=/usr/local/lib/x86_64-linux-gnu/perl/5.34.0 -Dman1dir=/usr/share/man/man1 -Dman3dir=/usr/share/man/man3 -Dsiteman1dir=/usr/local/man/man1 -Dsiteman3dir=/usr/local/man/man3 -Duse64bitint -Dman1ext=1 -Dman3ext=3perl -Dpager=/usr/bin/sensible-pager -Uafs -Ud_csh -Ud_ualarm -Uusesfio -Uusenm -Ui_libutil -Ui_xlocale -Uversiononly -DDEBUGGING=-g -Doptimize=-O2 -dEs -Duseshrplib -Dlibperl=libperl.so.5.34.0'
> hint=recommended
> useposix=true
> d_sigaction=define
> useithreads=define
> usemultiplicity=define
> use64bitint=define
> use64bitall=define
> uselongdouble=undef
> usemymalloc=n
> default_inc_excludes_dot=define
> Compiler:
> cc='x86_64-linux-gnu-gcc'
> ccflags ='-D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fwrapv -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
> optimize='-O2 -g'
> cppflags='-D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fwrapv -fno-strict-aliasing -pipe -I/usr/local/include'
> ccversion=''
> gccversion='11.4.0'
> gccosandvers=''
> intsize=4
> longsize=8
> ptrsize=8
> doublesize=8
> byteorder=12345678
> doublekind=3
> d_longlong=define
> longlongsize=8
> d_longdbl=define
> longdblsize=16
> longdblkind=3
> ivtype='long'
> ivsize=8
> nvtype='double'
> nvsize=8
> Off_t='off_t'
> lseeksize=8
> alignbytes=8
> prototype=define
> Linker and Libraries:
> ld='x86_64-linux-gnu-gcc'
> ldflags =' -fstack-protector-strong -L/usr/local/lib'
> libpth=/usr/local/lib /usr/lib/x86_64-linux-gnu /usr/lib /lib/x86_64-linux-gnu /lib
> libs=-lgdbm -lgdbm_compat -ldb -ldl -lm -lpthread -lc -lcrypt
> perllibs=-ldl -lm -lpthread -lc -lcrypt
> libc=/lib/x86_64-linux-gnu/libc.so.6
> so=so
> useshrplib=true
> libperl=libperl.so.5.34
> gnulibc_version='2.35'
> Dynamic Linking:
> dlsrc=dl_dlopen.xs
> dlext=so
> d_dlsymun=undef
> ccdlflags='-Wl,-E'
> cccdlflags='-fPIC'
> lddlflags='-shared -L/usr/local/lib -fstack-protector-strong'
>
>
> Characteristics of this binary (from libperl):
> Compile-time options:
> HAS_TIMES
> MULTIPLICITY
> PERLIO_LAYERS
> PERL_COPY_ON_WRITE
> PERL_DONT_CREATE_GVSV
> PERL_IMPLICIT_CONTEXT
> PERL_MALLOC_WRAP
> PERL_OP_PARENT
> PERL_PRESERVE_IVUV
> USE_64_BIT_ALL
> USE_64_BIT_INT
> USE_ITHREADS
> USE_LARGE_FILES
> USE_LOCALE
> USE_LOCALE_COLLATE
> USE_LOCALE_CTYPE
> USE_LOCALE_NUMERIC
> USE_LOCALE_TIME
> USE_PERLIO
> USE_PERL_ATOF
> USE_REENTRANT_API
> USE_THREAD_SAFE_LOCALE
> Locally applied patches:
> DEBPKG:debian/cpan_definstalldirs - Provide a sensible INSTALLDIRS default for modules installed from CPAN.
> DEBPKG:debian/db_file_ver - https://bugs.debian.org/340047 Remove overly restrictive DB_File version check.
> DEBPKG:debian/doc_info - Replace generic man(1) instructions with Debian-specific information.
> DEBPKG:debian/enc2xs_inc - https://bugs.debian.org/290336 Tweak enc2xs to follow symlinks and ignore missing @INC directories.
> DEBPKG:debian/errno_ver - https://bugs.debian.org/343351 Remove Errno version check due to upgrade problems with long-running processes.
> DEBPKG:debian/libperl_embed_doc - https://bugs.debian.org/186778 Note that libperl-dev package is required for embedded linking
> DEBPKG:fixes/respect_umask - Respect umask during installation
> DEBPKG:debian/writable_site_dirs - Set umask approproately for site install directories
> DEBPKG:debian/extutils_set_libperl_path - EU:MM: set location of libperl.a under /usr/lib
> DEBPKG:debian/no_packlist_perllocal - Don't install .packlist or perllocal.pod for perl or vendor
> DEBPKG:debian/fakeroot - Postpone LD_LIBRARY_PATH evaluation to the binary targets.
> DEBPKG:debian/instmodsh_doc - Debian policy doesn't install .packlist files for core or vendor.
> DEBPKG:debian/ld_run_path - Remove standard libs from LD_RUN_PATH as per Debian policy.
> DEBPKG:debian/libnet_config_path - Set location of libnet.cfg to /etc/perl/Net as /usr may not be writable.
> DEBPKG:debian/perlivp - https://bugs.debian.org/510895 Make perlivp skip include directories in /usr/local
> DEBPKG:debian/squelch-locale-warnings - https://bugs.debian.org/508764 Squelch locale warnings in Debian package maintainer scripts
> DEBPKG:debian/patchlevel - https://bugs.debian.org/567489 List packaged patches for 5.34.0-3ubuntu1.3 in patchlevel.h
> DEBPKG:fixes/document_makemaker_ccflags - https://bugs.debian.org/628522 [rt.cpan.org #68613] Document that CCFLAGS should include $Config{ccflags}
> DEBPKG:debian/find_html2text - https://bugs.debian.org/640479 Configure CPAN::Distribution with correct name of html2text
> DEBPKG:debian/perl5db-x-terminal-emulator.patch - https://bugs.debian.org/668490 Invoke x-terminal-emulator rather than xterm in perl5db.pl
> DEBPKG:debian/cpan-missing-site-dirs - https://bugs.debian.org/688842 Fix CPAN::FirstTime defaults with nonexisting site dirs if a parent is writable
> DEBPKG:fixes/memoize_storable_nstore - [rt.cpan.org #77790] https://bugs.debian.org/587650 Memoize::Storable: respect 'nstore' option not respected
> DEBPKG:debian/makemaker-pasthru - https://bugs.debian.org/758471 Pass LD settings through to subdirectories
> DEBPKG:debian/makemaker-manext - https://bugs.debian.org/247370 Make EU::MakeMaker honour MANnEXT settings in generated manpage headers
> DEBPKG:debian/kfreebsd-softupdates - https://bugs.debian.org/796798 Work around Debian Bug#796798
> DEBPKG:fixes/memoize-pod - [rt.cpan.org #89441] Fix POD errors in Memoize
> DEBPKG:debian/hurd-softupdates - https://bugs.debian.org/822735 Fix t/op/stat.t failures on hurd
> DEBPKG:fixes/math_complex_doc_great_circle - https://bugs.debian.org/697567 [rt.cpan.org #114104] Math::Trig: clarify definition of great_circle_midpoint
> DEBPKG:fixes/math_complex_doc_see_also - https://bugs.debian.org/697568 [rt.cpan.org #114105] Math::Trig: add missing SEE ALSO
> DEBPKG:fixes/math_complex_doc_angle_units - https://bugs.debian.org/731505 [rt.cpan.org #114106] Math::Trig: document angle units
> DEBPKG:fixes/cpan_web_link - https://bugs.debian.org/367291 CPAN: Add link to main CPAN web site
> DEBPKG:debian/hppa_op_optimize_workaround - https://bugs.debian.org/838613 Temporarily lower the optimization of op.c on hppa due to gcc-6 problems
> DEBPKG:debian/installman-utf8 - https://bugs.debian.org/840211 Generate man pages with UTF-8 characters
> DEBPKG:debian/hppa_opmini_optimize_workaround - https://bugs.debian.org/869122 Lower the optimization level of opmini.c on hppa
> DEBPKG:debian/sh4_op_optimize_workaround - https://bugs.debian.org/869373 Also lower the optimization level of op.c and opmini.c on sh4
> DEBPKG:debian/perldoc-pager - https://bugs.debian.org/870340 [rt.cpan.org #120229] Fix perldoc terminal escapes when sensible-pager is less
> DEBPKG:debian/prune_libs - https://bugs.debian.org/128355 Prune the list of libraries wanted to what we actually need.
> DEBPKG:debian/mod_paths - Tweak @INC ordering for Debian
> DEBPKG:debian/deprecate-with-apt - https://bugs.debian.org/747628 Point users to Debian packages of deprecated core modules
> DEBPKG:debian/disable-stack-check - https://bugs.debian.org/902779 [GH #16607] Disable debugperl stack extension checks for binary compatibility with perl
> DEBPKG:debian/perlbug-editor - https://bugs.debian.org/922609 Use "editor" as the default perlbug editor, as per Debian policy
> DEBPKG:debian/eu-mm-perl-base - https://bugs.debian.org/962138 Suppress an ExtUtils::MakeMaker warning about our non-default @INC
> DEBPKG:fixes/io_socket_ip_ipv6 - Disable getaddrinfo(3) AI_ADDRCONFIG for localhost and IPv4 numeric addresses
> DEBPKG:debian/usrmerge-lib64 - https://bugs.debian.org/914128 Configure / libpth.U: Do not adjust glibpth when /usr/lib64 is present.
> DEBPKG:debian/usrmerge-realpath - https://bugs.debian.org/914128 Configure / libpth.U: use realpath --no-symlinks on Debian
> DEBPKG:debian/configure-regen - https://bugs.debian.org/762638 Regenerate Configure et al. after probe unit changes
> DEBPKG:fixes/x32-io-msg-skip - https://bugs.debian.org/922609 Skip io/msg.t on x32 due to broken System V message queues
> DEBPKG:fixes/encode-CVE-2021-36770 - mitigate @INC pollution when loading ConfigLocal
> DEBPKG:fixes/gdbm-1.20 - https://bugs.debian.org/993514 [GH #18915] Fix GDBM_File to compile with version 1.20 and earlier
> DEBPKG:disable-libperl-tests -
> DEBPKG:CVE-2020-16156-1.patch - [PATCH] bugfix: signature verification type CANNOT_VERIFY was not recognized
> DEBPKG:CVE-2020-16156-2.patch - [PATCH] Add two new failure modes based on cpan_path
> DEBPKG:CVE-2020-16156-3.patch - [PATCH] use gpg --verify --output ... to disentangle data and signature
> DEBPKG:CVE-2020-16156-4.patch - [PATCH] replacing die with mydie in three spots
> DEBPKG:CVE-2020-16156-5.patch - [PATCH] disambiguate the call to gpg --output by adding --verify
> DEBPKG:CVE-2020-16156-6.patch - [PATCH] s/gpg/$gpg/ in system, add quotes where needed
> DEBPKG:CVE-2020-16156-7.patch - [PATCH] s,/dev/null,$devnull,
> DEBPKG:CVE-2023-31484.patch - [PATCH] Add verify_SSL=>1 to HTTP::Tiny to verify https server identity
> DEBPKG:CVE-2023-47038.patch - [PATCH 1/2] Fix read/write past buffer end: perl-security#140
> DEBPKG:CVE-2022-48522.patch - [PATCH] Don't try to Sv[PI]V() on an undef index SV in find_uninit_var()
> Built under linux
> Compiled at Nov 23 2023 14:56:46
> %ENV:
> PERL5LIB="/home/timothe/perl5/lib/perl5::/home/timothe/src/bioperl-1.6.924:/home/timothe/src/ensembl/modules:/home/timothe/src/ensembl-compara/modules:/home/timothe/src/ensembl-variation/modules:/home/timothe/src/ensembl-funcgen/modules"
> PERL_LOCAL_LIB_ROOT="/home/timothe/perl5"
> PERL_MB_OPT="--install_base "/home/timothe/perl5""
> PERL_MM_OPT="INSTALL_BASE=/home/timothe/perl5"
> @INC:
> /home/timothe/perl5/lib/perl5/5.34.0/x86_64-linux-gnu-thread-multi
> /home/timothe/perl5/lib/perl5/5.34.0
> /home/timothe/perl5/lib/perl5/x86_64-linux-gnu-thread-multi
> /home/timothe/perl5/lib/perl5
> /home/timothe/src/bioperl-1.6.924
> /home/timothe/src/ensembl/modules
> /home/timothe/src/ensembl-compara/modules
> /home/timothe/src/ensembl-variation/modules
> /home/timothe/src/ensembl-funcgen/modules
> /etc/perl
> /usr/local/lib/x86_64-linux-gnu/perl/5.34.0
> /usr/local/share/perl/5.34.0
> /usr/lib/x86_64-linux-gnu/perl5/5.34
> /usr/share/perl5
> /usr/lib/x86_64-linux-gnu/perl-base
> /usr/lib/x86_64-linux-gnu/perl/5.34
> /usr/share/perl/5.34
> /usr/local/lib/site_perl
> # ---------------------------------------------------------------------
> # ---------------------------------------------------------------------
>
>
> _______________________________________________
> Dev mailing list Dev at ensembl.org
> Posting guidelines and subscribe/unsubscribe info: https://lists.ensembl.org/mailman/listinfo/dev_ensembl.org
> Ensembl Blog: http://www.ensembl.info/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20240604/392d9f30/attachment-0001.html>
More information about the Dev
mailing list