Foreign keys / Transactions in MySQL.. WTF?

[ti@lemonia.org]

David Cantrell wrote:
> On Wed, Aug 01, 2007 at 12:40:24AM -0500, Jonathan T. Rockway wrote:
>
>> Does MySQL guarantee that the filesystem is in a legal state at all
>> times?
>
> I hope not.  I don't ever want to see MySQL know enough about the guts
> of ext3/ufs/jfs/NTFS/whateverthefuck to be able to influence that.

Some databases can pretty much be relied upon to get back to a consistant
state if the power suddenly drops (by means of logs, mostly, same sort of
principle as a journaled filing system). Last time I looked into this in
detail was ages ago.

I don't know if MySQL/InnoDB qualifies.

I do know there's a "hot backup" facility for InnoDB, which they appear to
charge extra for. I've not previously understood in detail what happens if
you try to run a hot backup using InnoDB's usual transaction mechanism to
maintain consistency. (The answer may be "it's fine for a few
seconds/minutes/etc, then connections which try to write find themselves
waiting for locks")

ti'