toby wrote:
> Jerry Stuckle wrote:
>
>>toby wrote:
>>
>>>Jerry Stuckle wrote:
>>>
>>>
>>>>Hi, Alex,
>>>>
>>>>Comments below.
>>>>
>>>>Axel Schwenke wrote:
>>>>
>>>>
>>>>>Jerry Stuckle <jstucklex@attglobal.net> wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Axel Schwenke wrote:
>>>>>>... 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.
>
>
> I think that's exactly what Axel meant, yes.
>
>
>>>>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.
>
>
> The problem you describe is solved by transactional engines.
>

Yes, it is solved by by "transactional engines". But you don't
necessarily need to explicitly use transactions for it. For instance,
INNODB can protect against that, even if you are using autocommit
(effectively otherwise negating transactional operations).

>
>>>>>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.
>
>
> Again, I think that's what he meant.
>

It could be. I can only go by what he said. And sometimes English is
not the best language, especially when discussing technical topics.

>
>>>>>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.
>
>
> I guess you/Axel have covered some of the points where this just isn't
> possible. OP really ought to consider a different engine, no?
>

I agree completely.

Of course, with the additional integrity comes additional overhead.
TANSTAAFL.

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


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