[Biojava-l] BioSQL problem

[Thomas Down]

On Thu, Oct 17, 2002 at 02:36:23PM -0400, Simon Foote wrote:
> Does the BioSQL implementation require a specific MySQL version.
> 
> I ask because doing a basic addSequence then removeSequence from a 
> BioSQL database is giving me problems.
> 
> The addition works flawlessly, but when I give the sequence ID to remove 
> it, I get the following error:
> 
> org.biojava.bio.BioException: Error removing from BioSQL tables (rolled 
> back successfully)
> 
> I'm using MySQL 3.23.49 on RedHat 7.3.  I'm just wondering if 
> transactional table support is necessary for the deleting to work correctly.

You're right, the BioJava-BioSQL adaptor can't delete
sequences under mysql 3.x, but not due for transactionality
reasons.  For efficiency reasons, it uses multi-table deletes
(where you delete rows from one table based on a join to
one or more other tables).  These are implemented by MySQL 4
and PostgreSQL (which is my preferred database, and what I
did almost all the development work on), but not MySQL 3.

There are actually a few hooks in place which would, in principal,
allow you to implement the delete sequence function under
MySQL 3 without substantually reworking the code -- but you'd 
have to delete every feature individually :-(.
Let me know if you want any help with this.  But I tend to
encourage people to run away from MySQL 3 as fast as possible...

      Thomas.

PS. I'm actually kind-of scared that it says "rolled back successfully".
I'd always assumed that calling rollback() on a Connection to a non-
transactional database would always fail, rather than claiming to
succeed...