MySQL Forums
Forum List  »  InnoDB

Re: Foreign Key Error 121
Posted by: Stephen Lynch
Date: April 11, 2006 09:08AM

Below is table monitor after I ran your script in your first reply. Then I recreated full_fusion db so I could create innodb_table_monitor table in full_fusion

=====================================
060411 16:01:58 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 10, signal count 10
Mutex spin waits 7, rounds 62, OS waits 1
RW-shared spins 21, OS waits 9; RW-excl spins 0, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060411 15:59:59 Error in foreign key constraint creation for table `full_fusion/child`.
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 110145
Purge done for trx's n:o < 0 110141 undo n:o < 0 0
History list length 5
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 3260
MySQL thread id 1, query id 32 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
174 OS file reads, 190 OS file writes, 68 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.92 writes/s, 0.31 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 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.54 non-hash searches/s
---
LOG
---
Log sequence number 0 85500090
Log flushed up to 0 85500090
Last checkpoint at 0 85500090
0 pending log writes, 0 pending chkp writes
47 log i/o's done, 0.15 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25241794; in additional pool allocated 180608
Buffer pool size 1152
Free buffers 1033
Database pages 118
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 118, created 0, written 136
0.00 reads/s, 0.00 creates/s, 0.69 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1760, 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
============================
===========================================
060411 16:01:58 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 2
COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; FOR_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name ID_IND, id 0 11, fields 1/6, type 3
root page 46, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
INDEX: name FOR_IND, id 0 12, fields 1/2, type 0
root page 47, appr.key vals 1, leaf pages 1, size pages 1
FIELDS: FOR_NAME ID
INDEX: name REF_IND, id 0 13, fields 1/2, type 0
root page 48, appr.key vals 1, leaf pages 1, size pages 1
FIELDS: REF_NAME ID
--------------------------------------
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 2
COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name ID_IND, id 0 14, fields 2/6, type 3
root page 49, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
--------------------------------------
TABLE: name full_fusion/innodb_monitor, id 0 3201, columns 5, indexes 1, appr.rows 0
COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name GEN_CLUST_INDEX, id 0 6892, fields 0/4, type 1
root page 45, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
--------------------------------------
TABLE: name full_fusion/innodb_table_monitor, id 0 3202, columns 5, indexes 1, appr.rows 0
COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name GEN_CLUST_INDEX, id 0 6893, fields 0/4, type 1
root page 55, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
--------------------------------------
TABLE: name mysql/innodb_monitor, id 0 3192, columns 5, indexes 1, appr.rows 0
COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name GEN_CLUST_INDEX, id 0 6883, fields 0/4, type 1
root page 51, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
--------------------------------------
TABLE: name mysql/innodb_table_monitor, id 0 3194, columns 5, indexes 1, appr.rows 0
COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name GEN_CLUST_INDEX, id 0 6885, fields 0/4, type 1
root page 53, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
--------------------------------------
TABLE: name mysql/innodb_tablespace_monitor, id 0 3193, columns 5, indexes 1, appr.rows 0
COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name GEN_CLUST_INDEX, id 0 6884, fields 0/4, type 1
root page 52, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================

Options: ReplyQuote


Subject
Views
Written By
Posted
22667
April 07, 2006 08:35AM
6490
April 09, 2006 01:38PM
7544
April 10, 2006 05:52AM
4172
April 17, 2009 09:54AM
4530
April 10, 2006 07:06AM
4401
April 10, 2006 07:48AM
3857
April 10, 2006 06:06PM
Re: Foreign Key Error 121
5148
April 11, 2006 09:08AM
3793
April 11, 2006 02:49PM
3960
September 17, 2007 05:54PM
6269
September 26, 2007 09:55AM
3526
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.