List:General Discussion« Previous MessageNext Message »
From:Jens Bierkandt Date:August 23 2004 8:35am
Subject:Table needs to be repaired once a week
View as plain text  
Hi,

we are experiencing a strange problem with a table of our MySQL-Server.
About every week this table gets corrupt and we have to repair it 
manually. Besides a couple other tables and databases there is another 
table who gets corrupted but not that often. Both tables are the 
"biggest" on the system (15MB and 45MB). We did some "stress-tests" but 
we could not reproduce the error. We do not know where the problem is 
(hardware, software) and would appreciate any hints or links...

So far, we got down to the following:
- A repair table always works in seconds
- No heavy traffic (two insert/minute) on the table
- The table has an average rowlength of 2KB.

We enabled the logging and got the following output:

General Query Log:
040707 10:01:39   18922 Connect     xxx@localhost on
                   18922 Init DB     xxx
                   18922 Query       select id, nickname, email, blocked
from user where session='1234' and confirmed='1' and
blocked='0'
                   18922 Query       select id from email_block where
email='xx@stripped'
                   18922 Query       select id, nickname, blocked from
user where email='xx@stripped' and confirmed='1' and blocked='0'
                   18922 Query       select id from game where
gameid='1234'
                   18922 Query       insert into game
(gameid,insel,startpunkt,nacht,spieler1,spieler2,farbe1,farbe2,model1,model2,zeit1,zeit2,rennen1,rennen2,time1,time2,hide1,hide2,gmail,nachricht,zzeiten1,

zzeiten2) values 
('1234',2,4,'0',3952,0,12,0,8,0,32249,0,'-6000,-5996,-5983,-5961,-5931,-5890,-5862,-5840,-5830,-5845,-5874,-5884,-5858,-5788,-5677,-5544,-5375,-5178,-4977,-4790,-4649,-4520,-4370,-4190,-3995,-3785,-3568,-3339,-3104,-2869,-2645,-2427,-2247,-2153,-2123,-2124,-2156,-2240,-2379,-2544,-2661,-2682,-2684,-2714,-2820,-2990,-3190,-3406,-3615,-3796,-3943,-4077,-4176,-4220,-4240,-4253,-4262,-4245,-4217,-4186,-4154,-4133,-4120,-4109,-4098#1060,1053,1027,969,874,744,582,394,183,-46,-282,-520,-756,-983,-1193,-1391,-1557,-1689,-1818,-1966,-2155,-2355,-2539,-2695,-2832,-2947,-3049,-3105,-3095,-3058,-2981,-2882,-2734,-2528,-2297,-2059,-1824,-1603,-1414,-1244,-1045,-927,-825,-697,-576,-504,-447,-371,-265,-114,72,270,486,718,955,1195,1434,1673,1911,2149,2387,2625,2865,3105,3344','',1089187299,0,'0','0','xx@stripped','','11916,19000,24999','')
                   18922 Quit
040707 10:01:53   18923 Connect     xxx@localhost on
                   18923 Init DB     xxx
                   18923 Query       select id, email, points, lose,
wins, blocked from user where nickname='xxx' and password='xxx' and
confirmed='1' and blocked='0'
                   18923 Query       update user set
session_date=1089187313, session='1234' where id=6436
                   18923 Query       select count(distinct points) as
anzahl from user where points>87 and blocked='0'
                   18923 Query       select count(id) as anzahl from game
where gmail='xx@stripped' and spieler2=0 and hide2='0'
                   18923 Query       select count(id) as anzahl from game
where spieler1=6436 and spieler2=0 and hide1='0'

## This ("could be better programmed") request fails about once a day 
and the table has been repaired by an automatically repair-skript:
                   18923 Query       select count(distinct g.id) as
anzahl from game g where (g.spieler1=6436 and g.spieler2<>0 and
hide1='0') or (g.spieler2=6436 and g.spieler1<>0 and hide2='0')
                   18923 Init DB     xxx
                   18923 Query       SHOW TABLES
                   18923 Query       REPAIR TABLE `game`
040707 10:01:54   18924 Connect     xxx@localhost on
                   18924 Init DB     xxx
                   18924 Query       select id, email, points, lose,
wins, blocked from user where session='12345' and
confirmed='1' and blocked='0'
                   18924 Query       update user set
session_date=1089187314, session='12345' where
id=3952                   18924 Query       select count(distinct
points) as anzahl from user where points>477 and blocked='0'
                   18924 Query       select count(id) as anzahl from game
where gmail='xx@stripped' and spieler2=0 and hide2='0'
040707 10:01:56   18924 Query       select count(id) as anzahl from game
where spieler1=3952 and spieler2=0 and hide1='0'
                   18924 Query       select count(distinct g.id) as
anzahl from game g where (g.spieler1=3952 and g.spieler2<>0 and
hide1='0') or (g.spieler2=3952 and g.spieler1<>0 and
hide2='0')                   18924 Query       select count(distinct
g.id) as anzahl from game g where g.spieler1=3952 and g.spieler2<>0
                   18924 Quit
040707 10:01:57   18923 Quit

/var/log/mysql/mysqld.log:
040707 10:01:56  Note: Found 25256 of 25257 rows when repairing
'./xxx/game'

Errors:
MySQL-Check-Statement: e.g. "Wrong bytesec 0-0-0 at linkstart" 43473336
MySQL-Repair-Statement: "Number of rows changed from "...

Error-Log:
040528 22:30:56  mysqld started
040528 22:30:56  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard-log'  socket: '/var/lib/mysql/mysql.sock' 
port: 3306
...
040630  9:33:41  read_next: Got error 127 when reading table ./xxxx/game
040630  9:38:15  Note: Found 21188 of 21189 rows when repairing 
'./xxxx/game'
040701  9:36:40  read_next: Got error 127 when reading table ./xxxx/game
040701  9:37:07  Note: Found 21867 of 21868 rows when repairing 
'./xxxx/game'

... Binary Logging enabled, warnings enabled:

040701 12:04:37  mysqld started
040701 12:04:37  Warning: Asked for 196608 thread stack, but got 126976
040701 12:04:37  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard-log'  socket: '/var/lib/mysql/mysql.sock' 
port: 3306

System:
Software:
Installiertes OS:		Fedora Core release 1 (Yarrow)
Installierter Kernel:		2.4.22-1.2188.nptlsmp
Filesystem:			Software-Raid with ext3fs
MySQL-Version:			4.0.18

Hardware:
CPU:				1 x Intel(R) Xeon(TM) CPU 2.80GHz Hyperthreading
RAM:				1 GB
HDD:				2 x MAXTOR ATLAS10K4_73SCA

Is there anything we can also try to catch this problem?

Ragards,
Jens Bierkandt


Thread
Table needs to be repaired once a weekJens Bierkandt23 Aug
  • Re: Table needs to be repaired once a weekEgor Egorov23 Aug