Professional Web Applications Themes

Error when defining Foreign Key - MySQL

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 CHT=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 CHT=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` ...

  1. #1

    Default 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 CHT=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 CHT=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 CHT=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 CHT=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 Guest

  2. #2

    Default Re: Error when defining Foreign Key

    "Christoph" <christoph.boget> wrote in message
    news:1141593488.519663.31770u72g2000cwu.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.


    Bill Karwin Guest

  3. #3

    Default Re: Error when defining Foreign Key

    "Christoph" <christoph.boget> 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: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  4. #4

    Default Re: Error when defining Foreign Key

    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
    [url]http://www.innodb.com/order.php[/url]


    "Christoph" <christoph.boget> kirjoitti
    viestissä:1141593488.519663.31770u72g2000cwu.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 CHT=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 CHT=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 CHT=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 CHT=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
    >

    Heikki Tuuri Guest

  5. #5

    Default Re: Error when defining Foreign Key

    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

    Christoph Guest

  6. #6

    Default Re: Error when defining Foreign Key

    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 Guest

  7. #7

    Default Re: Error when defining Foreign Key

    Christoph,

    "Christoph" <christoph.boget> kirjoitti
    viestissä:1141700160.171801.26750p10g2000cwp.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:
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html[/url]

    You can drop the orphaned table using the advice at:
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html[/url]

    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
    [url]http://www.innodb.com/order.php[/url]




    Heikki Tuuri Guest

  8. #8

    Default Re: Error when defining Foreign Key

    > > 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

    Christoph Guest

Similar Threads

  1. Replies: 11
    Last Post: June 2nd, 04:01 AM
  2. defining testing server
    By kuhajeya in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 29th, 05:22 AM
  3. Defining a property
    By Maziar Aflatoun in forum ASP.NET Web Services
    Replies: 1
    Last Post: March 29th, 09:09 PM
  4. Defining a new slice
    By Andrew L. Gould in forum FreeBSD
    Replies: 5
    Last Post: February 17th, 01:16 PM
  5. Error msg: Bad foreign object reference
    By dougfolk@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 3
    Last Post: May 18th, 08:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139