Re: DBD::Pg::db pg_endcopy failed: ERROR: invalid input syntax for integer:

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: DBD::Pg::db pg_endcopy failed: ERROR: invalid input syntax for integer:

Scott Cain
Hi Seth,

I'm cc'ing the schema mailing list so this can be archived and other people have the opportunity to offer suggestions.

My first guess is that the relationship ontology isn't loaded correctly in the database.  The offending line in the feature_relationship temp file is line 1, so the problem happened very early in the process, which is why I think RO is at fault.  If you execute this command in the psql shell:

  SELECT count(*) FROM cvterm WHERE cv_id IN (SELECT cv_id FROM cv WHERE name='relationship');

what do you get?

Scott

PS: My secondary advice is to set up a system for doing backups; nothing like a hardware failure to point out when this should be done :-)



On Thu, Jul 25, 2013 at 1:06 PM, <[hidden email]> wrote:
Hi Scott,

I've run into the same issue as Gary posted here.  My exact error message is:

DBD::Pg::db pg_endcopy failed: ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY feature_relationship, line 1, column type_id: "" at /usr/local/share/perl/5.14.2/Bio/GMOD/DB/Adapter.pm line 3258, <$fh> line 193922.

However, when I open the feature_relationship temp file the line causing the offence (line 193922) is a blank line after what I presume is the null terminator line of "\."

I had used this GFF to load a Chado database previously, but I've had to rebuild on a new machine due to a massive hardware failure.  Any suggestions?

- Seth Munholland

<quote author='Scott Cain'>
Hi Gary,

This can be caused by a few things, generally related to the Parent and
Derives_from tags.

To track down the line that is causing the problem, you can try to do the
load again, but this time add the "--save_files" argument to keep the
loader from throwing a way the temp files it uses while it's parsing the
GFF file.  Then you can look at the feature_relationship file, identify the
feature_id of the offending feature (it should be in the second number in
the line), and then go to one of the other temp files to help you sort out
what the problematic GFF line is (for example, you could look in the
featureprop temp file for the offending feature_id (again, the second
number) to look for tags that would help you identify the lines, or
featureloc to identify the coordinates).

You can also check the cvterm table for the derives_from and part_of terms
and verify that they belong to the relationship ontology.  Some times
loaders will "take" those terms and move them into other ontologies.

Scott


On Sat, Mar 30, 2013 at 8:43 AM, Moore, Gary <[hidden email]>wrote:

>  I’m having no luck loading some maker output into a clean chado 1.2
> database using gmod_bulk_load_gff3.pl.  I applied the fix suggested in
> the Chado FAQ to no effect.   Before I go any further mucking about,  I’m
> hoping someone – Scott?  - can point me in the right direction as I don’t
> know if this is typically a data issue or ???.  Some relevant versions:
>
>
>
> BioPerl 1.006901
>
> Chado 1.2
>
> Maker-2.10
>
>
>
> Error output:
>
>
>
> Loading data into feature_relationship table ...
>
> DBD::Pg::db pg_endcopy failed: ERROR:  invalid input syntax for integer:
> ""
>
> CONTEXT:  COPY feature_relationship, line 411, column type_id: "" at
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm line 3258, <$fh>
> line
> 419.
>
>
>
> ------------- EXCEPTION: Bio::Root::Exception -------------
>
> MSG: calling endcopy for feature_relationship failed:
>
> STACK: Error::throw
>
> STACK: Bio::Root::Root::throw
> /usr/lib/perl5/site_perl/5.8.8/Bio/Root/Root.pm:472
>
> STACK: Bio::GMOD::DB::Adapter::copy_from_stdin
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm:3258
>
> STACK: Bio::GMOD::DB::Adapter::load_data
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm:3180
>
> STACK: /usr/bin/gmod_bulk_load_gff3.pl:1072
>
>
>
> Thanks
>
> Gary
>
>
>
> Gary Moore, Technical Lead
>
> LCA Digital Commons Project
>
> National Agriculture Library/USDA/ARS
>
> Beltsville MD
>
>
>
>
>
>
> This electronic message contains information generated by the USDA solely
> for the intended recipients. Any unauthorized interception of this message
> or the use or disclosure of the information it contains may violate the
> law
> and subject the violator to civil or criminal penalties. If you believe
> you
> have received this message in error, please notify the sender and delete
> the email immediately.
>
>
> ------------------------------------------------------------------------------
> Own the Future-Intel(R) Level Up Game Demo Contest 2013
> Rise to greatness in Intel's independent game demo contest. Compete
> for recognition, cash, and the chance to get your game on Steam.
> $5K grand prize plus 10 genre and skill prizes. Submit your demo
> by 6/6/13. http://altfarm.mediaplex.com/ad/ck/12124-176961-30367-2
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>


