Rich, I tested this with 4.0.12 on Linux, and it worked ok.
What has probably happened is that you have somehow managed to make the foreign key definition in the child table not to match the column types or names in the parent table. Maybe through DROP TABLE and recreating the parent table. That can cause a crash when InnoDB tries to load the child table definition from its internal data dictionary. I recall seeing this problem in early 4.0.x versions. You should upgrade to 4.0.18. It may have better diagnostics for this problem. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ .................... [EMAIL PROTECTED]:~/mysql-4.0.12/client> ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.12-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table bom_mstr(bom_id int not null, primary key (bom_id)) type = i nnodb; Query OK, 0 rows affected (0.04 sec) mysql> create table forecast(bom_id int not null, fc_date date not null, primary key (bom_id, fc_date), foreign key (bom_id) references bom_mstr(bom_id) on dele te cascade on update cascade) type = innodb; Query OK, 0 rows affected (0.02 sec) mysql> insert into bom_mstr values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into bom_mstr values (2); Query OK, 1 row affected (0.00 sec) mysql> insert into bom_mstr values (3); Query OK, 1 row affected (0.00 sec) mysql> insert into bom_mstr values (4); Query OK, 1 row affected (0.00 sec) mysql> insert into forecast values (1, '2002-04-04'); Query OK, 1 row affected (0.00 sec) mysql> insert into forecast values (1, '2002-04-05'); Query OK, 1 row affected (0.00 sec) mysql> insert into forecast values (2, '2002-04-04'); Query OK, 1 row affected (0.00 sec) mysql> insert into forecast values (2, '2002-04-05'); Query OK, 1 row affected (0.00 sec) mysql> insert into forecast values (3, '2002-04-05'); Query OK, 1 row affected (0.00 sec) mysql> insert into forecast values (4, '2002-04-05'); Query OK, 1 row affected (0.00 sec) mysql> select * from forecast; +--------+------------+ | bom_id | fc_date | +--------+------------+ | 1 | 2002-04-04 | | 1 | 2002-04-05 | | 2 | 2002-04-04 | | 2 | 2002-04-05 | | 3 | 2002-04-05 | | 4 | 2002-04-05 | +--------+------------+ 6 rows in set (0.02 sec) mysql> select * from bom_mstr; +--------+ | bom_id | +--------+ | 1 | | 2 | | 3 | | 4 | +--------+ 4 rows in set (0.00 sec) mysql> alter table bom_mstr modify bom_id int(10) -> auto_increment; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from bom_mstr; +--------+ | bom_id | +--------+ | 1 | | 2 | | 3 | | 4 | +--------+ 4 rows in set (0.00 sec) mysql> show create table bom_mstr; +----------+---------------------------------------------------------------- ---- -------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------- ---- -------------------------------------------+ | bom_mstr | CREATE TABLE `bom_mstr` ( `bom_id` int(10) NOT NULL auto_increment, PRIMARY KEY (`bom_id`) ) TYPE=InnoDB | +----------+---------------------------------------------------------------- ---- -------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from forecast; +--------+------------+ | bom_id | fc_date | +--------+------------+ | 1 | 2002-04-04 | | 1 | 2002-04-05 | | 2 | 2002-04-04 | | 2 | 2002-04-05 | | 3 | 2002-04-05 | | 4 | 2002-04-05 | +--------+------------+ 6 rows in set (0.00 sec) mysql> insert into forecast values (3, '2002-04-06'); Query OK, 1 row affected (0.00 sec) mysql> show create table forecast; +----------+---------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------+ | forecast | CREATE TABLE `forecast` ( `bom_id` int(11) NOT NULL default '0', `fc_date` date NOT NULL default '0000-00-00', PRIMARY KEY (`bom_id`,`fc_date`), FOREIGN KEY (`bom_id`) REFERENCES `bom_mstr` (`bom_id`) ON DELETE CASCADE ON U PDATE CASCADE ) TYPE=InnoDB | +----------+---------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---------------------------------+ 1 row in set (0.00 sec) mysql> show create table bom_mstr; +----------+---------------------------------------------------------------- ---- -------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------- ---- -------------------------------------------+ | bom_mstr | CREATE TABLE `bom_mstr` ( `bom_id` int(10) NOT NULL auto_increment, PRIMARY KEY (`bom_id`) ) TYPE=InnoDB | +----------+---------------------------------------------------------------- ---- -------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into forecast values (3, '2002-04-06'); ERROR 1062: Duplicate entry '3-2002-04-06' for key 1 mysql> select count(*) from forecast; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.01 sec) mysql> describe forecast; +---------+---------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+------------+-------+ | bom_id | int(11) | | PRI | 0 | | | fc_date | date | | PRI | 0000-00-00 | | +---------+---------+------+-----+------------+-------+ 2 rows in set (0.00 sec) mysql> ----- Original Message ----- From: "Victor Pendleton" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, May 18, 2004 11:05 PM Subject: RE: Alter table primary key and foreign keys > Can you mysqldump the table then rebuild the table from the dump file? > > -----Original Message----- > From: Rich Schramm > To: 'Victor Pendleton'; [EMAIL PROTECTED] > Sent: 5/18/04 2:04 PM > Subject: RE: Alter table primary key and foreign keys > > The error log shows nothing when the binary dies. I can't rebuild the > child table - anything that touches the child table after the alter > stops the binary. > > -----Original Message----- > From: Victor Pendleton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 18, 2004 2:07 PM > To: 'Rich Schramm '; '[EMAIL PROTECTED] ' > Subject: RE: Alter table primary key and foreign keys > > > I would first see if an upgrade to a later version of InnoDB tables is > possible. What is being written to the error log? The ALTER TABLE > statement subtly creates a new table, with new contraint names that the > child table is unaware of, and drops the original table. Have you tried > rebuilding the child table? > > -----Original Message----- > From: Rich Schramm > To: [EMAIL PROTECTED] > Sent: 5/18/04 12:43 PM > Subject: Alter table primary key and foreign keys > > I am using mysql 4.0.12 max-nt on Windows XP. > > I have a master table with an int column as a primary key (bom_id) and a > second table that has a foreign key reference to the master column and > uses it as part of a composite key (bom_id, fc_date). Example: > > ****************************** > bom_mstr > primary key(bom_id) > ****************************** > > ****************************** > forecast > primary key(bom_id, fc_date) > FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON > UPDATE CASCADE ) TYPE=InnoDB; > > ****************************** > > This works fine. > > I then altered the table so that the primary key in the master table is > now an auto_increment: alter table bom_mstr modify bom_id int(10) > auto_increment; > > Having done this, column is updated and the values for the records are > set. I can insert into it and query it with no problem. However, when > I try to do anything with the secondary table at this point, it crashes > the entire mysql.exe process. Anything that touches the second table > crashes the binary: > > select count(*) from forecast > describe forecast > delete from bom_mstr (which cascades to forecast). > > All of these crash the binary. > > I have also tried truncating the data in forecast before altering > bom_mstr and I get the same result. > > Anyone seen this before or have any idea??? > > Thanks, > > Rich > > -- > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
