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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
