Wednesday, June 6, 2007

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;

No comments: