MySQL Bugs: #8919: Foreign keys handling

Bug #8919	Foreign keys handling
Submitted:	3 Mar 2005 10:14	Modified:	13 May 2010 16:04
Reporter:	[ name withheld ]	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1, 5.0	OS:	Any (all)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If you set the on delete or/and on update events for a foreign key, you get the message:
MySQL error number 1005 Cant create table '.\prokost2\#sql-d0_8b.frm' (errno 150).

The query was the following:

ALTER TABLE `prokost2`.`jpap` DROP FOREIGN KEY `jpap_ibfk_1`,
 ADD CONSTRAINT `jpap_ibfk_1` FOREIGN KEY `jpap_ibfk_1` (`job_id`)
    REFERENCES `job` (`id`)
    ON DELETE SET NULL;

How to repeat:
Just cut the symbol # from the name of the .frm file

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

What does SHOW INNODB STATUS display as the latest foreign key error? What does SHOW CREATE TABLE display for the tables in question?

This bug is also mentioned here: 
http://bugs.mysql.com/bug.php?id=8377

I have also had exactly this type of bug whilst using MySQL administrator to change a foreign key constraint. My SQL query was:

ALTER TABLE `skills-tracker`.`tbllearner` DROP FOREIGN KEY `FK_tbllearner_1`,
 ADD CONSTRAINT `FK_tbllearner_1` FOREIGN KEY `FK_tbllearner_1` (`PersonRef`)
    REFERENCES `tblperson` (`PersonID`);

The solution was to split the above into two statements. eg:

ALTER TABLE `skills-tracker`.`tbllearner` DROP FOREIGN KEY `FK_tbllearner_1`;

ALTER TABLE `skills-tracker`.`tbllearner` ADD CONSTRAINT `FK_tbllearner_1` FOREIGN KEY `FK_tbllearner_1` (`PersonRef`)
    REFERENCES `tblperson` (`PersonID`);

My DB version:
mysql.exe  Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32)

Below is the error log that relates to the last post. It certainly does explain what is going wrong internally .. the foreign key is not being dropped correctly.

=====================================
050523 14:45:32 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 448, signal count 446
Mutex spin waits 473, rounds 7772, OS waits 84
RW-shared spins 847, OS waits 327; RW-excl spins 46, OS waits 37
------------------------
LATEST FOREIGN KEY ERROR
------------------------
050523 14:45:17 Error in foreign key constraint creation for table `skills-tracker/#sql-3d8_18`.
A foreign key constraint of name `skills-tracker/FK_tbllearner_1`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
------------
TRANSACTIONS
------------
Trx id counter 0 406969
Purge done for trx's n:o < 0 406942 undo n:o < 0 0
History list length 5
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 1920
MySQL thread id 24, query id 2572 localhost 127.0.0.1 root
---TRANSACTION 0 406965, not started, OS thread id 2324
MySQL thread id 28, query id 2556 localhost 127.0.0.1 root
---TRANSACTION 0 406935, not started, OS thread id 2544
MySQL thread id 20, query id 2573 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
10545 OS file reads, 3918 OS file writes, 370 OS fsyncs
2.66 reads/s, 16384 avg bytes/read, 0.53 writes/s, 0.16 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 3, seg size 5, is empty
Ibuf for space 0: size 1, free list len 3, seg size 5,
1635 inserts, 1635 merged recs, 30 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 1.59 non-hash searches/s
---
LOG
---
Log sequence number 0 165954288
Log flushed up to   0 165954288
Last checkpoint at  0 165954288
0 pending log writes, 0 pending chkp writes
313 log i/o's done, 0.06 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 16406386; in additional pool allocated 742912
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 10535, created 2732, written 3505
2.66 reads/s, 0.09 creates/s, 0.44 writes/s
Buffer pool hit rate 938 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1600, state: waiting for server activity
Number of rows inserted 334362, updated 0, deleted 0, read 1087487
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Yohansb,

Your test case is incomplete, as is the test case for Bug #8377. Here's a complete one:

create table foo(a int primary key)engine=innodb;
create table bar(a int primary key,constraint b foreign key(a)references foo(a))engine=innodb;
alter table bar drop foreign key b, add constraint b foreign key(a)references foo(a);

Hi!

InnoDB does not support a FOREIGN KEY constraint drop + creation in the same ALTER TABLE. If you look at http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html, such syntax is not listed there.

I am changing this to a feature request.

Regards,

Heikki