>> > Agreed. But Alf worried he could lose whole tables aka files.
>> >
>> >
>> >>There is very little
>> >>chance you will lose the files completely in the case of a crash. There

Ok, I assume here we are talking about a mysqld crash, NOT an OS crash,
a power failure, or a hardware crash, or a hardware malfunction such
as a disk controller that writes on the wrong sectors or writes random
crap to the correct sectors.

WHY did mysqld crash? One plausible scenario is that it has gone
completely bonkers, e.g. because of a buffer-overflow virus attack
or coding error. Scribbled-on code can do anything. It's even more
likely to do something bad if the buffer-overflow was intentional.

So, you have to assume that mysqld can do anything a rogue user-level
process running with the same privileges will do: such as deleting
all the tables, or interpreting SELECT * FROM ... as DELETE FROM
.... Bye, bye, data. Any time you write data, there is a chance
of writing crap instead (buggy daemon code, buggy OS, buggy hardware,
etc.). Any time you write data, there is a chance of its being
written in the wrong place.

The worst case is considerably less ugly if you assume that mysqld
crashes because someone did a kill -9 on the daemon (it suddenly
stops with correct behavior up to the stopping point) and it is
otherwise bug-free.

The worst case is still very bad but the average case is a lot less
ugly if you assume a "clean" interruption of power: writes to the
hard disk just stop at an arbitrary point. (I have one system where
a particular disk partition usually acquires an unreadable sector
if the system crashes due to power interruption, even though 99% of
the time it's sitting there not accessing the disk, read or write).


>> >>is a much bigger (although admittedly still small) that the files will
>> >>be corrupted. And a huge chance if you have more than one table your
>> >>database will be inconsistent.
>> >>
>> >>
>> >>>However, thats not a real problem. MySQL never touches the datafile
>> >>>itself once it is created. Only exception: REPAIR TABLE. This will
>> >>>recreate the datafile (as new file with extension .TMD) and then
>> >>>rename files.

I believe this is incorrect. OPTIMIZE TABLE and ALTER TABLE (under
some circumstances, such as actually changing the schema) will also
do this. But these aren't used very often.

Now consider what happens when you attempt doing this WITH INSUFFICIENT
DISK SPACE for temporarily having two copies. I believe I have
managed to lose a table this way, although it was a scratch table
and not particularly important anyway. And this scenario has usually
"failed cleanly", although it usually leaves the partition out of
disk space so nothing much else works.

As far as I know there are very few places where MySQL chops a file and
then attempts to re-write it, and these are places where it's re-creating
the file from scratch, with the data already stored in another file
(REPAIR TABLE, OPTIMIZE TABLE, ALTER TABLE, DROP TABLE/CREATE TABLE).
It won't do that for things like mass UPDATE. It may leave some more
unused space in the data file which may be usable later when data is
INSERTed.

>> >>Excuse me? MySQL ALWAYS touches the data file.
>> >
>> >
>> > Sorry, I didn't express myself clear here: MyISAM never touches the
>> > metadata for a data file. The file itself is created with CREATE TABLE.

Writing on a file changes the change-time metadata for the file.
Writing on a file to extend it likely changes the list of blocks
used by a file (if it is extended by enough to add more blocks).

>> > Later on there is data appended to the file or some block inside the
>> > file is modified. But the file itself stays there and there is
>> > virtually no chance to lose it. So indeed there is no gain from using
>> > a filesystem with metadata journaling (in fact most "journaling"
>> > filesystems use the journal only for metadata).
>> >
>> >
>> >>And it is constantly rewriting the files to disk.
>> >
>> > ...
>> >
>> >>Yes, I know exactly how MySQL works. Yep, and it has to rewrite a
>> >>portion of the file to do all of this.
>> >
>> >
>> > What do you call "rewrite"?
>> >
>> > Of cource MySQL writes modified data. MySQL never reads an otherwise
>> > unmodified record and rewrites it somewhere else.

I don't think this is true for operations that copy rows of tables.
But that won't corrupt the source table.

>> >
>>
>> Just what you are calling it. It reads in a block of data and writes it
>> back out to disk.
>
>Note the words "otherwise unmodified" - i.e. not affected by current
>operation.
>
>>
>> Even in variable length rows where the new row is longer than the old
>> one and MySQL appends it to the end of the file, MySQL has to go back
>> and rewrite the original row to mark it as invalid.
>>
>> >
>> >>>Most file operations on MyISAM tables are easier, faster and less
>> >>>risky, if the table uses fixed length records. Then there is no need to
>> >>>collapse adjacent unused records into one, UPDATE can be done in place,
>> >>>there will be no fragmentation and such.
>> >>
>> >>... what happens if the row spans a disk and the
>> >>system crashes between writes, for instance? ...
>> >
>> >
>> > Agreed. But then again I don't know how *exactly* MyISAM does those
>> > nonatomic writes. ...
>> >
>>
>> Part of it is MyISAM. But part of it is the OS, also. For instance,
>> what happens if the row spans two physical blocks of data which are not
>> contiguous? In that case the OS has to write the first block, seek to
>> the next one and write that one.
>>
>> There isn't anything Monty can do about that, unfortunately.
>>
>
>MyISAM doesn't claim to be transactional.
>
>> > However, there is only very little chance to lose data that was not
>> > written to at the time of the crash.
>> >
>>
>> Actually, you would lose all data which wasn't written to the disk.
>
>Axel means, data *already* written which is not being changed, i.e.
>other records.
>
>>
>> > Dynamic vs. fixed format: Dynamic row format is susceptible to the
>> > following problem: ...
>> > Having the whole system crashing is also hard for "real" database
>> > engines. I remember several passages in the InnoDB manual about
>> > certain operating systems ignoring O_DIRECT for the tx log. Also
>> > there may be "hidden" caches in disk controllers and in the disks.
>> >
>> Agreed it's a problem. Most databases handle this with a log/journal
>> which writes directly to the file system and doesn't return until the
>> record is written. Once that is done, the real data is written
>> asynchronously to the tables.
>
>Yes, but how is this relevant to MyISAM?