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