MySQL Forums :: InnoDB :: Foreign Key Error 121


Advanced Search

Foreign Key Error 121
Posted by: Stephen Lynch ()
Date: April 07, 2006 08:35AM

Im getting this error when running the script generated by the output from mysqldump on a production database.

The steps are as follows:

- On Production db, mysqldump the db
- On the testing db, drop database x, create database x ( to remove all tables)
- use x; source production.sql

This fails with the following:

060407 15:28:03 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 60, signal count 58
Mutex spin waits 70, rounds 685, OS waits 29
RW-shared spins 60, OS waits 30; RW-excl spins 1, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060407 14:42:36 Error in foreign key constraint creation for table `full_fusion/staffmember`.
A foreign key constraint of name `full_fusion/FK_staffmembers_3`
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.
------------
TRANSACTIONS
------------
Trx id counter 0 106501
Purge done for trx's n:o < 0 106478 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 2232
MySQL thread id 10, query id 12455 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 0, not started, OS thread id 2808
MySQL thread id 3, query id 12454 localhost 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (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
75 OS file reads, 4311 OS file writes, 2735 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 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 74821, used cells 1468, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 85166645
Log flushed up to 0 85166645
Last checkpoint at 0 85166645
0 pending log writes, 0 pending chkp writes
1094 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25241826; in additional pool allocated 858752
Buffer pool size 1152
Free buffers 568
Database pages 581
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 65, created 1683, written 1843
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 3540, state: waiting for server activity
Number of rows inserted 30284, updated 160, deleted 6, read 15726
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


The production.sql does NOT have any duplicate constraints within it, so its not a problem with the exported sql file.

If I open the ibdata file, and search for "FK_staffmembers_3", I find multiple results. Even If I Drop the database again, its still in the ibdata file.

Why does it not remove it from the ibdata file after a drop database command?
Is this potentially causing a problem when I do a drop and create db continously?

btw, version is: mysql Ver 14.7 Distrib 4.1.14, for Win32 (ia32)

Options: ReplyQuote


Subject Views Written By Posted
Foreign Key Error 121 20879 Stephen Lynch 04/07/2006 08:35AM
Re: Foreign Key Error 121 5987 Heikki Tuuri 04/09/2006 01:38PM
Re: Foreign Key Error 121 7062 Stephen Lynch 04/10/2006 05:52AM
Re: Foreign Key Error 121 3663 Jim Maher 04/17/2009 09:54AM
Re: Foreign Key Error 121 4090 Heikki Tuuri 04/10/2006 07:06AM
Re: Foreign Key Error 121 3820 Stephen Lynch 04/10/2006 07:48AM
Re: Foreign Key Error 121 3364 Heikki Tuuri 04/10/2006 06:06PM
Re: Foreign Key Error 121 4629 Stephen Lynch 04/11/2006 09:08AM
Re: Foreign Key Error 121 3306 Heikki Tuuri 04/11/2006 02:49PM
Re: Foreign Key Error 121 3472 Nathan Alden 09/17/2007 05:54PM
Re: Foreign Key Error 121 5710 Noga Woronoff 09/26/2007 09:55AM
Re: Foreign Key Error 121 3055 Dave Page 07/07/2008 06:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.