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

Reply via email to