Error: Can't locate Spreadsheet/WriteExcel.pm in @INC

[Alicia Mastretta]

Hi all,

I have yet another episode of the Mac nightmare.

When I tried to run export_sql.pl I got the following error:

Can't locate Spreadsheet/WriteExcel.pm in @INC (@INC contains: /sw/lib/perl5 /sw/lib/perl5/darwin /Library/Perl/5.12/darwin-thread-multi-2level /Library/Perl/5.12 /Network/Library/Perl/5.12/darwin-thread-multi-2level /Network/Library/Perl/5.12 /Library/Perl/Updates/5.12.3 /System/Library/Perl/5.12/darwin-thread-multi-2level /System/Library/Perl/5.12 /System/Library/Perl/Extras/5.12/darwin-thread-multi-2level /System/Library/Perl/Extras/5.12 .) at /usr/local/bin/export_sql.pl line 31.
BEGIN failed--compilation aborted at /usr/local/bin/export_sql.pl line 31

I thought there may have been a problem with the installation of Spreadsheet::WriteExcel, so I installed it again with cpan. The error persisted.

I then checked the file /opt/local/lib/perl5/5.12.3/darwin-multi-2level/perllocal.pod to see where it is installed and got:

=head2 Wed Jul 24 15:47:08 2013: C<Module> L<Spreadsheet::WriteExcel|Spreadsheet::WriteExcel>
=over 4
=item *
C<installed into: /opt/local/lib/perl5/site_perl/5.12.3>
=item *
C<LINKTYPE: dynamic>
=item *
C<VERSION: 2.39>

So to me it seems that stacks is not looking in the right place, because the error log doesn't refer to /opt/local... but to Library/Perl/...

I saw that Bill discussed a similar problem in the group. The problem seems to be that the stacks scripts are set to search in usr/local/bin for the appropriate perl script and for whatever Mac reason this is different in my system. By the way, in case its relevant:

perl -V
/sw/lib/perl5
    /sw/lib/perl5/darwin
    /opt/local/lib/perl5/site_perl/5.12.3/darwin-multi-2level
    /opt/local/lib/perl5/site_perl/5.12.3
    /opt/local/lib/perl5/vendor_perl/5.12.3/darwin-multi-2level
    /opt/local/lib/perl5/vendor_perl/5.12.3
    /opt/local/lib/perl5/5.12.3/darwin-multi-2level
    /opt/local/lib/perl5/5.12.3
    /opt/local/lib/perl5/site_perl
    /opt/local/lib/perl5/vendor_perl
    .

So I tried to add the line:

#!/opt/local/bin/perl

at the top of /usr/local/bin/export_sql.pl

But that makes the script not being able to find DBI as stated in this error:

Can't locate DBI.pm in @INC (@INC contains: /sw/lib/perl5 /sw/lib/perl5/darwin /opt/local/lib/perl5/site_perl/5.12.3/darwin-multi-2level /opt/local/lib/perl5/site_perl/5.12.3 /opt/local/lib/perl5/vendor_perl/5.12.3/darwin-multi-2level /opt/local/lib/perl5/vendor_perl/5.12.3 /opt/local/lib/perl5/5.12.3/darwin-multi-2level /opt/local/lib/perl5/5.12.3 /opt/local/lib/perl5/site_perl /opt/local/lib/perl5/vendor_perl .) at /usr/local/bin/export_sql.pl line 30.

So seems that there is a mess with where different modules are installed in my Mac. *Sigh*

Maybe the easiest will be to install Spreadsheet::WriteExcel in the place stacks was looking for it originally? I'm not entirely sure how to do that and the README gives no hints. Somewhere on the line of make install? Or maybe you guys know a clever way of telling the export script what to do?

I hope you can help, I have been faffing around for hours.

Many thanks as always,

Alicia

[Julian Catchen]

Hi Alicia,

You have two versions of Perl installed, one provided by Apple and installed in
/usr/bin (with libraries under /Library and /System among other places) and the
other installed via macports, or another package system. If you install packages
through cpan, you will get Apple's Perl and the packages will go in the Apple
install paths (/Library or /System, etc). If you install Perl module packages
via your macports (or similar) it will go under /opt/local/lib.

This is not a problem related to what Stacks is doing, the Perl interpreter
running the Stacks script is where the system paths are stored.

You can either install the DBI modules using macports so that they are also
located under /opt/local, or you can execute cpan to install WriteExcel under
the Apple hierarchy:

