MySQL Bugs: #3062: table corruption when doing adump at the same time as optimize

Bug #3062	table corruption when doing adump at the same time as optimize
Submitted:	4 Mar 2004 3:00	Modified:	15 Apr 2004 7:10
Reporter:	Pete French	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.18	OS:	FreeBSD (FreeBSD 4.9-RELEASE)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We have a master/slave database setup and every night
we run mysqldump on the slave to create a dump of
the entire database.

On a second machine we also have a script which
runs 'optimize table' on each of the tables on
all our databases, including the slave. Note that it stops
the slave replication before the optimise.

Since upgrading to 4.0.18 (fropm 4.0.16) this has resulted in a
corrupted table every night. Usually one of the large ones. I get the
error 'no rows found in table XXX'  from the dump program
and I need to repair the table in the morning. It is not always
the same table.

On a  hunch that this might be caused by the optimise and
the dump running simultaneously I altered the scripts to do the
optimise before the dump oon that particualr machine.

The corruption has now gone away.

How to repeat:
I havent exoperimented with repeating this in a controlled
environment, but dooing asmuatneous optimise of a a database
wth a dumy should trigger it. Our tables are very large and
take a number of minutes to optimise - I dont know if this
will happen on a smaller database.

Do you think you could create a test script that could create a database with a lot of rows and then runs mysqldump + optimize on it until you get corruption ?

We would need a more information to be able to repeat your problem

- What options did you use to mysqldump ?
- What options did you use to optimize ?
- While optimize + mysqldump where running, was there any other running    statements ?
- What does myisamchk say about the crashed table ?
- Was the slave connected to the master while this happend ?

The best way to get this fixed is to create a couple of test scripts that can be used to repeat this problem.

I am beginning to think there is a general problem with
optimisation of a database tables. The last two days we
have had our main db also freeze up during the nightly
optimisation run. The problem in this case is mysql using
99% CPU and not accepting any connections.

All these machines were upgraded to 4.0.18 recently.
At the same time Imoved from just using '-O' compile
optimisation to the FreeBSD recommended options of
-O2 -fno-rtti -fno-exceptions -felide-constructors -fno-strength-reduce
with the "--enable-assembler
"config option as well.

Since then we have been getting the optimisation problems.
These systems have worked flawlessly for a number of years
up to this point. I am going to go back to just using -O
and see if that helps matters.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".