Re: Serious bug (or my foolishness) with alter table and InnoDB
Karam, - Original Message - From: Karam Chand [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 25, 2005 8:58 PM Subject: RE: Serious bug (or my foolishness) with alter table and InnoDB Hello, I just checked with the same version at home and it works. Dont know whats the problem at office. do the tables contain data? Does the data satisfy the FOREIGN KEY constraint you are trying to add? Will check again? It is best not to add any extra clauses to a plain: ALTER TABLE ... TYPE=InnoDB; Few people try to run such complex statements, and there may be bugs there. Karam Regards, Heikki --- Artem Koltsov [EMAIL PROTECTED] wrote: Works fine on WinXP 4.1.8. Only generates warning: mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead | +-+--+--+ And here is the `child` table after execution: mysql show create table child; +---+ - | Table | Create Table +---+ - | child | CREATE TABLE `child` ( `id` int(11) NOT NULL default '0', `name` char(1) NOT NULL default '', PRIMARY KEY (`id`,`name`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `master` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+ - -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 10:50 AM To: mysql@lists.mysql.com 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] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
RE: Serious bug (or my foolishness) with alter table and InnoDB
Works fine on WinXP 4.1.8. Only generates warning: mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead | +-+--+--+ And here is the `child` table after execution: mysql show create table child; +---+ - | Table | Create Table +---+ - | child | CREATE TABLE `child` ( `id` int(11) NOT NULL default '0', `name` char(1) NOT NULL default '', PRIMARY KEY (`id`,`name`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `master` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+ - -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 10:50 AM To: mysql@lists.mysql.com 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] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Serious bug (or my foolishness) with alter table and InnoDB
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_idparent_name 1Tom Smith 2Mary Jones Child child_idchild_nameparent_id 555 Bonnie Smith 1 689 TedJones2 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]
Re: Serious bug (or my foolishness) with alter table and InnoDB
There was a serious (threat of data loss) bug in 4.1.7 InnoDB . I'm not very familiar with the specifics, but this problem could be that. In any case I'd be scared to run InnoDB on a 4.1.7 without fully understanding the problem. You should definitely consider upgrading to 4.1.8 , or, at the very least, look into the bugs that were fixed in 4.1.8 and their workarounds. Chris Karam Chand wrote: 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]
Re: Serious bug (or my foolishness) with alter table and InnoDB
Can somebody try this on 4.1.7? I will download 4.1.8 tomorrow and check it out. Damn...my connection is slow (on a dialup) and 35MB of download...h Regards, Karam --- Chris [EMAIL PROTECTED] wrote: There was a serious (threat of data loss) bug in 4.1.7 InnoDB . I'm not very familiar with the specifics, but this problem could be that. In any case I'd be scared to run InnoDB on a 4.1.7 without fully understanding the problem. You should definitely consider upgrading to 4.1.8 , or, at the very least, look into the bugs that were fixed in 4.1.8 and their workarounds. Chris Karam Chand wrote: 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] __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Serious bug (or my foolishness) with alter table and InnoDB
Thanks for your ultra detailed reply. The tables are just samples (no where related to actual table). Well I am not that bad in db designing also ;) I checked the manual - you indeed can execute two statements like this in one. And if you see the query I am even changing the table type in the later part of the query. Executing it separately causes no problem but I just wanted to know why the two comnined throw up an error. Regards, karam --- Rhino [EMAIL PROTECTED] wrote: 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_idparent_name 1Tom Smith 2Mary Jones Child child_idchild_nameparent_id 555 Bonnie Smith 1 689 TedJones2 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 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
RE: Serious bug (or my foolishness) with alter table and InnoDB
Hello, I just checked with the same version at home and it works. Dont know whats the problem at office. Will check again? Karam --- Artem Koltsov [EMAIL PROTECTED] wrote: Works fine on WinXP 4.1.8. Only generates warning: mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead | +-+--+--+ And here is the `child` table after execution: mysql show create table child; +---+ - | Table | Create Table +---+ - | child | CREATE TABLE `child` ( `id` int(11) NOT NULL default '0', `name` char(1) NOT NULL default '', PRIMARY KEY (`id`,`name`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `master` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+ - -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 10:50 AM To: mysql@lists.mysql.com 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] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