--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot
net
GMOD Coordinator (http://gmod.org/)                     <a href="tel:216-392-3087" value="+12163923087">216-392-3087
Ontario Institute for Cancer Research

------------------------------------------------------------------------------
Own the Future-Intel&reg; Level Up Game Demo Contest 2013
Rise to greatness in Intel's independent game demo contest.
Compete for recognition, cash, and the chance to get your game
on Steam. $5K grand prize plus 10 genre and skill prizes.
Submit your demo by 6/6/13. http://p.sf.net/sfu/intel_levelupd2d
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema

</quote>
Quoted from:
http://gmod.827538.n3.nabble.com/DBD-Pg-db-pg-endcopy-failed-ERROR-invalid-input-syntax-for-integer-tp4030830p4030835.html


_____________________________________
Sent from http://gmod.827538.n3.nabble.com




--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     216-392-3087
Ontario Institute for Cancer Research

------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: DBD::Pg::db pg_endcopy failed: ERROR: invalid input syntax for integer:

Scott Cain
Hi Seth,

Right--RO isn't loaded; there should be over 20 terms from that cv.

Scott



On Thu, Jul 25, 2013 at 1:42 PM, Seth Munholland <[hidden email]> wrote:
Hi Scott,

I just resubmitted my email to the mailing list as well, so I suppose that one can be ignored now.  The output looks like this:

OAC_Rex_dev=# SELECT count(*) FROM cvterm WHERE cv_id IN (SELECT cv_id FROM cv WHERE name='relationship');
 count
-------
     1
(1 row)

OAC_Rex_dev=#

I had a backup system in place for the data, that's how I saved the gffs, but now I've extended it to the entire system, nothing teaches you a lesson quite like failure :P

- Seth


On Thu, Jul 25, 2013 at 1:33 PM, Scott Cain <[hidden email]> wrote:
Hi Seth,

I'm cc'ing the schema mailing list so this can be archived and other people have the opportunity to offer suggestions.

My first guess is that the relationship ontology isn't loaded correctly in the database.  The offending line in the feature_relationship temp file is line 1, so the problem happened very early in the process, which is why I think RO is at fault.  If you execute this command in the psql shell:

  SELECT count(*) FROM cvterm WHERE cv_id IN (SELECT cv_id FROM cv WHERE name='relationship');

what do you get?

Scott

PS: My secondary advice is to set up a system for doing backups; nothing like a hardware failure to point out when this should be done :-)



On Thu, Jul 25, 2013 at 1:06 PM, <[hidden email]> wrote:
Hi Scott,

I've run into the same issue as Gary posted here.  My exact error message is:

DBD::Pg::db pg_endcopy failed: ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY feature_relationship, line 1, column type_id: "" at /usr/local/share/perl/5.14.2/Bio/GMOD/DB/Adapter.pm line 3258, <$fh> line 193922.

However, when I open the feature_relationship temp file the line causing the offence (line 193922) is a blank line after what I presume is the null terminator line of "\."

I had used this GFF to load a Chado database previously, but I've had to rebuild on a new machine due to a massive hardware failure.  Any suggestions?

- Seth Munholland

<quote author='Scott Cain'>
Hi Gary,

This can be caused by a few things, generally related to the Parent and
Derives_from tags.

To track down the line that is causing the problem, you can try to do the
load again, but this time add the "--save_files" argument to keep the
loader from throwing a way the temp files it uses while it's parsing the
GFF file.  Then you can look at the feature_relationship file, identify the
feature_id of the offending feature (it should be in the second number in
the line), and then go to one of the other temp files to help you sort out
what the problematic GFF line is (for example, you could look in the
featureprop temp file for the offending feature_id (again, the second
number) to look for tags that would help you identify the lines, or
featureloc to identify the coordinates).

