List:General Discussion« Previous MessageNext Message »
From:Rich Schramm Date:May 18 2004 7:04pm
Subject:RE: Alter table primary key and foreign keys
View as plain text  
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:VPendleton@stripped] 
Sent: Tuesday, May 18, 2004 2:07 PM
To: 'Rich Schramm '; 'mysql@stripped '
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: mysql@stripped
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/mysql?unsub=1

Thread
Alter table primary key and foreign keysRich Schramm18 May
RE: Alter table primary key and foreign keysVictor Pendleton18 May
  • RE: Alter table primary key and foreign keysRich Schramm18 May
RE: Alter table primary key and foreign keysVictor Pendleton18 May
Re: Alter table primary key and foreign keysHeikki Tuuri19 May