Hi,
I created an index on a foreign in a table before. I need to drop this index. but I got the following error:
mysql> alter table transaction drop index prodcode;
ERROR 1025 (HY000): Error on rename of './datacube/#sql-30e8_3' to './datacube/transaction' (errno: 150)
mysql>
mysql> show index from transaction;
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| transaction | 0 | PRIMARY | 1 | transcode | A | 161361 | NULL | NULL | | BTREE | |
| transaction | 1 | custcode | 1 | custcode | A | 810 | NULL | NULL | | BTREE | |
| transaction | 1 | prodcode | 1 | prodcode | A | 911 | NULL | NULL | | BTREE | |
| transaction | 1 | date | 1 | date | A | 1014 | NULL | NULL | | BTREE | |
| transaction | 1 | netsales | 1 | netsales | A | 14669 | NULL | NULL | | BTREE | |
| transaction | 1 | salesvolume | 1 | salesvolume | A | 197 | NULL | NULL | | BTREE | |
| transaction | 1 | custcode_2 | 1 | custcode | A | 139 | NULL | NULL | | BTREE | |
| transaction | 1 | custcode_2 | 2 | date | A | 26893 | NULL | NULL | | BTREE | |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.02 sec)
mysql> show index from inventory;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| inventory | 0 | PRIMARY | 1 | prodcode | A | 2377 | NULL | NULL | | BTREE | |
| inventory | 1 | basename | 1 | basename | A | 30 | NULL | NULL | YES | BTREE | |
| inventory | 1 | vendorname | 1 | vendorname | A | 27 | NULL | NULL | YES | BTREE | |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)
mysql> show innodb status;
=====================================
050124 10:50:54 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1372, signal count 1344
Mutex spin waits 2173, rounds 23570, OS waits 375
RW-shared spins 889, OS waits 444; RW-excl spins 661, OS waits 553
------------------------
LATEST FOREIGN KEY ERROR
------------------------
050124 10:49:59 Error in foreign key constraint of table `datacube/transaction`:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match to the ones in the referenced table. Constraint:
,
CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`prodcode`) REFERENCES `inventory` (`prodcode`) ON DELETE CASCADE------------
TRANSACTIONS
------------
Trx id counter 0 3143526
Purge done for trx's n:o < 0 3143526 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 3143514, not started, OS thread id 167162880
MySQL thread id 3, query id 69 localhost root
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (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
79139 OS file reads, 100705 OS file writes, 6249 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 369, seg size 371, is empty
Ibuf for space 0: size 1, free list len 369, seg size 371,
1834532 inserts, 1834532 merged recs, 48442 merges
Hash table size 34679, 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 973297780
Log flushed up to 0 973297780
Last checkpoint at 0 973297780
0 pending log writes, 0 pending chkp writes
3619 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13336062; in additional pool allocated 221440
Buffer pool size 512
Free buffers 0
Database pages 511
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 89072, created 24682, written 131060
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 165823488, state: waiting for server activity
Number of rows inserted 1130467, updated 0, deleted 0, read 1133755
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
+--------------------------------------------------------------
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
