Error when defining Foreign Key

Consider the following table definitions:

CREATE TABLE `Games` (
`id` int(10) unsigned NOT NULL auto_increment,
`gameName` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `ix_Games_GameName` (`gameName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Sets` (
`id` int(10) unsigned NOT NULL auto_increment,
`setName` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `ix_Sets_SetName` (`setName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `GameSets` (
`id` int(10) unsigned NOT NULL auto_increment,
`gameId` int(10) unsigned NOT NULL default '0',
`setId` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `ix_GameSets_GamesSets` (`gameId`,`setId`),
KEY `ix_GamesSets_Sets` (`setId`),
KEY `ix_GameSets_Games` (`gameId`),
CONSTRAINT `FK_GameSets_Sets` FOREIGN KEY (`setId`) REFERENCES `Sets`
(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_GameSets_Games` FOREIGN KEY (`gameId`) REFERENCES
`Games`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `GamePieces` (
`id` int(10) unsigned NOT NULL auto_increment,
`gameSetId` int(10) unsigned NOT NULL default '0',
`pieceName` varchar(50) NOT NULL default '',
`pieceType` varchar(25) NOT NULL default '',
`pieceSubType` varchar(25) NOT NULL default '',
`pieceLife` int(10) unsigned NOT NULL default '0',
`pieceDescription` text NOT NULL,
`pieceArtist` varchar(50) NOT NULL default '',
`pieceRarity` varchar(15) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `ix_GamePieces_GameSetId` (`gameSetId`),
KEY `ix_GamePieces_PieceName` (`pieceName`),
KEY `ix_GamePieces_PieceType` (`pieceType`),
KEY `ix_GamePieces_PieceSubType` (`pieceSubType`),
KEY `ix_GamePieces_Set_Piece` (`gameSetId`,`pieceName`),
CONSTRAINT `FK_GamePieces_GameSets` FOREIGN KEY (`gameSetId`)
REFERENCES `GameSets` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The problem I'm having is when I set the foreign key action for ON
DELETE. If I set the value to anything other that RESTRICT, I get the
following error:

"MySQL Error Number 1005
Can't create table './database_directory/#sql-158_51.frm' (errno: 121)"

I get the same error if I try to do the same thing for the GameSets
table. Why? Why can't I set it to any value other than RESTRICT?

As an aside, if I remove the NOT NULL part of the column definition for
the 'gameSetId' column, I still cannot set the ON DELETE action to SET
NULL. Why?

thnx,
Christoph

"Christoph" <christoph.boget@gmail.com> wrote in message
news:1141593488.519663.31770@u72g2000cwu.googlegro ups.com...
>
> The problem I'm having is when I set the foreign key action for ON
> DELETE. If I set the value to anything other that RESTRICT, I get the
> following error:
>
> "MySQL Error Number 1005
> Can't create table './database_directory/#sql-158_51.frm' (errno: 121)"

It works for me, using MySQL 5.0.18 on Windows.
What version of MySQL are you using?

Regards,
Bill K.

"Christoph" <christoph.boget@gmail.com> wrote:
> Consider the following table definitions:

[snip]

Works for me. What MySQL version do you have?

> The problem I'm having is when I set the foreign key action for ON
> DELETE.

For the `FK_GamePieces_GameSets` constraint in table `GamePieces`?

> I get the same error if I try to do the same thing for the GameSets
> table.

You're talking about the `FK_GameSets_Sets` and `FK_GameSets_Games`
constraints, right? Looks all good to me.

> Why? Why can't I set it to any value other than RESTRICT?

Possibly a bug. I tested with latest 5.0 and 5.1, so if it is a bug,
it's already fixed.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Christoph,

please post what

SHOW INNODB STATUS\G

prints about the foreign key error.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php


"Christoph" <christoph.boget@gmail.com> kirjoitti
viestissä:1141593488.519663.31770@u72g2000cwu.goog legroups.com...
> Consider the following table definitions:
>
> CREATE TABLE `Games` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `gameName` varchar(100) NOT NULL default '',
> PRIMARY KEY (`id`),
> KEY `ix_Games_GameName` (`gameName`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> CREATE TABLE `Sets` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `setName` varchar(100) NOT NULL default '',
> PRIMARY KEY (`id`),
> KEY `ix_Sets_SetName` (`setName`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> CREATE TABLE `GameSets` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `gameId` int(10) unsigned NOT NULL default '0',
> `setId` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `ix_GameSets_GamesSets` (`gameId`,`setId`),
> KEY `ix_GamesSets_Sets` (`setId`),
> KEY `ix_GameSets_Games` (`gameId`),
> CONSTRAINT `FK_GameSets_Sets` FOREIGN KEY (`setId`) REFERENCES `Sets`
> (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
> CONSTRAINT `FK_GameSets_Games` FOREIGN KEY (`gameId`) REFERENCES
> `Games`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> CREATE TABLE `GamePieces` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `gameSetId` int(10) unsigned NOT NULL default '0',
> `pieceName` varchar(50) NOT NULL default '',
> `pieceType` varchar(25) NOT NULL default '',
> `pieceSubType` varchar(25) NOT NULL default '',
> `pieceLife` int(10) unsigned NOT NULL default '0',
> `pieceDescription` text NOT NULL,
> `pieceArtist` varchar(50) NOT NULL default '',
> `pieceRarity` varchar(15) NOT NULL default '',
> PRIMARY KEY (`id`),
> KEY `ix_GamePieces_GameSetId` (`gameSetId`),
> KEY `ix_GamePieces_PieceName` (`pieceName`),
> KEY `ix_GamePieces_PieceType` (`pieceType`),
> KEY `ix_GamePieces_PieceSubType` (`pieceSubType`),
> KEY `ix_GamePieces_Set_Piece` (`gameSetId`,`pieceName`),
> CONSTRAINT `FK_GamePieces_GameSets` FOREIGN KEY (`gameSetId`)
> REFERENCES `GameSets` (`id`) ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> The problem I'm having is when I set the foreign key action for ON
> DELETE. If I set the value to anything other that RESTRICT, I get the
> following error:
>
> "MySQL Error Number 1005
> Can't create table './database_directory/#sql-158_51.frm' (errno: 121)"
>
> I get the same error if I try to do the same thing for the GameSets
> table. Why? Why can't I set it to any value other than RESTRICT?
>
> As an aside, if I remove the NOT NULL part of the column definition for
> the 'gameSetId' column, I still cannot set the ON DELETE action to SET
> NULL. Why?
>
> thnx,
> Christoph
>

I'm using MySQL 5.0.13-rc on Linux. Fedora Core 4, to be exact. I've
tried upgrading to 5.0.18 but am having serious problems. I guess I'll
have to start a new post for that. Perhaps after I successfully
upgrade, my problem will go away.

thnx,
Christoph

Below is everything that command shows. I'm not sure how I can run it
to print out the foreign key error, however...?

mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
060306 20:45:42 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2, signal count 2
Mutex spin waits 1, rounds 20, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 0, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 2048
Purge done for trx's n:o < 0 1543 undo n:o < 0 0
History list length 13
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 31184, OS thread id
2968796080
MySQL thread id 1, query id 1 localhost root
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
27 OS file reads, 3 OS file writes, 3 OS fsyncs
0.44 reads/s, 94208 avg bytes/read, 0.05 writes/s, 0.05 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 1.52 non-hash searches/s
---
LOG
---
Log sequence number 0 497956
Log flushed up to 0 497956
Last checkpoint at 0 497956
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.13 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 18324884; in additional pool allocated 865280
Buffer pool size 512
Free buffers 491
Database pages 21
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 21, created 0, written 0
0.34 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 950 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 31184, id 2996267952, state: waiting for server
activity

Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.07 sec)

thnx,
Christoph

Christoph,

"Christoph" <christoph.boget@gmail.com> kirjoitti
viestissä:1141700160.171801.26750@p10g2000cwp.goog legroups.com...
> Below is everything that command shows. I'm not sure how I can run it
> to print out the foreign key error, however...?

if there was a foreign key error during the lifetime of the mysqld process,
then it would output an explanation for it. But in this case there has not
been any.

Now that I look at the error you got, it cannot come from a CREATE TABLE
statement since there is a temporary table involved:

"MySQL Error Number 1005
Can't create table './database_directory/#sql-158_51.frm' (errno: 121)"

Were you running an ALTER TABLE statement when you received that error? What
exect SQL statement did you run?

Errno 121 means a duplicate key error. There might be an orphaned temporary
table:

database_directory.#sql-158_51

inside the InnoDB internal data dictionary.

You can use innodb_table_monitor to check if that is the case:
http://dev.mysql.com/doc/refman/5.0/...b-monitor.html

You can drop the orphaned table using the advice at:
http://dev.mysql.com/doc/refman/5.0/...-datadict.html

Another explanation would be that you are trying to create a foreign key
constraint with a same name that you already have in the database. But it is
hard to say anything about that without seeing the actual SQL statement you
tried to execute.

....

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

> > Below is everything that command shows. I'm not sure how I can run it
> > to print out the foreign key error, however...?
> if there was a foreign key error during the lifetime of the mysqld process,
> then it would output an explanation for it. But in this case there has not
> been any.
> Now that I look at the error you got, it cannot come from a CREATE TABLE
> statement since there is a temporary table involved:
> "MySQL Error Number 1005
> Can't create table './database_directory/#sql-158_51.frm' (errno: 121)"
> Were you running an ALTER TABLE statement when you received that error? What
> exect SQL statement did you run?
> Errno 121 means a duplicate key error. There might be an orphaned temporary
> table:

Here is the full command that is getting executed (created by MySQL
Administrator, though I get the same error if I execute the same
command running the mysql client on linux):

ALTER TABLE `communal_haven`.`GamePieces` DROP FOREIGN KEY
`FK_GamePieces_GameSets`,
ADD CONSTRAINT `FK_GamePieces_GameSets` FOREIGN KEY
`FK_GamePieces_GameSets` (`gameSetId`)
REFERENCES `GameSets` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

If I execute that query, I get the error described in my original post.
What's interesting is that if I break the above into 2 seperate
queries, one for the drop and one for the add then it all works out as
it should. It's only when I perform both in the same query that the
problem occurs.

Should it be like that? Or is this a possible bug? I'm thinking it
may be the latter because it was MySQL's own MySQL Administrator that
generated the query...

thnx,
Christoph