There is certainly *something* wrong if the ALTER TABLE statement causes the server to hang; the statement should either succeed or fail with an error but in neither case should the server hang.
I'm still back on 4.0.15 so I'm not in a great position to try the code myself but I see a few odd things in your statements. First of all, you are using InnoDB for 'master' but MyISAM for 'child'; as I recall, MySQL will only enforce foreign keys if both tables in the relationship are using InnoDB as their engines. You'll want to confirm that in the manual of course, I may just be misremembering. Second, your Alter table statement has a Type=InnoDB at the end. According to the manual, you *can* change the type of the table in an ALTER TABLE statement. However, based on past experience with DB2 which works much like MySQL much of the time, you probably can't do two changes in the same statement. You might have better results if you changed the table type in one ALTER TABLE and then added the foreign key in another ALTER TABLE. Or, drop both tables and recreate them so that both are InnoDB, then add the foreign key via ALTER TABLE. You can also create the foreign key while you are creating the 'child' table; that's how I normally do it. However, you may be separating the creation of the 'child' table and the creation of its foreign key into two statements deliberately and that should work correctly. By the way, I can't help but notice that your table design is rather odd. It makes little sense to have child.id be a foreign key pointing to master.id the way you are doing since they will, presumably, never contain the same values. After all, child.id contains the child's ID number while parent.id contains the parent's ID number which will, presumably, be different. Wouldn't it make more sense to do something like this [untested]? create table master (parent_id int(11) not null, parent_name char(20) not null, primary key(parent_id) ) Engine=InnoDB, charset=utf8; create table child (child_id int(11) not null, child_name char(20) not null, parent_id int(11) not null, primary key(child_id) foreign key parent_id references master(parent_id) ) Engine=InnoDB, charset=utf8; This would result in tables like this: Master parent_id parent_name 1 Tom Smith 2 Mary Jones Child child_id child_name parent_id 555 Bonnie Smith 1 689 Ted Jones 2 You could easily look up the names of the parents of the children by joining child.parent_id to parent.parent_id and you could be assured that the child.parent_id was always a value from the Master table. Rhino ----- Original Message ----- From: "Karam Chand" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Tuesday, January 25, 2005 10:49 AM Subject: Serious bug (or my foolishness) with alter table and InnoDB > Hello, > > I am running mysql 4.1.7 on Win2K. > > I have two tables: > > CREATE TABLE `child` ( > `id` int(11) NOT NULL default '0', > `name` char(1) NOT NULL default '', > PRIMARY KEY (`id`,`name`) > > > > ) ENGINE=MyISAM DEFAULT CHARSET=utf8; > > CREATE TABLE `master` ( > > > > `id` int(11) NOT NULL default '0', > `name` char(10) NOT NULL default '', > PRIMARY KEY (`id`,`name`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > If I execute the following statement: > > alter table child add foreign key (id) references > master (id), type = innodb; > > the mysql server hangs and needs to be killed. After > restarting the table child is also lost. > > Is this a known bug? > > Karam > > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - You care about security. So do we. > http://promotions.yahoo.com/new_mail > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 21/01/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 21/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
