toby wrote:
> Jerry Stuckle wrote:
>
>>Hi, Alex,
>>
>>Comments below.
>>
>>Axel Schwenke wrote:
>>
>>>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.
>>>
>>
>>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.
>

Depends on your definition of "otherwise unmodified". That sounds like
something different than "unmodified", doesn't it? "Otherwise
unmodified" indicates *something* has changed.

Now - if you just say "MySQL never reads an unmodified record and
rewrites it somewhere else", I will agree.

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

Nope, and I never said it did. But this has nothing to do with
transactions. It has to do with a single row - or even a single column
in one row - being corrupted.

Transactional has to do with multiple operations (generally including
modification of the data) in which all or none must complete. That's
not the case here.

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

Could be. But that's not what he said. He said "not written to...".

Now - if he means data which was not overwritten (or in the progress of
being overwritten), then I will agree.

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

It goes back to the crux of the original poster's problem. He wants to
use an access method which is not crash-safe and is trying to ensure the
integrity of his data - or at least a major portion of it.

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



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================