List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 12 2000 1:11pm
Subject:Re: MySQL database corruption, frequency of for average user?
View as plain text  
Hi!

>>>>> "Matthew" == Matthew Costello <matthew@stripped> writes:

<cut>

Matthew> I get corrupted tables fairly often in 3.22 (currently 3.22.29).  I run
Matthew> lots of replicated databases using the update logs and a few Perl
Matthew> scripts.  There are processes which copy the update logs in real-time
Matthew> and each replication group has a process which applies the SQL
Matthew> updates (using the DBI i/f) for those databases in its replication
Matthew> group.  All my DB machines are dual PIIIs with lots (mostly 2GB)
Matthew> of RAM.

Any way you can duplicate this in any way?  If you get corruption
often, then it you probably are using some features that doesn't work
properly and this kills mysqld (which results in corruption of tables
that are written to at the same time).

Normally it's quite easy to find errors like this by following the
methods outlined in the MySQL manual, section 'How to debug the mysql server'

<cut>

Matthew> A fairly common failure is for the currently selected database to
Matthew> change for no reason to the 'mysql' database in the midst of
Matthew> thousands of updates for another database.  This seems to be a
Matthew> timing hole in the mysqld server; this problem has never occured
Matthew> on those machines with a single active database.

If you connect to the right database when you open the connection this
shouldn't happen as a automatic reconnect will attach to this
database.  If you on the other hand use the SQL statement 'use
database' in your script and mysqld restarts or you get an timeout,
the client will connect to the database you specified on connect and
this may be the problem you are facing.

Matthew> Another class of problems are those where the information in memory
Matthew> is corrupted, while the tables on disk are fine.  This normally manifests
Matthew> as duplicate key entries.  Doing a "mysqladmin flush-tables" corrects
Matthew> the problem.

This is something that I haven't heard about before?
Do you have any way to make a repeatable test case of this?

Did you check the table with isamchk after 'flush-tables' to verify
that it wasn't really corrupted? 

By the way, how have you compiled your MySQL version ?  Please repost
with the mysqlbug scrict so that we can get some information about
your setup; In quite many cases problems believed to be MySQL specific
are actually bugs in the compiler or libraries and we need to know
more about your system to be able to rule this out.

Regards,
Monty
Thread
MySQL database corruption, frequency of for average user?Peter Hurd19 Jan
  • Re: MySQL database corruption, frequency of for average user?Patrick Greenwell19 Jan
  • Re: MySQL database corruption, frequency of for average user?sasha19 Jan
  • MySQL database corruption, frequency of for average user?Michael Widenius26 Jan
  • Re: MySQL database corruption, frequency of for average user?Matthew Costello12 Feb
    • Re: MySQL database corruption, frequency of for average user?Michael Widenius12 Feb
Re: MySQL database corruption, frequency of for average user?Peter Hurd19 Jan
  • Re: MySQL database corruption, frequency of for average user?Don Read19 Jan
    • Re: MySQL database corruption, frequency of for average user?Van19 Jan
      • Re: MySQL database corruption, frequency of for average user?Ed Carp19 Jan
        • Re: MySQL database corruption, frequency of for average user?Arion19 Jan
          • Re: MySQL database corruption, frequency of for average user?Michael Widenius26 Jan
        • Re: MySQL database corruption, frequency of for average user?sasha19 Jan
        • Re: MySQL database corruption, frequency of for average user?Van19 Jan