PDA

View Full Version : [ubuntu] 8.04, MySQL 5.0, mysterious error



jwatte
November 25th, 2008, 10:51 PM
I'm running 8.04 with LTS, and the mysql-server-5.0 package. I'm trying to create tables with foreign key constraints. This has traditionally been somewhat problematic on MySQL, but is supposed to work well in 5.0, if the following constraints are met:


ERROR 1005: Can't create table './test/fkey_exmpl.frm' (errno: 150)

This means either:

* The referenced table is not an InnoDB table
* The referenced column in other_table is not unique
* The referenced column in other_table has a different type to other_id

In MySQL prior to 5.0, this error can also indicate that:

* No index has been defined on the column other_id

So, I have a case where all of these conditions are fulfilled, but I still cannot create the constraint:



CREATE TABLE `contact` (
`email` varchar(64) NOT NULL default '',
`owner` varchar(64) NOT NULL default '',
PRIMARY KEY (`owner`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `user` (
`email` varchar(64) NOT NULL default '',
`password` varchar(32) default NULL,
`lastvisit` datetime default NULL,
`enabled` int(11) default NULL,
PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> alter table contact add foreign key (owner) references user(email) on delete cascade on update cascade;
ERROR 1005 (HY000): Can't create table './aconf/#sql-187a_150.frm' (errno: 150)
mysql>


Is this a known problem with the 5.0 in 8.04? Or a generally known problem? The Google can't find anything that suggests something I haven't already tried.

Barring that, where does mysql store its database files on the 8.04 install? Perhaps there's some scratch files with the wrong names in that location, and I need to apply the root hammer to remove them?