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 [EMAIL PROTECTED] 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='[EMAIL PROTECTED]'
18922 Query select id, nickname, blocked from
user where email='[EMAIL PROTECTED]' 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','[EMAIL PROTECTED]','','11916,19000,24999','')
18922 Quit
040707 10:01:53 18923 Connect [EMAIL PROTECTED] 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='[EMAIL PROTECTED]' 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 [EMAIL PROTECTED] 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='[EMAIL PROTECTED]' 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
