Re: newbie qs; fixing and checking databases

On 2002.10.16 00:34 Nigel J. Andrews wrote:
> On Tue, 15 Oct 2002, Tom Lane wrote:
>
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Rusty Wright wrote:
> > >> Are there commands provided with postgresql for checking the databases
> > >> and also for repairing them?
> >
> > > We don't have checks because thing rarely break.
> >
> > Or at least: there are seldom any programmable-in-advance patterns to
> > our failures. We prefer to put our efforts into preventing problems
> > rather than cleaning up after 'em...
>
> Does this question mean that there known, detectable data integrity (not
> referential integrity) problems with MySQL?

With the isam and myisam table formats you are subject to corruption due to sudden crashes of the database. From my limited understanding of low level database internals, the lack of transactions is the major cause of the need for the tools that Rusty was asking about.

With PostgreSQL if postmaster cores mid transaction, upon startup because that transaction was not commited, effectively the transaction "never happened"... the corrupt data is just thrown away. With MySQL it is different, the transaction will have been half written to disk when the core happened and that row will be corrupt within the database files. Upon startup of mysqld no rollback is performed and the data remains in a half written state (corrupt). The tools (isamchk, myisamchk and CHECK TABLE table;) exist to fix the corruption (read: delete the row) in that specific table, at the possible cost of referential integrity.

>
> Wondering because it seems an odd question to me and it could be a factor in a
> descision, although the number of MySQL installations out there one would have
> to there is no problem or all those web sites surely would be terribly unhappy.
>

Most people don't notice these problems with MySQL because there are not that many sites out there that are "high volume" enough to have MySQL core in one thread while another is performing a write. When these cores do happen the startup script for mysql (safe_mysqld) is basically a big while true ; do mysqld ; done loop, total down time for these people is typically less than a second and only 1 web page gets an error. Also as part of the startup otions for mysql is/was "auto repair", which I think is effectively some form of "CHECK TABLE table; if corrupt REPAIR TABLE table".

Shawn