Your message dated Sun, 27 Mar 2005 17:48:16 +0200
with message-id <[EMAIL PROTECTED]>
and subject line Bug#299099: mysql - problems with foreign keys
has caused the attached Bug report to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what I am
talking about this indicates a serious mail system misconfiguration
somewhere. Please contact me immediately.)
Debian bug tracking system administrator
(administrator, Debian Bugs database)
--------------------------------------
Received: (at submit) by bugs.debian.org; 11 Mar 2005 19:11:12 +0000
>From [EMAIL PROTECTED] Fri Mar 11 11:11:12 2005
Return-path: <[EMAIL PROTECTED]>
Received: from dd1516.kasserver.com [81.209.148.207]
by spohr.debian.org with esmtp (Exim 3.35 1 (Debian))
id 1D9pXT-0007eb-00; Fri, 11 Mar 2005 11:11:11 -0800
Received: from [192.168.1.2] (dsl-27-218.utaonline.at [81.189.27.218])
by dd1516.kasserver.com (Postfix) with ESMTP id 139CF175AA0
for <[EMAIL PROTECTED]>; Fri, 11 Mar 2005 20:11:09 +0100 (CET)
Message-ID: <[EMAIL PROTECTED]>
Date: Fri, 11 Mar 2005 20:11:16 +0100
From: Roland Ulbricht <[EMAIL PROTECTED]>
User-Agent: Debian Thunderbird 1.0 (X11/20050116)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: [EMAIL PROTECTED]
Subject: mysql - problems with foreign keys
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Delivered-To: [EMAIL PROTECTED]
X-Spam-Checker-Version: SpamAssassin 2.60-bugs.debian.org_2005_01_02
(1.212-2003-09-23-exp) on spohr.debian.org
X-Spam-Status: No, hits=-8.0 required=4.0 tests=BAYES_00,HAS_PACKAGE
autolearn=no version=2.60-bugs.debian.org_2005_01_02
X-Spam-Level:
Package: mysql-server
Version: 4.0.23_Debian-7-log
I have created several tables and tried to link them using foreign keys.
I used mysql-admin. However it did not work. I asked on irc and several
other people encountered the same problem. We believe that it's either a
bug in mysql-server or mysql-admin.
We were able to reproduce the problem on mysql 4.0.23_Debian-7-log and
4.1.11 (tests were done by two completely seperate persons who met on irc).
I created two InnoDB-tables, without problems, and then tried to create
a foreign key. I used mysql-admin to do this, but using the same
sql-commands on any shell produces the same errors....
**** BEGIN SQL ****
# This table contains a list of computers. No problem here.
CREATE TABLE `Computer` (
`Computer-Name` varchar(50) NOT NULL default '',
PRIMARY KEY (`Computer-Name`),
KEY `Computer-Name` (`Computer-Name`)
) TYPE=InnoDB
# This table contains a list of sound-cards which are built into
computers, so the Computer-column should refer to the above table.
Creating the table and setting up the foreign key worked once. However
after that I was not able to change the foreign-key settings anymore
(error 1005).
CREATE TABLE `Soundkarten` (
`Soundkarten-NR` int(11) NOT NULL auto_increment,
`Soundkarten-Name` varchar(50) default NULL,
`Line-In-Anzahl` int(11) default '0',
`Line-In-Type` varchar(50) default NULL,
`Mic-In-Anzahl` int(11) default '0',
`Mic-In-Type` varchar(50) default NULL,
`Speaker-Out-Anzahl` int(11) default '0',
`Speaker-Out-Type` varchar(50) default NULL,
`Sonstiges-Anzahl/Type` varchar(100) default NULL,
`Joystick` varchar(5) default NULL,
`Sonstiges` varchar(200) default NULL,
`Computer` varchar(50) NOT NULL default '',
PRIMARY KEY (`Soundkarten-NR`),
KEY `Computer` (`Computer`),
CONSTRAINT `new_fk_constraint` FOREIGN KEY (`Computer`) REFERENCES
`Computer` (`Computer-Name`)
) TYPE=InnoDB
# This is a list of processors, same foreign key here, however here I
was never able to introduce the foreign key.
CREATE TABLE `Prozessoren` (
`Prozessor-Nr` int(11) NOT NULL auto_increment,
`Prozessor-Bezeichnung` varchar(50) default NULL,
`Core` int(11) default '0',
`FSB` int(11) default '0',
`Computer` varchar(50) NOT NULL default '',
PRIMARY KEY (`Prozessor-Nr`),
KEY `{1CF61A17-EB07-4F60-A79B-7E29C1609801}` (`Computer`),
KEY `Computer` (`Computer`)
) TYPE=InnoDB
# This is the query I am using to try to configure the foreign key
(Created by MySQL-Administration Tool mysql-admin):
ALTER TABLE `privat`.`Prozessoren` MODIFY COLUMN `Prozessor-Nr` INTEGER
NOT NULL AUTO_INCREMENT,
MODIFY COLUMN `Prozessor-Bezeichnung` VARCHAR(50) ,
MODIFY COLUMN `Core` INTEGER DEFAULT 0,
MODIFY COLUMN `FSB` INTEGER DEFAULT 0,
MODIFY COLUMN `Computer` VARCHAR(50) NOT NULL,
ADD CONSTRAINT `new_fk_constraint` FOREIGN KEY `new_fk_constraint`
(`Computer`)
REFERENCES `Computer` (`Computer-Name`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
**** END SQL *****
This last query was created by mysql-admin. Several people checked it
and confirmed that it should work in their opinion, however it does not
work on any of the tested systems.
We figured out that if we leave out the CONSTRAINT-part and only the
following it works:
"(....)ADD FOREIGN KEY `new_fk_constraint` (`Computer`)
REFERENCES `Computer` (`Computer-Name`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;"
We are not absolutely sure where the bug is...
If the query is correct but doesn't work it's a bug in mysql-server.
If the query is wrong and mysql-admin tries to use it, it's a bug in
mysql-admin.
Thanks alot!
Roland Ulbricht
---------------------------------------
Received: (at 299099-done) by bugs.debian.org; 27 Mar 2005 15:48:23 +0000
>From [EMAIL PROTECTED] Sun Mar 27 07:48:23 2005
Return-path: <[EMAIL PROTECTED]>
Received: from mail3b.westend.com (mail3b2.westend.com) [212.117.79.78]
by spohr.debian.org with esmtp (Exim 3.35 1 (Debian))
id 1DFZzy-0002Se-00; Sun, 27 Mar 2005 07:48:23 -0800
Received: from localhost (localhost [127.0.0.1])
by mail3b2.westend.com (Postfix) with ESMTP id 4F6FA12132F;
Sun, 27 Mar 2005 17:48:21 +0200 (CEST)
Received: from mail3b2.westend.com ([127.0.0.1])
by localhost (mail3b [127.0.0.1]) (amavisd-new, port 20024)
with ESMTP id 04100-05; Sun, 27 Mar 2005 17:48:17 +0200 (CEST)
Received: from app109.intern (gate.lathspell.de [212.117.68.82])
by mail3b2.westend.com (Postfix) with ESMTP id F32DD121449;
Sun, 27 Mar 2005 17:48:16 +0200 (CEST)
Date: Sun, 27 Mar 2005 17:48:16 +0200
From: Christian Hammers <[EMAIL PROTECTED]>
To: Roland Ulbricht <[EMAIL PROTECTED]>,
[EMAIL PROTECTED]
Subject: Re: Bug#299099: mysql - problems with foreign keys
Message-ID: <[EMAIL PROTECTED]>
In-Reply-To: <[EMAIL PROTECTED]>
References: <[EMAIL PROTECTED]>
X-Mailer: Sylpheed-Claws 1.0.3 (GTK+ 1.2.10; x86_64-pc-linux-gnu)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Delivered-To: [EMAIL PROTECTED]
X-Spam-Checker-Version: SpamAssassin 2.60-bugs.debian.org_2005_01_02
(1.212-2003-09-23-exp) on spohr.debian.org
X-Spam-Status: No, hits=-6.0 required=4.0 tests=BAYES_00,HAS_BUG_NUMBER
autolearn=no version=2.60-bugs.debian.org_2005_01_02
X-Spam-Level:
Hello Sean & Roland
On 2005-03-11 Roland Ulbricht wrote:
> CREATE TABLE `Soundkarten` (
> CONSTRAINT `new_fk_constraint` FOREIGN KEY (`Computer`) REFERENCES
...
> CREATE TABLE `Prozessoren` (
...
> ALTER TABLE `Prozessoren`
...
> ADD CONSTRAINT `new_fk_constraint`
> FOREIGN KEY `new_fk_constraint` (`Computer`)
> REFERENCES `Computer` (`Computer-Name`)
> ON DELETE RESTRICT
> ON UPDATE RESTRICT;
It works fine if you don't try to create two constraints
with the same name. `new_fk_constraing` is already used in the table
`soundkarten`.
If you get strange error messages like:
ERROR 1005 (HY000) at line 42:
Can't create table './test/#sql-11e2_38.frm' (errno: 121)
try using the perror program which tells you:
MySQL error: 121 = Duplicate key on write or update
Or simply try "SHOW INNODB STATUS;" which tells you more verbosely:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
050327 17:46:47 Error in foreign key constraint creation for table
`test/#sql-11e2_3f`.
A foreign key constraint of name `test/new_fk_constraint`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
I close the bug report.
bye,
-christian-
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