You can also check the cvterm table for the derives_from and part_of terms
and verify that they belong to the relationship ontology.  Some times
loaders will "take" those terms and move them into other ontologies.

Scott


On Sat, Mar 30, 2013 at 8:43 AM, Moore, Gary <[hidden email]>wrote:

>  I’m having no luck loading some maker output into a clean chado 1.2
> database using gmod_bulk_load_gff3.pl.  I applied the fix suggested in
> the Chado FAQ to no effect.   Before I go any further mucking about,  I’m
> hoping someone – Scott?  - can point me in the right direction as I don’t
> know if this is typically a data issue or ???.  Some relevant versions:
>
>
>
> BioPerl 1.006901
>
> Chado 1.2
>
> Maker-2.10
>
>
>
> Error output:
>
>
>
> Loading data into feature_relationship table ...
>
> DBD::Pg::db pg_endcopy failed: ERROR:  invalid input syntax for integer:
> ""
>
> CONTEXT:  COPY feature_relationship, line 411, column type_id: "" at
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm line 3258, <$fh>
> line
> 419.
>
>
>
> ------------- EXCEPTION: Bio::Root::Exception -------------
>
> MSG: calling endcopy for feature_relationship failed:
>
> STACK: Error::throw
>
> STACK: Bio::Root::Root::throw
> /usr/lib/perl5/site_perl/5.8.8/Bio/Root/Root.pm:472
>
> STACK: Bio::GMOD::DB::Adapter::copy_from_stdin
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm:3258
>
> STACK: Bio::GMOD::DB::Adapter::load_data
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm:3180
>
> STACK: /usr/bin/gmod_bulk_load_gff3.pl:1072
>
>
>
> Thanks
>
> Gary
>
>
>
> Gary Moore, Technical Lead
>
> LCA Digital Commons Project
>
> National Agriculture Library/USDA/ARS
>
> Beltsville MD
>
>
>
>
>
>
> This electronic message contains information generated by the USDA solely
> for the intended recipients. Any unauthorized interception of this message
> or the use or disclosure of the information it contains may violate the
> law
> and subject the violator to civil or criminal penalties. If you believe
> you
> have received this message in error, please notify the sender and delete
> the email immediately.
>
>
> ------------------------------------------------------------------------------
> Own the Future-Intel(R) Level Up Game Demo Contest 2013
> Rise to greatness in Intel's independent game demo contest. Compete
> for recognition, cash, and the chance to get your game on Steam.
> $5K grand prize plus 10 genre and skill prizes. Submit your demo
> by 6/6/13. http://altfarm.mediaplex.com/ad/ck/12124-176961-30367-2
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>


