MySQL Forums
Forum List  »  InnoDB

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
21056
April 07, 2006 08:35AM
6029
April 09, 2006 01:38PM
7112
April 10, 2006 05:52AM
3706
April 17, 2009 09:54AM
4134
April 10, 2006 07:06AM
3876
April 10, 2006 07:48AM
3477
April 10, 2006 06:06PM
4668
April 11, 2006 09:08AM
3343
April 11, 2006 02:49PM
3514
September 17, 2007 05:54PM
5754
September 26, 2007 09:55AM
3095
July 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.