[MySQL] Kapotte table, probleem onvindbaar

[Verwijderd]

Vandaag gebeurde het weer. Voor de 3e keer in de laatste 2 weken is mijn user table corrupt geraakt. Ik weet niet waar het probleem zit.

Naast het corrupt raken van de table, heeft de server ook erg veel last van mysqld die gewoon ophoudt met functioneren, en alleen te killen valt via 'killall -9 mysqld'. Soms crashed de hele server ook gewoon, en is alleen via een reboot-optie van de dedicated server hosting company te herstarten.

Als de table corrupt is, valt hij altijd geheel te herstellen met REPAIR TABLE user;
Er mist dus nooit data!

Informatie over de server:
- Dual AMD MP 2600
- 2 GB geheugen
- Debian 3.0 Woody
- Linux cicciolina.barafranca.com 2.4.18-sb-smp #1 SMP Tue Jul 27 17:39:37 UTC 2004 i686 unknown
- mysql Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)


We gebruiken mysql_connect van een andere server (AMD MP 2600, 1GB ram). Ze zijn helaas niet intern verbonden.

Errors:

code:

mysql> CHECK TABLE user;
+-------------+-------+----------+--------------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                               |
+-------------+-------+----------+--------------------------------------------------------+
| omerta.user | check | warning  | Table is marked as crashed                             |
| omerta.user | check | warning  | 4 clients is using or hasn't closed the table properly |
| omerta.user | check | error    | Checksum for key: 2 doesn't match checksum for records |
| omerta.user | check | error    | Corrupt                                                |
+-------------+-------+----------+--------------------------------------------------------+

mysql> CHECK TABLE user EXTENDED;
+-------------+-------+----------+--------------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                               |
+-------------+-------+----------+--------------------------------------------------------+
| omerta.user | check | warning  | Table is marked as crashed                             |
| omerta.user | check | warning  | 4 clients is using or hasn't closed the table properly |
| omerta.user | check | error    | Record at: 116104  Can't find key for index: 2         |
| omerta.user | check | error    | Corrupt                                                |
+-------------+-------+----------+--------------------------------------------------------+

mysql.err:

code:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail

key_buffer_size=157282304
record_buffer=131072
sort_buffer=2097144
max_used_connections=140
max_connections=500
threads_connected=31
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1241592 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

Omdat het loggen van alle queries zo'n 10GB per dag in beslag neemt, heb ik dit maar uitgezet. Daar heb ik nu geen informatie over, maar heb het na deze corrupte table maar weer aangezet.

Ik ben echt ten einde raad, iemand een idee waar het aan ligt?

[Verwijderd]

Inmiddels geswitched naar Sarge en:
mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686)

Lost het probleem ook niet op...

[insan1ty]

Ik zie dat je probleem nog niet verholopen is... maar je hebt geen mail adres dus rest mij niets anders dan deze te kicken..
Ik stond vandaag voor hetzelfde probleem.. ineens table corrupt, mysql errors in php.. Phpadmin wil de table niet meer showen vanwege een fout in tabelnaam.MYD.. REPAIR TABLE tabelnaam werkt dan inderdaad bij mij ook zonder verlies..

Wat ik denk dat het veroorzaakt heeft: Ik had al mijn allocated space in gebruik (250/250MB in use dus)..
MySQL valt ook onder dit quotum. Dus als er geen ruimte meer is om te schrijven kan de tabel niet weggeschreven worden en de boel raakt corrupt. Heb helaas de codes niet gekopieerd die de admin gaf toen de tabel gerepareerd werd.. Zag iets staan van written past block no 3432535 ofzo..

Ik hoop dat je er iets aan hebt en dat de modjes niet te boos worden dat ik je gekickt heb

edit:
de output van repair table tabelnaam; wordt bij mij
Table Op Msg_type Msg_text
tabelnaam repair info Found block that points outside data file at 40136...
tabelnaam repair info Found block that points outside data file at 40136...
tabelnaam repair status OK

Thought it might help

[ Voor 16% gewijzigd door insan1ty op 06-01-2005 17:40 ]

[Snow_King]

Het mag dan een oud topic zijn, hier komt het zelfde voor:

code:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=536870912
read_buffer_size=8384512
max_used_connections=95
max_connections=800
threads_connected=20
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1045369 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x69109ee0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x746cea58, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81700f0
0xffffe420
0x81a769c
0x81a769c
0x81cb31f
0x8186060
0x818b422
0x81829ce
0x81824f9
0x8181a01
0xb7f42b63
0xb7e7b18a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x6b687700  is invalid pointer
thd->thread_id=23759
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
060603 15:57:32  mysqld restarted
060603 15:57:32 [Warning] Changed limits: max_open_files: 65535  max_connections: 800  table_cache: 32362
060603 15:57:32  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060603 16:00:26  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 4 92253063.
InnoDB: Doing recovery: scanned up to log sequence number 4 92264639
060603 16:00:26  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
060603 16:00:26  InnoDB: Started; log sequence number 4 92264639
060603 16:00:27 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.20a-standard-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=536870912
read_buffer_size=8384512
max_used_connections=71
max_connections=800
threads_connected=56
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1045369 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x908eab8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x745c3a58, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81700f0
0xffffe420
0x81a769c
0x81a769c
0x81cb31f
0x8186060
0x818b422
0x81829ce
0x81824f9
0x8181a01
0xb7f4ab63
0xb7e8318a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x73e9e5c0  is invalid pointer
thd->thread_id=142
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
060603 16:00:41  mysqld restarted
060603 16:00:41 [Warning] Changed limits: max_open_files: 65535  max_connections: 800  table_cache: 32362
060603 16:00:41  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060603 16:02:17  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 4 92253227.
InnoDB: Doing recovery: scanned up to log sequence number 4 92277602
060603 16:02:17  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
060603 16:02:18  InnoDB: Started; log sequence number 4 92277602
060603 16:02:18 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.20a-standard-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)

Een stacktrace heb ik al gemaakt, maar ook dat zegt mij weinig, maar afijn:

code:

0x81700f0 handle_segfault + 356
0xffffe420 _end + -140100080
0x81a769c _Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table_triggers_list14trg_event_type + 32
0x81a769c _Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table_triggers_list14trg_event_type + 32
0x81cb31f _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb + 1079
0x8186060 _Z21mysql_execute_commandP3THD + 8716
0x818b422 _Z11mysql_parseP3THDPcj + 306
0x81829ce _Z16dispatch_command19enum_server_commandP3THDPcj + 1178
0x81824f9 _Z10do_commandP3THD + 129
0x8181a01 handle_one_connection + 621
0xb7f4ab63 _end + -1348795053
0xb7e8318a _end + -1349612678

Ik heb dit nu op 2 machines, exact het zelfde OS en zelfde hardware (Dual Opteron 275, 8GB Ram en Debian 3.1)

Heeft iemand enig idee waar dit aan ligt?