| Bug #61471 | FK difficulties in the presence of index prefix-lengths | ||
|---|---|---|---|
| Submitted: | 10 Jun 2011 4:56 | Modified: | 11 Jun 2011 8:04 |
| Reporter: | Dustin Oprea | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.5.12 | OS: | Any (64-bit) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign keys, indexes, prefix length | ||
[10 Jun 2011 4:56]
Dustin Oprea
[10 Jun 2011 5:02]
Dustin Oprea
Also, this is the SHOW ENGINE INNODB STATUS output: | InnoDB | | ===================================== 110610 0:57:54 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 104 1_second, 103 sleeps, 9 10_second, 11 background, 11 flush srv_master_thread log flush and writes: 105 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 17, signal count 17 Mutex spin waits 10, rounds 300, OS waits 1 RW-shared spins 16, rounds 480, OS waits 16 RW-excl spins 0, rounds 0, OS waits 0 Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 110610 0:57:51 Error in foreign key constraint of table inquest/#sql-263c_1: foreign key(documentid, url) references documents(documentid, url): 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.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. ------------ TRANSACTIONS ------------ Trx id counter 74A Purge done for trx's n:o < 744 undo n:o < 0 History list length 26 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 740, not started, OS thread id 7952 MySQL thread id 1, query id 92 localhost root show engine 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 (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 175 OS file reads, 339 OS file writes, 102 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.06 writes/s, 0.06 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276707, node heap has 1 buffer(s) 0.00 hash searches/s, 3.56 non-hash searches/s --- LOG --- Log sequence number 1765109 Log flushed up to 1765109 Last checkpoint at 1751798 0 pending log writes, 0 pending chkp writes 68 log i/o's done, 0.06 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 94111 Buffer pool size 8192 Free buffers 7963 Database pages 228 Old database pages 0 Modified db pages 19 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 156, created 72, written 253 0.00 reads/s, 0.22 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s LRU len: 228, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 9576, state: sleeping 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 ============================ Note that the error indicates that the proposed FK is not compatible with the existing indexes, which is incorrect. The statements in the previous comment work fine when the prefixes are excluded: mysql> alter table documents add unique(documentid,url); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table documentstates add unique(documentid,url); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table documentstates add foreign key(documentid,url) references documents(documentid,url); Query OK, 0 rows affected (0.37 sec) Records: 0 Duplicates: 0 Warnings: 0 Dustin Oprea
[11 Jun 2011 8:04]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Please read at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html: Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.
