MySQL Bugs: #44441: Foreign Keys in combination with Memory storage engine

Bug #44441	Foreign Keys in combination with Memory storage engine
Submitted:	23 Apr 2009 16:29	Modified:	20 Dec 2013 6:52
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	6.1-fk	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The new foreign keys implementation allows for mixing parent and child tables of different engines (with limitations).

Currently the limitations are based on the engines ability to rollback or not.
There is no limitation in regards to a combination that includes a memory table (e.g. memory and myisam).

This can lead to loss of data once the server is bounced ans memory table data is not persistent.

How to repeat:
Create two tables with a foreign key relations where the parent is a memory table and the child is a myisam table.

Insert rows into both

Bounce the server

>> The parent table is empty - foreign key relationship is broken.

Suggested fix:
Do not allow for a creation of a foreign key relationship when one of the tables is a 'memory' table (having both tables 'memory' tables is ok as both will be zero'd out when a server is bounced).

The suggested fix (to disallow foreign keys when parent and child are not in the same engine type) is very limitative.

For this particular case, rather consider disallowing foreign keys when the parent table is a non-persistant table.

Foreign keys with child memory table is generally used for performance. In such cases, loosing the child table would not affect the parent and thus would not break constraints.

6.x project was abandoned years ago.