sudo /usr/bin/perl -MCPAN -e shell

Also, if you run export_sql.pl with the explicit interpreter you want you can
change which set of modules it looks for:

/usr/bin/perl /usr/local/bin/export_sql
(gives you the Apple version)

/opt/local/bin/perl /usr/local/bin/export_sql
(gives you the macports version)

Best,

julian

> <https://groups.google.com/forum/#!searchin/stacks-users/Can$27t$20locate$20Spreadsheet$2FWriteExcel.pm$20/stacks-users/HYNx3s_o9yo/WmzOe7MoQBoJ>.

[Alicia Mastretta]

Thank you Julian,

Your answer pointed me in the right place, thank you. However, your solution didn't work straight forward, so below some details of what I end up doing in case is useful to others. Afterwards and a new error I got, this time more related to Stacks, hope you or someone can help.

I. What I did to solve Error: Can't locate Spreadsheet/WriteExcel.pm in @INC due to more than one Perl installation in Mac:

I have a MacPorts installation which at some point installed Perl. It also means that it was using MacPorts' cpan, so cpan Spreadsheet::WriteExcel was installing the module under /opt/local. The line that worked to invoke cpan was:

sudo /usr/bin/cpan Spreadsheet::WriteExcel

Sorry to bother you with Mac-Perl issues, I chose the wrong time in life to switch to Mac (and I regret it so much).


II. Error when running export_sql.pl

Before export_sql.pl I ran: 1) denovo_map in a cluster,  2) created the database and table in the target computer, 3) load_radtags.pl, 4) ran index_radtags.pl. Console since index_radtags:


$ index_radtags.pl -D basicdatacheck_ju01100809 -c
Generating catalog tag index
  Fetching catalog SNPs...done.
  Fetching markers...done.
  Fetching genotypes...done.
  Fetching associated sequences...done.
  Fetching catalog matches...done.
  Assembling catalog tags at the database...done.
  Processing catalog tags
mysql --defaults-file=/Users/ticatla/.my.cnf basicdatacheck_ju01100809 -e "LOAD DATA LOCAL INFILE '/var/folders/gc/zjds5kwd6076ybqkp0mllq6h0000gn/T/catalog_index_cb4GCqD3' INTO TABLE catalog_index"

Now when I try to run export_sql.pl I get:

$ export_sql.pl -D basicdatacheck_ju01100809 -b 1 -a haplo -f Ju01-10-08-09DataCheck.SNP -o tsv -L 3
DBD::mysql::st execute failed: Table 'basicdatacheck_ju01100809.tag_index' doesn't exist at /usr/local/bin/export_sql.pl line 82.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/local/bin/export_sql.pl line 84.


And it seems to hang there. I also tried dropping the database and repeating everything from step 2, but nothing changes.

export_sql.pl hanging has been discussed before in the group. For those guys it worked after a week, and seems you may have done changes to the code since then to speed things up.

Should be patient and wait or is not worth it because "DBD::mysql::st execute failed" and "DBD::mysql::st fetchrow_hashref failed:" are showing up?

Thank you so much,

Alicia

[Julian Catchen]

Hi Alicia,

Glad you resolved the missing modules. As for your current problem, DBI is not
failing due to installation issues, but because a table that export_sql.pl
expects to be present is missing, tag_index:

"DBD::mysql::st execute failed: Table 'basicdatacheck_ju01100809.tag_index'
doesn't exist at /usr/local/bin/export_sql.pl line 82."

This table is built by index_radtags.pl but only if you specify the "-t" flag,
So, if you add that flag in addition to '-c' you will get both a tag and catalog
index.

You can re-execute index_radtags.pl as often as you like, it is non-destructive,
so no original data is changed when it runs.

julian

ps - for other users with a similar issue: MacPorts also provides packages for
most Perl modules if you want to skip using CPAN. Since Apple doesn't provide
packages, you have to use CPAN to install new modules to the Apple install of Perl.

On 7/27/13 2:42 AM, Alicia Mastretta wrote:
> Thank you Julian,
>
> Your answer pointed me in the right place, thank you. However, your solution
> didn't work straight forward, so below some details of what I end up doing in
> case is useful to others. Afterwards and a new error I got, this time more
> related to Stacks, hope you or someone can help.
>

> _I. What I did to solve Error: Can't locate Spreadsheet/WriteExcel.pm in @INC
> due to more than one Perl installation in Mac:_

