[ensembl-dev] variation API-Data truncated for column error

[Laurent Gil]

Hi Nathalie,

The issue with the p-value is due to an issue with our import pipeline 
of the NHGRI data. It affects less than 20 entries.
Basically these entries don't have p-values.
We apologize for this issue and this will be fixed on the next Ensembl 
release (78).

If you want to avoid the error on you import script, you can replace the 
line:
$p_value=$pf->p_value;
by
$p_value = ($pf->p_value eq "NULL") ? undef : $pf->value;

Best regards,

Laurent

On 14/10/2014 17:29, Nathalie Conte wrote:
> Hi,
> i have this script below, which fetch from ensembl APi, RS-ID, 
> consequence source and pvalue and write these to a mysql database 
> table and a txt file.
> This script will print the  RS-ID, consequence source and pvalue in a 
> file without any issue (outtest6.txt) but not with the table in the 
> database, it will break at some point and throw an error message.
> _error message:_
> DBD::mysql::st execute failed: Data truncated for column 'P_VALUE' at 
> row 1 at ./test5.pl line 43.
> DBD::mysql::st execute failed: Data truncated for column 'P_VALUE' at 
> row 1 at ./test5.pl line 43.
>
> it will break at a line where the pvalue is written as "NULL" (string 
> is NULL not empty), see below  - I am not sure why this is happening 
> but it could be linked to the datatype of the p_value retrieved from 
> ensembl as in most case this is either a numeric value or nothing, but 
> not "NULL". Any help or tips appreciated
> thanks
> Nathalie
>
> >tail outtest6.txt where the script breaks
> rs421379Intergenic variantdbSNP4.0500248e-004
> rs429358Missense variantdbSNP
> rs429358Missense variantdbSNP
> rs429358Missense variantdbSNP
> rs429358Missense variantdbSNP
> *rs429358Missense variantdbSNPNULL*
> *
> *
> *
> *
> ######
> script, note the credentials to my database are hidden you will need 
> to test this using a in house database.
>
> #!/usr/local/bin/perl
> use strict;
> use warnings;
> use DBI;
> use Bio::EnsEMBL::Registry;
>
>
> Bio::EnsEMBL::Registry->load_registry_from_db(
>     -host=>"ensembldb.ensembl.org <http://ensembldb.ensembl.org>", 
> -user=>"anonymous",
>     -port=>'5306', 'db_version' => 75,);
> open (OUTFILE2, ">/outtest6.txt") or die "problem open OUTfile";
> my $geno_dbh = DBI->connect( 
>  'dbi:mysql:dbname=x;host=mysql-mi-dev;port=x',    'x',  'x',   
>  {InactiveDestroy => 1, RaiseError => 1, PrintError => 1}    ) || die 
> "Database connection not made: $DBI::errstr";
> print STDERR "Connection...\n";
>
> my $table_creation = "create table ENSEMBL1 (
> RS_ID VARCHAR(255),
> CONS VARCHAR(255),
> SOURCE VARCHAR(255),
> P_VALUE FLOAT8
> )";
>
> $geno_dbh->do($table_creation);
> my $sql_table = 'INSERT INTO ENSEMBL1  (RS_ID, CONS, SOURCE, P_VALUE) 
> VALUES (?,?,?,?)';
> my $geno_sthout = $geno_dbh->prepare($sql_table);
>
> my $vs_adaptor = 
> Bio::EnsEMBL::Registry->get_adaptor('human','variation','variationset');
> my $vs = $vs_adaptor->fetch_by_name('All phenotype-associated variants');
> my $iterator = $vs->get_Variation_Iterator();
> while ($iterator->has_next()) {
> my $var = $iterator->next();
> my $variant=$var->name();
> my @vfs = @{$var->get_all_VariationFeatures()};
> my $cons=$var->display_consequence ();
> my $source=$var->source;
> my $p_value;
> foreach my $vf (@vfs) {
>  my @pfs = @{$var->get_all_PhenotypeFeatures()};
>   foreach my $pf(@pfs) {
>   $p_value=$pf->p_value;
> print OUTFILE2  $variant, "\t",$cons, "\t",$source, "\t", $p_value, "\n";
> $geno_sthout -> execute($variant, $cons , $source, $p_value) or die 
>  $DBI::errstr;
> }
> }
> }
>
>
> _______________________________________________
> 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/

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