[Bioperl-l] bioperl-db failing tests

[Hilmar Lapp]

Hi Michael,

yes it looks like a problem in DBD if DBD::mysql fails to recognize  
that the mysql instance to which it is connected does support  
transactions. You can verify this by writing a simple script that  
tries to open a connection with
{ AutoCommit => 0 } as the parameter hash:

	use DBI;
	my $dbh = DBI->connect("dbi:mysql:database=<yourdb>;host=<yourhost>",
	                       "username","password",
	                       { AutoCommit => 0, RaiseError => 0 });
	die DBI::errstr unless $dbh;
	$dbh->disconnect;

If this succeeds fine then something in Biosql may be related to the  
problem, but otherwise not.

	-hilmar


On Jun 7, 2006, at 12:01 PM, Michael Muratet US-Huntsville wrote:

> Hilmar
>
> Pardon the top post.
>
> I tried the test below and it failed. So, I went back and redid the  
> Innodb configuration (deleted all the index files--they were empty  
> anyway, reinstalled biosql (which was empty,too) and restarted the  
> server. Now, the test below works. I went into the DBD-3.0003 and  
> did a distclean and reinstalled the package, but it fails the one  
> transaction test, too. So, it looks like the problem is in DBD, yes?
>
> We had a RAID 5 drive glitch the day before yesterday and rebuilt  
> it. That's the only thing that's changed that I know of that could  
> have caused the problem with ibxxx files.
>
> I have received a reply on the DBD list. Can you think of anything  
> else I should try from the biosql end?
>
> Thanks a million.
>
> Mike
>
> -----Original Message-----
> From: Hilmar Lapp [mailto:hlapp at gmx.net]
> Sent: Wednesday, June 07, 2006 7:52 AM
> To: Michael Muratet US-Huntsville
> Cc: Bioperl; BioSQL
> Subject: Re: [Bioperl-l] bioperl-db failing tests
>
>
> Hi Michael,
>
> Bioperl-db will open all connections with AutoCommit => 0 in the DBI
> parameter hash. The test you're stumbling over is actually there to
> test that the database  does support transactions, but apparently in
> 5.x versions MySQL no longer silently ignores the AutoCommit
> parameter if it doesn't support transactions (effectively preempting
> the test ...).
>
> Now you say that innodb shows as enabled - i.e., you can confirm that
> you changed the Mysql configuration parameter that designates the
> directory for innodb to store its files?
>
> You can confirm that transactions are supported by simple tests on
> the sql level. Open a mysql shell and do the following:
>
> 	-- BTW 'start transaction;' will (should) work too
> 	mysql> set autocommit = 0;
> 	mysql> insert into biodatabase (name) values ('__dummy__');
> 	mysql> select name from biodatabase where name = '__dummy__';
> 	mysql> rollback;
> 	mysql> select name from biodatabase where name = '__dummy__';
>
> The first SELECT query should return one and the last query should
> return zero rows if transactions are supported, and there shouldn't
> be any error.
>
> If the above succeeds (which I don't expect it to) then it looks like
> the DBD::mysql driver thinks the database doesn't support
> transactions when in reality it does. Let me know the result.
>
> 	-hilmar
>
> On Jun 6, 2006, at 2:34 PM, Michael Muratet US-Huntsville wrote:
>
>> Greetings
>>
>> I am trying to install bioperl-db in preparation for installing a
>> biosql database. I'm running on a Dell PowerEdge with quad dual-
>> core Xeons and RedHat Enterprise v4 and perl 5.8.5 and bioperl
>> 1.5.1.  I have installed mysql v5.0.21 from source with --with-
>> innodb set for the configuration. I installed bioperl-db from cvs.
>> I have the latest DBI and DBD:mysql installed a few weeks ago from
>> CPAN. The installation has been working well with perl otherwise,
>> for example, the Ensembl core API works OK. SHOW ENGINES indicates
>> that innodb is enabled.  I have attached a snippet from the top of
>> the output below. I searched the web and the bioperl-db list and
>> haven't found anything that appears to be relevant. I've done
>> several of these installs and they've pretty much completed without
>> a single glitch. Does anyone have any ideas how to isolate the
>> problem?
>>
>> Thanks
>>
>> Mike
>>
>> [mmuratet at HSV-PROBE bioperl-db]$ make test
>> PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
>> "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
>> t/01dbadaptor.....ok 14/19
>> ------------- EXCEPTION  -------------
>> MSG: failed to open connection: Transactions not supported by  
>> database
>> STACK Bio::DB::DBI::base::new_connection /usr/lib/perl5/site_perl/
>> 5.8.5/Bio/biosql-schema/sql/bioperl-db/blib/lib/Bio/DB/DBI/base.pm: 
>> 255
>> STACK Bio::DB::DBI::base::get_connection /usr/lib/perl5/site_perl/
>> 5.8.5/Bio/biosql-schema/sql/bioperl-db/blib/lib/Bio/DB/DBI/base.pm: 
>> 215
>> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::dbh /usr/lib/perl5/
>> site_perl/5.8.5/Bio/biosql-schema/sql/bioperl-db/blib/lib/Bio/DB/
>> BioSQL/BasePersistenceAdaptor.pm:1477
>> STACK Bio::DB::BioSQL::BaseDriver::prepare_findbyuk_sth /usr/lib/
>> perl5/site_perl/5.8.5/Bio/biosql-schema/sql/bioperl-db/blib/lib/Bio/
>> DB/BioSQL/BaseDriver.pm:518
>> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::_find_by_unique_key /
>> usr/lib/perl5/site_perl/5.8.5/Bio/biosql-schema/sql/bioperl-db/blib/
>> lib/Bio/DB/BioSQL/BasePersistenceAdaptor.pm:927
>> STACK Bio::DB::BioSQL::BasePersistenceAdaptor::find_by_unique_key /
>> usr/lib/perl5/site_perl/5.8.5/Bio/biosql-schema/sql/bioperl-db/blib/
>> lib/Bio/DB/BioSQL/BasePersistenceAdaptor.pm:855
>> STACK toplevel t/01dbadaptor.t:62
>>
>>
>> _______________________________________________
>> Bioperl-l mailing list
>> Bioperl-l at lists.open-bio.org
>> http://lists.open-bio.org/mailman/listinfo/bioperl-l
>>
>
> -- 
> ===========================================================
> : Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
> ===========================================================
>
>
>
>
>

-- 
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================