Stefan, please upgrade to 4.0.5 and test again.
4.0.1 is a very old alpha release, and lots of bugs have been fixed since. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query .................... Subject: MySQL crashes with foreign key restraints From: Stefan Hinz Date: Fri, 13 Dec 2002 16:17:56 +0100 ---------------------------------------------------------------------------- ---- Dear list, I am using MySQL-max 4.0.1 on Win 98 Second Edition with InnoDB as default table type. Using DeZign, I created the following SQL file with three tables (I left out all those other CHAR and TEXT fields here): CREATE TABLE Projekt( id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY (id)); CREATE TABLE Teilnehmer( id SMALLINT UNSIGNED NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES Projekt (id), PRIMARY KEY (id), INDEX IDX_Teilnehmer_1 (pid)); CREATE TABLE Bewertung( pid SMALLINT UNSIGNED NOT NULL, #FOREIGN KEY (pid) REFERENCES Projekt (id), INDEX IDX_Bewertung_1 (pid)); Importing this in MySQL batch mode makes the MySQL server crash immediately. Everything works fine for table Projekt and Teilnehmer, but the line I commented out here for table Bewertung makes MySQL crash. Table Bewertung doesn't have a primary key, that's all the difference. Starting the server again and logging in, I find the first two tables are fine, but SHOW TABLE STATUS reports only NULL fields for table Bewertung, and InnoDB complains that it has no fields at all. Trying to drop this broken table results in 'table Bewertung doesn't exist'. To be able to delete the table (and the database), I have to delete Bewertung.frm manually. This makes InnoDB complain at server start like this: InnoDB: MySQL database directory from another database? InnoDB: Have you copied the .frm file of the table to the InnoDB: data dictionary though MySQL is trying to drop it. InnoDB: Error: table projekt/bewertung does not exist in the InnoDB internal InnoDB: MySQL database directory from another database? InnoDB: Have you copied the .frm file of the table to the InnoDB: data dictionary though MySQL is trying to drop it. InnoDB: Error: table projekt/bewertung does not exist in the InnoDB internal have moved .frm files to another database? Is there something wrong with my foreign key restraints, or is this a bug in InnoDB / MySQL? P.S. I tried the same with 4.0.3-max-nt on a Win2K box. Here, the server doesn't crash, but the response is "can't create '.\projekt\teilnehmer.frm'" with error 150 (unknown error). Commenting out the FOREIGN KEY lines solves the problem, but then again, I have no f.k.restraints :( Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php