MySQL Forums
Forum List  »  InnoDB

Re: Foreign Key Error 121
Posted by: Noga Woronoff
Date: September 26, 2007 09:55AM

It looks like I am having a similar problem in MySQL 5.0.

A new child table is successfully created in MySQL 4.1 but not MySQL 5.0.

Here is the dump of the process I used:

Enter password: ***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
Database changed
mysql> drop database smokeqa;
Query OK, 481 rows affected (6.28 sec)

mysql> create database smokeqa;
Query OK, 1 row affected (0.00 sec)

########################################################################
using the command line tool in a separate window:
mysql smokeqa < C:\mysql\dev1_qa_daily_bu\scholasticQA_smoke_backup.sql

Also, the parent table that of the child table created below is:
CREATE TABLE ic_user (
USER_ID varchar(32) NOT NULL default '',
FIRST_NAME varchar(50) NOT NULL default '',
MIDDLE_NAME varchar(50) default NULL,
LAST_NAME varchar(50) NOT NULL default '',
CODE_LIST_ID varchar(32) default NULL,
SOCIAL_SECURITY_NUMBER varchar(20) default NULL,
BIRTH_DATE datetime default NULL,
USER_NAME varchar(20) NOT NULL default '',
PASSWORD varchar(24) NOT NULL default '',
PASSWORD_SET_DATE timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PASSWORD_HINT varchar(100) default NULL,
PASSWORD_HINT_ANSWER varchar(100) default NULL,
COMMENTS varchar(255) default NULL,
IS_ENABLED tinyint(4) NOT NULL default '1',
SYSTEM_DELIVERED tinyint(4) NOT NULL default '0',
PRIMARY KEY (USER_ID),
KEY IC_USER_IDX_01 (CODE_LIST_ID),
KEY IC_USER_IDX_02 (USER_NAME,PASSWORD,USER_ID),
KEY IC_USER_IDX_03 (USER_NAME,PASSWORD,USER_ID,FIRST_NAME,LAST_NAME),
CONSTRAINT FK1_IC_USER FOREIGN KEY (CODE_LIST_ID) REFERENCES ic_code_list (CODE_LIST_ID)
) ENGINE=InnoDB;

########################################################################
mysql> use smokeqa;
Database changed

mysql> DROP TABLE IF EXISTS slms_explana_data;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE slms_explana_data (
-> user_id varchar(32) NOT NULL,
-> explana_user_id varchar(255) NOT NULL,
-> explana_username varchar(255) NOT NULL,
-> explana_password varchar(255) NOT NULL,
-> explana_password_hint varchar(255),
-> PRIMARY KEY (user_id),
-> CONSTRAINT slms_explana_data_ibfk_1
-> FOREIGN KEY (user_id)
-> REFERENCES ic_user (USER_ID)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> )
-> ENGINE=InnoDB
-> COMMENT='table containing extra account info for DTE logins';
ERROR 1005 (HY000): Can't create table '.\smokeqa\slms_explana_data.frm' (errno: 150)
mysql> show innodb status;

=====================================
070926 11:42:35 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 23 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 334, signal count 334
Mutex spin waits 0, rounds 4048, OS waits 158
RW-shared spins 74, OS waits 37; RW-excl spins 137, OS waits 136
------------------------
LATEST FOREIGN KEY ERROR
------------------------
070926 11:42:23 Error in foreign key constraint of table smokeqa/slms_explana_data:

FOREIGN KEY (user_id)
REFERENCES ic_user (USER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE=InnoDB
COMMENT='table containing extra account info for DTE logins':
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 5836874
Purge done for trx's n:o < 0 5835938 undo n:o < 0 0
History list length 1
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 2408
MySQL thread id 1, query id 3135 localhost 127.0.0.1 root
show innodb status
--------
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
1624 OS file reads, 14024 OS file writes, 1800 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.43 writes/s, 0.17 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 14, seg size 16,
2114 inserts, 2114 merged recs, 58 merges
Hash table size 195193, used cells 41111, node heap has 46 buffer(s)
0.43 hash searches/s, 1.00 non-hash searches/s
---
LOG
---
Log sequence number 3 945416641
Log flushed up to 3 945416641
Last checkpoint at 3 945416641
0 pending log writes, 0 pending chkp writes
1825 log i/o's done, 0.09 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 60825090; in additional pool allocated 2093952
Buffer pool size 3008
Free buffers 1
Database pages 2961
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1614, created 7372, written 11979
0.00 reads/s, 0.00 creates/s, 1.30 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 3416, state: waiting for server activity
Number of rows inserted 892741, 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.00 sec)

mysql>



Thank you much in advance -

Noga

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
4171
April 17, 2009 09:54AM
4530
April 10, 2006 07:06AM
4401
April 10, 2006 07:48AM
3857
April 10, 2006 06:06PM
5148
April 11, 2006 09:08AM
3793
April 11, 2006 02:49PM
3960
September 17, 2007 05:54PM
Re: Foreign Key Error 121
6268
September 26, 2007 09:55AM
3525
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.