>
> I have a MacPorts installation which at some point installed Perl. It also means
> that it was using MacPorts' cpan, so cpan Spreadsheet::WriteExcel was installing
> the module under /opt/local. The line that worked to invoke cpan was:
>
> sudo /usr/bin/cpan Spreadsheet::WriteExcel
>
> Sorry to bother you with Mac-Perl issues, I chose the wrong time in life to
> switch to Mac (and I regret it so much).
>
>

> _II. Error when running export_sql.pl_

> <https://groups.google.com/forum/#!searchin/stacks-users/export_sql.pl/stacks-users/uj7-rqNvzOE/zPK_zQEfsSYJ>

> For those guys it worked after a week, and seems you may have done changes to
> the code since then to speed things up.
>
> Should be patient and wait or is not worth it because "DBD::mysql::st execute
> failed" and "DBD::mysql::st fetchrow_hashref failed:" are showing up?
>
> Thank you so much,
>
> Alicia
>
>
>
>
>
> On Friday, July 26, 2013 7:07:57 AM UTC+1, Julian Catchen wrote:
>
> Hi Alicia,
>
> You have two versions of Perl installed, one provided by Apple and installed in
> /usr/bin (with libraries under /Library and /System among other places) and the
> other installed via macports, or another package system. If you install
> packages
> through cpan, you will get Apple's Perl and the packages will go in the Apple
> install paths (/Library or /System, etc). If you install Perl module packages
> via your macports (or similar) it will go under /opt/local/lib.
>
> This is not a problem related to what Stacks is doing, the Perl interpreter
> running the Stacks script is where the system paths are stored.
>
> You can either install the DBI modules using macports so that they are also
> located under /opt/local, or you can execute cpan to install WriteExcel under
> the Apple hierarchy:
>
> sudo /usr/bin/perl -MCPAN -e shell
>

> Also, if you run export_sql.pl <http://export_sql.pl> with the explicit

> interpreter you want you can
> change which set of modules it looks for:
>
> /usr/bin/perl /usr/local/bin/export_sql
> (gives you the Apple version)
>
> /opt/local/bin/perl /usr/local/bin/export_sql
> (gives you the macports version)
>
> Best,
>
> julian
>
> On 7/25/13 3:53 AM, Alicia Mastretta wrote:
> > Hi all,
> >
> > I have yet another episode of the Mac nightmare.
> >

> > When I tried to run export_sql.pl <http://export_sql.pl> I got the

> following error:
> >
> > Can't locate Spreadsheet/WriteExcel.pm in @INC (@INC contains: /sw/lib/perl5
> > /sw/lib/perl5/darwin /Library/Perl/5.12/darwin-thread-multi-2level
> > /Library/Perl/5.12 /Network/Library/Perl/5.12/darwin-thread-multi-2level
> > /Network/Library/Perl/5.12 /Library/Perl/Updates/5.12.3
> > /System/Library/Perl/5.12/darwin-thread-multi-2level
> /System/Library/Perl/5.12
> > /System/Library/Perl/Extras/5.12/darwin-thread-multi-2level
> > /System/Library/Perl/Extras/5.12 .) at /usr/local/bin/export_sql.pl

> <http://export_sql.pl> line 31.

> > BEGIN failed--compilation aborted at /usr/local/bin/export_sql.pl

> <http://export_sql.pl> line 31

> > at the top of /usr/local/bin/export_sql.pl <http://export_sql.pl>

> >
> > But that makes the script not being able to find DBI as stated in this
> error:
> >
> > Can't locate DBI.pm in @INC (@INC contains: /sw/lib/perl5
> /sw/lib/perl5/darwin
> > /opt/local/lib/perl5/site_perl/5.12.3/darwin-multi-2level
> > /opt/local/lib/perl5/site_perl/5.12.3
> > /opt/local/lib/perl5/vendor_perl/5.12.3/darwin-multi-2level
> > /opt/local/lib/perl5/vendor_perl/5.12.3
> > /opt/local/lib/perl5/5.12.3/darwin-multi-2level /opt/local/lib/perl5/5.12.3
> > /opt/local/lib/perl5/site_perl /opt/local/lib/perl5/vendor_perl .) at

> > /usr/local/bin/export_sql.pl <http://export_sql.pl> line 30.

[Alicia Mastretta]

Thank you very much Julian,

I missed I had to use the -t flag. Export_sql is taking is time, but I imagine this is normal.

Sorry for the newbie error and thank you for the effort you put on this.

Best,

Alcia