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.

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

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

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