PhyInit: INT(10) != INTEGER

I fixed the Foreign Key problems for some of tables that make references to other PhyloDB tables. However linking to the other BioSQL tables seems to be a problem because the INTEGER values in the Phylo tables are created as INT(11) while the INTEGER values in the other BioSQL tables are INT(10) UNSIGNED.

I am going to make all of the integer values in the PhyloDB tables INT(10) so that the foreign key values will work. This will also make the tables consistent with the rest of BioSQL.

PhyInit: Change to InnoDB tables causes ALTER TABLE errors

Changing the table types to InnoDB now causes problems with using ALTER TABLE to create foreign keys.
For example:

ALTER TABLE tree ADD CONSTRAINT FKNode
FOREIGN KEY (node_id) REFERENCES node (node_id);

is giving the error:

DBD::mysql::db do failed: Can't create table './biosql/#sql-cc7_bba.frm' (errno: 150) at ./PhyInit.pl line 363, <> line 1.

Typing the SQL code directly in the MySQL Command line gives:

ERROR 1005: Can't create table './biosql/#sql-cc7_ba6.frm' (errno: 150)

It looks there may be some help in an online discussion of this issue. It is odd that it flags this as a "Can't create table error" when this is really an ALTER TABLE problem.

Info on Foreign Key constraints is also in the MySQL manual. The conditions for foreign key definitions that are listed in the MySQL manual are:

• Both tables must be InnoDB tables and they must not be TEMPORARY tables.
  All of my tables are now InnoDB tables so this is not the problem.

• Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.
  Both columns in the broken SQL are INT(11) so this is probably not the problem.
  

• In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.
  This is it, adding indexes to the tables fixed the problem.
  

• In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
  This is it, adding indexes to the tables fixed the problem.
  

• Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.
  This is not the problem since the columns are INT(11).
  

• If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.
  This is not the problem since FKnode is a unique value in the database. As a test, I ran the SQL without specifying the symbol, and I still have the error.
  

I am crossing these off the list as I can ..

Transaction Support in MySQL

I am working with the parseTreesPG.pl script to make it work with MySQL and I am having trouble with transaction support. The use of

$dbh->commit();

is currently causing fatal errors with the message

commit ineffective with AutoCommit enabled at ./parseTreesPG.pl line 736

According to the documentation, this error message occurs when AutoCommit is off, or when transactions are not supported by the system you are using.

It looks like transaction support for MySQL has been around for a few years, but I have never worked with transactions before so this is new for me.

I am working through the Requirements for Transaction Support in MySQL to see where the trouble is.

• The version of MySQL I am using (4.0.18-standard) should support transactions
• The version of DBD:MySQL I am using supports transactions
• ISAM and MyISAM tables in MySQL do NOT support transaction support.
  
• The tables that do support transaction support are: BDB, InnoDB and Gemini.

The code that I am using to create tables currenty does not specify the table type, so MyISAM tables are being created. So .. my guess is that the MyISAM tables are the problem.

It looks like I will need to make sure that MySQL is creating InnoDB tables by modifying the PhyInit.pl script CREATE TABLE syntax to specify the table type as INNODB, this would be something like:

CREATE TABLE tree (
tree_id INTEGER NOT NULL auto_increment,
name VARCHAR(32) NOT NULL,
identifier VARCHAR(16),
node_id INTEGER NOT NULL
, PRIMARY KEY (tree_id)
, UNIQUE (name)
,
)TYPE=INNODB;

Week 2 Project update

Below is the weekly progress report email I sent to the Wg-phyloinformatics listserv.

Hi All --

Week 2 Update for: Command Line Topological Query Application for BioSQL

Last week:
* Updated project web page:
- to reflect changes in command line options
- linked to SVN source
- linked to existing code that is relevant to what I am working on
* Modified my original command line options to fit the standards used in the existing BioSQL scripts
* Wrote PhyInit.pl to initialize the phylogenetic data tables for BioSQL
- This currently assumes an existing BioSQL database
- DB handle info can be sent as:
(1) dsn string as ENV variable,
(2) dsn string at command line,
(3) separate command line vars (--host,--driver,--dbname) that are used to create dsn string
- A new DB will be created if a DB with the name in the dsn string does not exist
() This uses the --dbname or a series of split commands to get info from command line --dsn
() SQL create table code is hard coded in MySQL format
- Password can be entered in a 'secure' fashion if not passed at the command line.
* Given an existing DB, only the new tables will be created, existing tables will be deleted
- User is warned before deleting any existing data. This step does a record count to tell the user how many records would be deleted from any existing tables.
* Started PhyImport.pl to import phylogenetic data from NEXUS,Newick files
- Mainly just set up command line options and POD documentation
* Posted changes I made to the schema to get this to work in MySQL on the project code repository
* All new PERL code was place in the project working repository listed below
* The -h or --help command line switch can be used to read POD documentation

This week:
* PhyImport.pl
- Add NEXUS file support
- Add Newick file support
* PhyInit.pl
- Check over POD documentation
- Add ability to use sql in the sqldir, this will create the full BioSQL schema if needed

Project Web: https://www.nescent.org/wg_phyloinformatics/PhyloSoC:Command_Line_Topological_Query_Application_for_BioSQL
Project blog: http://phylosoc2007jestill.blogspot.com
Working repository: http://code.google.com/p/phylosoc2007jestill

PhyInit: Create tables

I added the code to create the phylo tables if they do not exist. The other BioSQL tables are not currently created, and the current version uses hard coded SQL instead of running external SQL code. The current implementation will only work with MySQL.

Download of source available: PhyInit.pl