--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot
net
GMOD Coordinator (http://gmod.org/)                     <a href="tel:216-392-3087" value="+12163923087" target="_blank">216-392-3087
Ontario Institute for Cancer Research

------------------------------------------------------------------------------
Own the Future-Intel&reg; Level Up Game Demo Contest 2013
Rise to greatness in Intel's independent game demo contest.
Compete for recognition, cash, and the chance to get your game
on Steam. $5K grand prize plus 10 genre and skill prizes.
Submit your demo by 6/6/13. http://p.sf.net/sfu/intel_levelupd2d
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema

</quote>
Quoted from:
http://gmod.827538.n3.nabble.com/DBD-Pg-db-pg-endcopy-failed-ERROR-invalid-input-syntax-for-integer-tp4030830p4030835.html


_____________________________________
Sent from http://gmod.827538.n3.nabble.com




--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     <a href="tel:216-392-3087" value="+12163923087" target="_blank">216-392-3087
Ontario Institute for Cancer Research



--
Seth Munholland, B.Sc.
Department of Biological Sciences
Rm. 304 Biology Building
University of Windsor
401 Sunset Ave. N9B 3P4
T: (519) 253-3000 Ext: 4755



--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     216-392-3087
Ontario Institute for Cancer Research

------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: DBD::Pg::db pg_endcopy failed: ERROR: invalid input syntax for integer:

Scott Cain
Seth,

Please keep the schema mailing list cc'ed--here is a good example of why: I don't use pgAdminIII, so I have no idea what have might gone wrong.  I imagine there are people on the list who use it though, so maybe they can help.  I do a similar thing with command line tools though (creating "starting" database dumps with pg_dump and then reconstituting as needed by feeding the dump into psql).

Scott



On Fri, Jul 26, 2013 at 12:04 PM, Seth Munholland <[hidden email]> wrote:
Hi Scott,

Thanks for your help yesterday, manually loading the RO fixed that but then I got another error that you suggested someone reload the GO.  I suspect none of the ontologies were properly loaded, I've run them manually and am trying the bulk_load_gff3.pl again, but I wanted to ask for your take on this.  We work with multiple assemblies and so, before the rebuild, we made an empty DB and used it as a template for pgAdminIII to make a new DB then just loaded the gffs as needed.  I tried doing that this time but it seems that the onotologies didn't get copied when using the template DB, any idea why?

- Seth

Seth Munholland, B.Sc.
Department of Biological Sciences
Rm. 304 Biology Building
University of Windsor
401 Sunset Ave. N9B 3P4
T: (519) 253-3000 Ext: 4755


On Thu, Jul 25, 2013 at 1:44 PM, Scott Cain <[hidden email]> wrote:
Hi Seth,

Right--RO isn't loaded; there should be over 20 terms from that cv.

Scott



On Thu, Jul 25, 2013 at 1:42 PM, Seth Munholland <[hidden email]> wrote:
Hi Scott,

I just resubmitted my email to the mailing list as well, so I suppose that one can be ignored now.  The output looks like this:

OAC_Rex_dev=# SELECT count(*) FROM cvterm WHERE cv_id IN (SELECT cv_id FROM cv WHERE name='relationship');
 count
-------
     1
(1 row)

OAC_Rex_dev=#

I had a backup system in place for the data, that's how I saved the gffs, but now I've extended it to the entire system, nothing teaches you a lesson quite like failure :P

- Seth


On Thu, Jul 25, 2013 at 1:33 PM, Scott Cain <[hidden email]> wrote:
Hi Seth,

I'm cc'ing the schema mailing list so this can be archived and other people have the opportunity to offer suggestions.

My first guess is that the relationship ontology isn't loaded correctly in the database.  The offending line in the feature_relationship temp file is line 1, so the problem happened very early in the process, which is why I think RO is at fault.  If you execute this command in the psql shell:

  SELECT count(*) FROM cvterm WHERE cv_id IN (SELECT cv_id FROM cv WHERE name='relationship');

what do you get?

Scott

PS: My secondary advice is to set up a system for doing backups; nothing like a hardware failure to point out when this should be done :-)



On Thu, Jul 25, 2013 at 1:06 PM, <[hidden email]> wrote:
Hi Scott,

I've run into the same issue as Gary posted here.  My exact error message is:

DBD::Pg::db pg_endcopy failed: ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY feature_relationship, line 1, column type_id: "" at /usr/local/share/perl/5.14.2/Bio/GMOD/DB/Adapter.pm line 3258, <$fh> line 193922.

However, when I open the feature_relationship temp file the line causing the offence (line 193922) is a blank line after what I presume is the null terminator line of "\."

I had used this GFF to load a Chado database previously, but I've had to rebuild on a new machine due to a massive hardware failure.  Any suggestions?

- Seth Munholland

<quote author='Scott Cain'>
Hi Gary,

This can be caused by a few things, generally related to the Parent and
Derives_from tags.

To track down the line that is causing the problem, you can try to do the
load again, but this time add the "--save_files" argument to keep the
loader from throwing a way the temp files it uses while it's parsing the
GFF file.  Then you can look at the feature_relationship file, identify the
feature_id of the offending feature (it should be in the second number in
the line), and then go to one of the other temp files to help you sort out
what the problematic GFF line is (for example, you could look in the
featureprop temp file for the offending feature_id (again, the second
number) to look for tags that would help you identify the lines, or
featureloc to identify the coordinates).

You can also check the cvterm table for the derives_from and part_of terms
and verify that they belong to the relationship ontology.  Some times
loaders will "take" those terms and move them into other ontologies.

