Daniel,
I tested this with very small test tables, and it did not crash.
You should run CHECK TABLE on both tables. Maybe they are corrupt.
Can you make a repeatable test case that you can email or upload to ftp:
support.mysql.com:/pub/mysql/secret ?
Best regards,
Heikki
..............
[EMAIL PROTECTED]:~/mysql-standard-4.0.18-pc-linux-i686/bin> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table
-> `Prospects` (
-> `LeadNo` mediumint(8) unsigned NOT NULL auto_increment,
-> `MySQLStamp` timestamp(14) NOT NULL,
-> `Client` varchar(70) NOT NULL default '',
-> `Address` varchar(50) NOT NULL default '',
-> `Suburb` varchar(25) NOT NULL default '',
-> `State` char(3) NOT NULL default '',
-> `PostCode` smallint(6) NOT NULL default '0',
-> `BusinessTypeID` smallint(3) unsigned NOT NULL default '0',
-> `TelecomSpend` mediumint(8) unsigned NOT NULL default '0',
-> `ElectricitySpend` mediumint(8) unsigned NOT NULL default '0',
-> `GasSpend` mediumint(8) unsigned NOT NULL default '0',
-> `Turnover` bigint(20) unsigned default '0',
-> `Region` enum('L','N','I') default 'N',
-> `Locations` smallint(6) default '0',
-> `Employees` mediumint(8) unsigned default '0',
-> `ACN` varchar(11) default '',
-> `ParentID` mediumint(8) unsigned NOT NULL default '0',
-> `Autonomous` smallint(3) NOT NULL default '0',
-> `BusinessDescription` varchar(100) default '',
-> `FuelSpend` mediumint(8) unsigned NOT NULL default '0',
-> `WebPage` varchar(50) default '',
-> `SignedBy` mediumint(8) unsigned default '0',
-> `FileNo` mediumint(9) default NULL,
-> `SubNo` mediumint(9) default NULL,
-> `EnteredBy` mediumint(9) NOT NULL default '0',
-> `DateEntered` date default '0000-00-00',
-> `TooSmall` datetime default NULL,
-> `DontCall` datetime default NULL,
-> `Status` smallint(5) unsigned NOT NULL default '0',
-> `Archived` date default NULL,
-> `EnergySupplier` smallint(5) unsigned default '0',
-> `TelecomSupplier` smallint(5) unsigned default '0',
-> PRIMARY KEY (`LeadNo`),
-> KEY `IDX_BusinessTypeID` (`BusinessTypeID`),
-> KEY `IDX_ParentID` (`ParentID`),
-> KEY `IDX_DontCall` (`DontCall`),
-> KEY `IDX_TooSmall` (`TooSmall`),
-> KEY `IDX_PostCode` (`PostCode`),
-> KEY `IDX_State` (`State`),
-> KEY `IDX_FileNo` (`FileNo`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>
mysql> create table
-> `Leads` (
-> `DanPK` mediumint(8) unsigned NOT NULL auto_increment,
-> `TimeStamp` timestamp(14) NOT NULL,
-> `LeadNo` mediumint(9) unsigned NOT NULL default '0',
-> `IssueDate` date default NULL,
-> `IssuedTo` tinyint(3) unsigned NOT NULL default '0',
-> `CompleteDate` date default NULL,
-> `Status` tinyint(3) unsigned NOT NULL default '1',
-> `Telemarketer` mediumint(9) NOT NULL default '0',
-> PRIMARY KEY (`DanPK`),
-> KEY `IDX_LeadNo` (`LeadNo`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into Prospects(LeadNo) values (1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Prospects(LeadNo) values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Prospects(LeadNo) values (3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Prospects(LeadNo) values (4);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> insert into Leads(LeadNo) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Leads(LeadNo) values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Leads(LeadNo) values (3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Leads(LeadNo) values (4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Leads(LeadNo) values (2);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> alter table Leads
-> add foreign key fk_LeadNo ( LeadNo ) references Prospects ( LeadNo );
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
----- Original Message -----
From: "Daniel Kasak" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, July 09, 2004 7:08 AM
Subject: MySQL crash on adding foreign key constraint
> ------------=_1089346615-12596-119
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> Content-Transfer-Encoding: 7bit
> Content-Disposition: inline
>
> Hi all.
>
> I just tried adding a foreign key constraint, and crashed MySQL (
4.0.18 ).
> I tried it again and crashed it again :(
>
> The SQL I'm using is:
>
> ---
>
> alter table Leads
> add foreign key fk_LeadNo ( LeadNo ) references Prospects ( LeadNo );
>
> ---
>
> The tables involved:
>
> `Prospects` (
> `LeadNo` mediumint(8) unsigned NOT NULL auto_increment,
> `MySQLStamp` timestamp(14) NOT NULL,
> `Client` varchar(70) NOT NULL default '',
> `Address` varchar(50) NOT NULL default '',
> `Suburb` varchar(25) NOT NULL default '',
> `State` char(3) NOT NULL default '',
> `PostCode` smallint(6) NOT NULL default '0',
> `BusinessTypeID` smallint(3) unsigned NOT NULL default '0',
> `TelecomSpend` mediumint(8) unsigned NOT NULL default '0',
> `ElectricitySpend` mediumint(8) unsigned NOT NULL default '0',
> `GasSpend` mediumint(8) unsigned NOT NULL default '0',
> `Turnover` bigint(20) unsigned default '0',
> `Region` enum('L','N','I') default 'N',
> `Locations` smallint(6) default '0',
> `Employees` mediumint(8) unsigned default '0',
> `ACN` varchar(11) default '',
> `ParentID` mediumint(8) unsigned NOT NULL default '0',
> `Autonomous` smallint(3) NOT NULL default '0',
> `BusinessDescription` varchar(100) default '',
> `FuelSpend` mediumint(8) unsigned NOT NULL default '0',
> `WebPage` varchar(50) default '',
> `SignedBy` mediumint(8) unsigned default '0',
> `FileNo` mediumint(9) default NULL,
> `SubNo` mediumint(9) default NULL,
> `EnteredBy` mediumint(9) NOT NULL default '0',
> `DateEntered` date default '0000-00-00',
> `TooSmall` datetime default NULL,
> `DontCall` datetime default NULL,
> `Status` smallint(5) unsigned NOT NULL default '0',
> `Archived` date default NULL,
> `EnergySupplier` smallint(5) unsigned default '0',
> `TelecomSupplier` smallint(5) unsigned default '0',
> PRIMARY KEY (`LeadNo`),
> KEY `IDX_BusinessTypeID` (`BusinessTypeID`),
> KEY `IDX_ParentID` (`ParentID`),
> KEY `IDX_DontCall` (`DontCall`),
> KEY `IDX_TooSmall` (`TooSmall`),
> KEY `IDX_PostCode` (`PostCode`),
> KEY `IDX_State` (`State`),
> KEY `IDX_FileNo` (`FileNo`)
> ) TYPE=InnoDB
>
> `Leads` (
> `DanPK` mediumint(8) unsigned NOT NULL auto_increment,
> `TimeStamp` timestamp(14) NOT NULL,
> `LeadNo` mediumint(9) unsigned NOT NULL default '0',
> `IssueDate` date default NULL,
> `IssuedTo` tinyint(3) unsigned NOT NULL default '0',
> `CompleteDate` date default NULL,
> `Status` tinyint(3) unsigned NOT NULL default '1',
> `Telemarketer` mediumint(9) NOT NULL default '0',
> PRIMARY KEY (`DanPK`),
> KEY `IDX_LeadNo` (`LeadNo`)
> ) TYPE=InnoDB
>
> ---
>
> Maybe my syntax of my SQL is wrong ( haven't done this in a while ), but
> it surely shouldn't crash MySQL.
>
> I'm getting the following in the error log:
>
> ---
>
> Number of processes running now: 1
> mysqld process hanging, pid 12801 - killed
> 040709 14:04:29 mysqld restarted
> Warning: Ignoring user change to 'mysql' because the user was set to
> 'mysql' earlier on the command line
> 040709 14:04:30 InnoDB: Database was not shut down normally.
> InnoDB: Starting recovery from log files...
> InnoDB: Starting log scan based on checkpoint at
> InnoDB: log sequence number 0 1299171801
> InnoDB: Doing recovery: scanned up to log sequence number 0 1299171801
> InnoDB: Last MySQL binlog file position 0 2135037, file name
> ./screamer-bin.264
> 040709 14:04:30 InnoDB: Flushing modified pages from the buffer pool...
> 040709 14:04:30 InnoDB: Started
> /usr/sbin/mysqld: ready for connections.
> Version: '4.0.18-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306
>
> Number of processes running now: 1
> mysqld process hanging, pid 1529 - killed
> 040709 14:05:45 mysqld restarted
> Warning: Ignoring user change to 'mysql' because the user was set to
> 'mysql' earlier on the command line
> 040709 14:05:45 InnoDB: Database was not shut down normally.
> InnoDB: Starting recovery from log files...
> InnoDB: Starting log scan based on checkpoint at
> InnoDB: log sequence number 0 1299172664
> InnoDB: Doing recovery: scanned up to log sequence number 0 1299172664
> InnoDB: Last MySQL binlog file position 0 1398, file name
./screamer-bin.265
> 040709 14:05:45 InnoDB: Flushing modified pages from the buffer pool...
> 040709 14:05:45 InnoDB: Started
> /usr/sbin/mysqld: ready for connections.
> Version: '4.0.18-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306
>
> ---
>
> I haven't had ANY issues with this system / MySQL installation, ever.
> Should I create a bug report? The problem is "quite reproducable" ... at
> least for me.
>
> --
> Daniel Kasak
> IT Developer
> NUS Consulting Group
> Level 5, 77 Pacific Highway
> North Sydney, NSW, Australia 2060
> T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> email: [EMAIL PROTECTED]
> website: http://www.nusconsulting.com.au
>
>
> ------------=_1089346615-12596-119
> Content-Type: text/plain; charset=us-ascii
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> ------------=_1089346615-12596-119--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
