Jerry Stuckle <jstucklex@attglobal.net> wrote:
> Axel Schwenke wrote:
>> Jerry Stuckle <jstucklex@attglobal.net> wrote:
>>
>>>So? If the file itself is corrupted, all it will do is recover a
>>>corrupted file. What's the gain there?
>>
>> The gain is, that you have a chance to recover at all. With no files,
>> there is *no* way to recover.
>
> What you don't get it that it's not the presence or absence of the files
> - it's the CONTENTS of the files that matters.

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
> 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.
>
> 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.
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.

>> 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? Depending on exactly where
> the block was split, you could completely screw up that row, bug be very
> difficult to detect. Sure, it's only one row. But data corruption like
> this can be much worse than just losing a row. The latter is easier to
> determine.

Agreed. But then again I don't know how *exactly* MyISAM does those
nonatomic writes. One could imagine that the record is first written
with a "this record is invalid" flag set. As soon as the complete
record was written successfully, this flag is cleared in an atomic
write. I know Monty is very fond of atomic operations.

But still there is no difference to what I said: If mysqld crashes,
there is a good chance that all records that mysqld was writing to
are damaged. Either incomplete or lost or such.

However, there is only very little chance to lose data that was not
written to at the time of the crash.

Dynamic vs. fixed format: Dynamic row format is susceptible to the
following problem: imagine there is a hole between two records that
will be filled by INSERT. The new record contains information about
its used and unused length. While writing the record, mysqld crashes
and garbles the length information. Now this record could look longer
than the original hole and shadow one or more of the following
(otherwise untouched) records. This would be hard to spot. Similar
problems exist with merging holes.

Fixed length records don't have this problem and are therefore more
robust.

>> The MyISAM engine is quite simple. Data and index are held in separate
>> files. Data is structured in records. Whenever a record is modified,
>> it's written to disk immediately (however the operation system might
>> cache this). MyISAM never touches records without need. So if mysqld
>> goes down while in normal operation, only those records can be damaged
>> that were in use by active UPDATE, DELETE or INSERT operations.
>
> But the caching is all too important. It's not unusual to have hundreds
> of MB of disk cache in a busy system. That's a lot of data which can be
> lost.

Sure. But this problem was out of scope. We didn't talk about what
happens if the whole machine goes down, only what happens if mysqld
crashes.

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.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/