Scott


On Sat, Mar 30, 2013 at 8:43 AM, Moore, Gary <[hidden email]>wrote:

>  I’m having no luck loading some maker output into a clean chado 1.2
> database using gmod_bulk_load_gff3.pl.  I applied the fix suggested in
> the Chado FAQ to no effect.   Before I go any further mucking about,  I’m
> hoping someone – Scott?  - can point me in the right direction as I don’t
> know if this is typically a data issue or ???.  Some relevant versions:
>
>
>
> BioPerl 1.006901
>
> Chado 1.2
>
> Maker-2.10
>
>
>
> Error output:
>
>
>
> Loading data into feature_relationship table ...
>
> DBD::Pg::db pg_endcopy failed: ERROR:  invalid input syntax for integer:
> ""
>
> CONTEXT:  COPY feature_relationship, line 411, column type_id: "" at
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm line 3258, <$fh>
> line
> 419.
>
>
>
> ------------- EXCEPTION: Bio::Root::Exception -------------
>
> MSG: calling endcopy for feature_relationship failed:
>
> STACK: Error::throw
>
> STACK: Bio::Root::Root::throw
> /usr/lib/perl5/site_perl/5.8.8/Bio/Root/Root.pm:472
>
> STACK: Bio::GMOD::DB::Adapter::copy_from_stdin
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm:3258
>
> STACK: Bio::GMOD::DB::Adapter::load_data
> /usr/lib/perl5/site_perl/5.8.8/Bio/GMOD/DB/Adapter.pm:3180
>
> STACK: /usr/bin/gmod_bulk_load_gff3.pl:1072
>
>
>
> Thanks
>
> Gary
>
>
>
> Gary Moore, Technical Lead
>
> LCA Digital Commons Project
>
> National Agriculture Library/USDA/ARS
>
> Beltsville MD
>
>
>
>
>
>
> This electronic message contains information generated by the USDA solely
> for the intended recipients. Any unauthorized interception of this message
> or the use or disclosure of the information it contains may violate the
> law
> and subject the violator to civil or criminal penalties. If you believe
> you
> have received this message in error, please notify the sender and delete
> the email immediately.
>
>
> ------------------------------------------------------------------------------
> Own the Future-Intel(R) Level Up Game Demo Contest 2013
> Rise to greatness in Intel's independent game demo contest. Compete
> for recognition, cash, and the chance to get your game on Steam.
> $5K grand prize plus 10 genre and skill prizes. Submit your demo
> by 6/6/13. http://altfarm.mediaplex.com/ad/ck/12124-176961-30367-2
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>
>


--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot
net
GMOD Coordinator (http://gmod.org/)                     <a href="tel:216-392-3087" value="+12163923087" target="_blank">216-392-3087
Ontario Institute for Cancer Research

------------------------------------------------------------------------------
Own the Future-Intel&reg; Level Up Game Demo Contest 2013
Rise to greatness in Intel's independent game demo contest.
Compete for recognition, cash, and the chance to get your game
on Steam. $5K grand prize plus 10 genre and skill prizes.
Submit your demo by 6/6/13. http://p.sf.net/sfu/intel_levelupd2d
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema

</quote>
Quoted from:
http://gmod.827538.n3.nabble.com/DBD-Pg-db-pg-endcopy-failed-ERROR-invalid-input-syntax-for-integer-tp4030830p4030835.html


_____________________________________
Sent from http://gmod.827538.n3.nabble.com




--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     <a href="tel:216-392-3087" value="+12163923087" target="_blank">216-392-3087
Ontario Institute for Cancer Research



--
Seth Munholland, B.Sc.
Department of Biological Sciences
Rm. 304 Biology Building
University of Windsor
401 Sunset Ave. N9B 3P4
T: (519) 253-3000 Ext: 4755



--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     <a href="tel:216-392-3087" value="+12163923087" target="_blank">216-392-3087
Ontario Institute for Cancer Research




--
------------------------------------------------------------------------
Scott Cain, Ph. D.                                   scott at scottcain dot net
GMOD Coordinator (http://gmod.org/)                     216-392-3087
Ontario Institute for Cancer Research

------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema