On 2002.10.16 00:34 Nigel J. Andrews wrote:
> On Tue, 15 Oct 2002, Tom Lane wrote:
>
> > Bruce Momjian <pgman@candle.pha.pa.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
limitedunderstanding of low level database internals, the lack of transactions is the major cause of the need for the
toolsthat Rusty was asking about.
With PostgreSQL if postmaster cores mid transaction, upon startup because that transaction was not commited,
effectivelythe transaction "never happened"... the corrupt data is just thrown away. With MySQL it is different, the
transactionwill 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
startupscript for mysql (safe_mysqld) is basically a big while true ; do mysqld ; done loop, total down time for these
peopleis 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