We've had problems for a few months with corruption of a MyISAM table,
when deleting many rows. The same setup/code didn't have problems under
3.22.x but started when we switched to 3.23.x - we're now running 3.23.40.
The table is used by a mod_perl application serving webpages running on
the same server as mysql. The only process writing to the table is an
update script running on another server that runs about 5-6 times a day.
All tables in the database holds two sets of information, so we can
easily switch between new data and the previous set.
The update script does roughly the following:
delete half the rows from all tables where version = 1
insert the new data and set version = 1
tell application to use the rows with the version nr. we just put in
About 2 -3 times a week this causes a corruption in the same table. The
table holds about 14.000 rows in total, with the same amount of rows in
each set.
We've generated a large amount of logs and there's no other funny
queries running at the time of update. The problem always occur in the
table "Specifikation" after we execute the following perlcode:
$dbh->do("delete from Specifikation where version='$off'");
We suspect that the problem is related to the fact that we delete ~7000
rows and inserts the same shortly thereafter. The mysql-manual mentions
that you should run an "optimize table" after deleting many rows from a
table, but doesn't list it as an requirement.
Any suggestions as to where the problem lies? Thanks.
Additonal info:
Table after crash
-----------------
mysql> check table Specifikation;
+-----------------------------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------+-------+----------+----------------------------+
| DB_ProConsult.Specifikation | check | warning | Table is marked as crashed |
| DB_ProConsult.Specifikation | check | error | Found 13648 keys of 13983 |
| DB_ProConsult.Specifikation | check | error | Corrupt |
+-----------------------------+-------+----------+----------------------------+
Perl statements
---------------
.....
$dbh->do("delete from Relation where version='$off'");
$dbh->do("delete from Relationstype where version='$off'");
$dbh->do("delete from Specifikation where version='$off'");
$dbh->do("delete from Link where version='$off'");
.........
Server version
--------------
Server version 3.23.40
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Table desc
----------
mysql> desc Specifikation;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| VareNr | varchar(40) | | PRI | | |
| Type | varchar(40) | | PRI | | |
| Tekst | varchar(200) | | | | |
| version | int(11) | | PRI | 0 | |
| idx | int(11) | | PRI | NULL | auto_increment |
+---------+--------------+------+-----+---------+----------------+
--
Med venlig hilsen
-Toem
Tommy Davis, Technical Director
Adapt A/S, Havnegade 41, 2. sal, 1058 København K
Tel +45 3341 1050, Fax +45 3341 1069, http://www.adapt.dk/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
