Dear Heikki, > are you sure you used the script: > CREATE TABLE Projekt( > ...
Yep. I tried 4.0.1 (on Win98), 4.0.3 and 4.0.4 (on Win2K with SP2), and tonight I tried 4.0.5a-max-nt on Win2K (with SP2, but another machine). Here's the result: C:\mysql\bin>mysql -usuperuser -p projekt < my.dump.projekt.sql Enter password: ******** C:\mysql\bin>mysql -usuperuser -p projekt Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 277 to server version: 4.0.5-beta-max-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW TABLES; +-------------------+ | Tables_in_projekt | +-------------------+ | bewertung | | projekt | | teilnehmer | +-------------------+ 3 rows in set (0.00 sec) Hmm ... seems like 4.0.5 is the first MySQL/InnoDB working for me ... Thanks alot for your help, anyway! Regards, -- Stefan Hinz <[EMAIL PROTECTED]> CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, December 14, 2002 10:28 PM Subject: Re: MySQL crashes with foreign key restraints > Stefan, > > are you sure you used the script: > > CREATE TABLE Projekt( > id SMALLINT UNSIGNED NOT NULL, > PRIMARY KEY (id)) TYPE=InnoDB; > > 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)) TYPE=InnoDB; > > SHOW CREATE TABLE Teilnehmer; > > ? > > On Linux I get: > > heikki@hundin:~/mysql-4.0.3-beta-pc-linux-gnu-i686/bin> mysql test > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 2 to server version: 4.0.3-beta-log > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> CREATE TABLE Projekt( > -> id SMALLINT UNSIGNED NOT NULL, > -> PRIMARY KEY (id)) TYPE=InnoDB; > Query OK, 0 rows affected (0.22 sec) > > mysql> > mysql> 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)) TYPE=InnoDB; > Query OK, 0 rows affected (0.01 sec) > > mysql> > mysql> SHOW CREATE TABLE Teilnehmer; > +------------+-------------------------------------------------------------- > ---- > -------------------------------------------------------------------------- -- > ---- > -------------------------------------------------------------------------- -- > ---- > ----------------------+ > | Table | Create Table > > > | > +------------+-------------------------------------------------------------- > ---- > -------------------------------------------------------------------------- -- > ---- > -------------------------------------------------------------------------- -- > ---- > ----------------------+ > | Teilnehmer | CREATE TABLE `Teilnehmer` ( > `id` smallint(5) unsigned NOT NULL default '0', > `pid` smallint(5) unsigned default NULL, > PRIMARY KEY (`id`), > KEY `IDX_Teilnehmer_1` (`pid`), > FOREIGN KEY (`pid`) REFERENCES `test.Projekt` (`id`) > ) TYPE=InnoDB | > +------------+-------------------------------------------------------------- > ---- > -------------------------------------------------------------------------- -- > ---- > -------------------------------------------------------------------------- -- > ---- > ----------------------+ > 1 row in set (0.00 sec) > > mysql> > > > Please test again! Do you have the default charset set to german? The > default is latin1 which I use. > > Note that > > " > The syntax of a foreign key constraint definition in InnoDB: > > [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) > REFERENCES table_name (index_col_name, ...) > [ON DELETE CASCADE | ON DELETE SET NULL | RESTRICT] > > Both tables have to be InnoDB type and there must be an index where the > foreign key and the referenced key are listed as the first columns. InnoDB > does not auto-create indexes on foreign keys or referenced keys: you have to > create them explicitly. > > Corresponding columns in the foreign key and the referenced key must have > similar internal data types inside InnoDB so that they can be compared > without a type conversion. The size and the signedness of integer types has > to be the same. The length of string types need not be the same. > > ... > > If MySQL gives the error number 1005 from a CREATE TABLE statement, and the > error message string refers to errno 150, then the table creation failed > because a foreign key constraint was not correctly formed. Similarly, if an > ALTER TABLE fails and it refers to errno 150, that means a foreign key > definition would be incorrectly formed for the altered table. > " > > Best regards, > > Heikki Tuuri > Innobase Oy > --- > InnoDB - transactions, hot backup, and foreign key support for MySQL > See http://www.innodb.com, download MySQL-Max from http://www.mysql.com > > sql query > > > ----- Original Message ----- > From: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]> > To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Saturday, December 14, 2002 10:37 PM > Subject: Re: MySQL crashes with foreign key restraints > > > > Dear Heikki, > > > > thanks for the hint! > > > > > 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. > > > > But please note what I said about 4.0.3: > > > > >> 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). > > > > I had the same problems with 4.0.4 on Win2K. > > > > Regards, > > -- > > Stefan Hinz <[EMAIL PROTECTED]> > > CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de> > > Heesestr. 6, 12169 Berlin (Germany) > > Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 > > > > > > ----- Original Message ----- > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Friday, December 13, 2002 9:47 PM > > Subject: Re: MySQL crashes with foreign key restraints > > > > > > > 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 :( > > > > > > --------------------------------------------------------------------- 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
