469,159 Members | 1,454 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

error: #1005 - Can't create table '.\_ftl\#sql-918_f.frm' (errno: 150)

43
Good day to all...i always have this error after making an ALTER command ... #1005 - Can't create table '.\_ftl\#sql-918_f.frm' (errno: 150) whats the cause of this error...thanks in advance..
Oct 25 '07 #1
6 8716
amitpatel66
2,367 Expert 2GB
Good day to all...i always have this error after making an ALTER command ... #1005 - Can't create table '.\_ftl\#sql-918_f.frm' (errno: 150) whats the cause of this error...thanks in advance..
Could you please POST the output after executing the below command:

mysql> show innodb status;
Oct 25 '07 #2
daveftl
43
mysql> show innodb status;

| Status


=====================================
071025 19:07:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 54 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7, signal count 7
Mutex spin waits 0, rounds 40, OS waits 2
RW-shared spins 8, OS waits 4; RW-excl spins 1, OS waits 1
------------------------
LATEST FOREIGN KEY ERROR
------------------------
071025 18:17:38 Error in foreign key constraint of table _ftl/#sql-918_f:

FOREIGN KEY (Order_Id, cust_ID) REFERENCES trans_Order (Order_Id,cust_ID):
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 1328
Purge done for trx's n:o < 0 1299 undo n:o < 0 0
History list length 4
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 2868
MySQL thread id 19, query id 345 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
49 OS file reads, 58 OS file writes, 27 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: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 62383, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 98760
Log flushed up to 0 98760
Last checkpoint at 0 98760
0 pending log writes, 0 pending chkp writes
25 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 23277710; in additional pool allocated 1412480
Buffer pool size 960
Free buffers 917
Database pages 42
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 39, created 3, written 35
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
1 read views open inside InnoDB
Main thread id 3208, 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
============================
1 row in set, 1 warning (0.00 sec)

mysql> clear;
mysql>
Oct 25 '07 #3
daveftl
43
this is my sql code:

ALTER TABLE Bottles ADD CONSTRAINT trans_Order_Bottles
FOREIGN KEY (Order_Id, cust_ID) REFERENCES trans_Order (Order_Id,cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT Customer_trans_Order
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT trans_Payment_trans_Order
FOREIGN KEY (Payment_Id) REFERENCES trans_Payment (Payment_Id);

ALTER TABLE trans_Payment ADD CONSTRAINT Customer_trans_Payment
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

is there wrong with the code?
thanks for the help...
Oct 25 '07 #4
amitpatel66
2,367 Expert 2GB
this is my sql code:

ALTER TABLE Bottles ADD CONSTRAINT trans_Order_Bottles
FOREIGN KEY (Order_Id, cust_ID) REFERENCES trans_Order (Order_Id,cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT Customer_trans_Order
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

ALTER TABLE trans_Order ADD CONSTRAINT trans_Payment_trans_Order
FOREIGN KEY (Payment_Id) REFERENCES trans_Payment (Payment_Id);

ALTER TABLE trans_Payment ADD CONSTRAINT Customer_trans_Payment
FOREIGN KEY (cust_ID) REFERENCES Customer (cust_ID);

is there wrong with the code?
thanks for the help...

Can you please check if the table trans_order has the primary key (order_id,cust_id) created? If not create the composite key first in trans_order and then create foreign key
Oct 25 '07 #5
daveftl
43
Can you please check if the table trans_order has the primary key (order_id,cust_id) created? If not create the composite key first in trans_order and then create foreign key
that was the reason of the error...the composite key was not created first...thanks a lot! God bless
Oct 25 '07 #6
amitpatel66
2,367 Expert 2GB
that was the reason of the error...the composite key was not created first...thanks a lot! God bless
Thank you for those Blessings :-)
DO POST back any issues you face in future!!
We will help you out!!
Oct 25 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Andrew | last post: by
1 post views Thread by JS | last post: by
6 posts views Thread by Bruce | last post: by
3 posts views Thread by blindsey | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.