Axel Schwenke wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote:
>
>>alf wrote:
>>
>>>Not sure I agree. ext3 enables a quick recovery because there is a
>>>trxlog of the file system itself. In ext2 you can lose files. So there
>>>is a small step froward.
>>
>>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. 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. That's where the
information is stored! And it is constantly rewriting the files to disk.

> DELETE just marks a record as deleted (1 bit). INSERT writes a new
> record at the end of the datafile (or into a hole, if one exists).
> UPDATE is done either in place or as INSERT + DELETE.
>

Yes, I know exactly how MySQL works. Yep, and it has to rewrite a
portion of the file to do all of this.

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

No, no fragmentation. But 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.

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

> There are two exceptions: REPAIR TABLE and OPTIMIZE TABLE. Both
> recreate the datafile with new name and then switch by renaming.
> There is still no chance to lose *both* files.
>

True - but these are so seldom used it's almost not worth talking about.
And even then it's a good idea to backup the database before repairing
or optimizing it.

> Indexes are different, though. Indexes are organized in pages and
> heavily cached. You can even instruct mysqld to never flush modified
> index pages to disk (except at shutdown or cache restructuring).
> However indexes can be rebuilt from scratch, without losing data.
> The only thing lost is the time needed for recovery.
>

True. But that's not a big concern, is it?

>
> HTH, 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
==================

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/

Axel Schwenke wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote:
> > Axel Schwenke wrote:
> >> Jerry Stuckle <jstucklex@attglobal.net> wrote:
> >> ...
> >> 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.

Indeed. Some references here:
http://groups.google.com/group/comp....17a85b71816f98

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

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.

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

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.

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

That is true.

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

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

Yep, a serious problem.

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

I agree there. But there can be other problems as I noted before. And
a single corrupted row may be worse than a completely crashed dataset
because it's so difficult to find that row. For instance - let's say we
have a bank account number which is a string and spans two blocks.
Someone makes a $10M deposit to your account. In the middle MySQL
crashes. The account number is now incorrect - the first 1/2 has been
written to one block but the 2nd 1/2 never made it out. So it credited
the deposit to my account.

Wait a sec - I LIKE that idea! :-)

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

In that way a crash loses at most the last record written (in the case
of an incomplete journal entry). But it still needs a consistent point
(i.e. a backup) to roll forward the log from.

But, as you pointed out, not all OS's support this. They should,
however, for critical data.

And BTW - some even have an option to have their own file system which
is not dependent on the OS at all. They are just provided with a space
on the disk (i.e. a partition) and handle their own I/O completely.
This, obviously, is the most secure because the RDB can handle corrupted
files - they know both the external and internal format for the data.
It's also the most efficient. But it's the hardest to implement.


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

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.

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

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

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

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

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

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

Jerry Stuckle wrote:
> 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
> &g