[BioSQL-l] Re: Regarding the Bioperl-db you installed..

[Tim Henderson]

Andrew,

 >> I have bioperl 1.0.2, MySQL 3.23.49-3 (Redhat 7.3) and  various
 >> versions of bioperl-db.

I don't know if this is responsible for your SQL errors, but there are 
other reasons why you should upgrade to the latest stable production 
release of MySQL, currently 3.23.53a. In a previous thread ("Adding 
qualifiers to features on a stored sequence"), Hilmar Lapp told me that 
"MySQL < 3.23.{50,51 - not sure} had the problem of losing all FK 
constraints..."

Also, make sure that you are running the MySQL-Max server (mysqld-max). 
The regular one doesn't support InnoDB tables, which the BioSQL schema 
use. If you feed biosqldb-mysql.sql to a regular MySQL server it won't 
complain, but will silently create the tables as type MyISAM, which 
doesn't support foreign key constraints. This won't cause problems with 
inserts and fetches, but will cause deletes to leave orphaned rows in 
various tables, e.g., seqfeature_qualifier_value.

Further, be aware that even though you have the MySQL-Max server 
installed, you still need to specify some minimal InnoDB configuration 
in the server configuration file (my.cnf); otherwise InnoDB support will 
be disabled and again you will find the server silently converting your 
tables to MyISAM. (I'm speaking here of my experience with MySQL 
3.23.53a; I don't know what is changed/different in 4.0.x). For details 
on configuring InnoDB see:

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB_start


-- Tim