MYSQL startet alle paar Sekunden neu

MySQL, PostgreSQL, SQLite
belzebob
Posts: 14
Joined: 2008-07-25 00:22

MYSQL startet alle paar Sekunden neu

Post by belzebob » 2008-10-14 19:26

Hallo,

habe das Problem, dass meine MySQL-Datenbank alle paar Sekunden stirbt. top zeigt mir einen load von 1.0% aber bei jedem Refresh von top hat MYSQL einen neue PID (bin in kürzester Zeit bei 3200+). Greife ich auf einen Website auf dem Server zu, welche die Datenbank verwendet, muss ich mehrmals Refresh drücken bis wieder einen DB-Verbindung besteht und die Inhalte angezeigt werden. Beim nächsten Klick auf einen Link gibt es dann erst mal nichts mehr zu sehen.

Hat jemand so etwas schon erlebt? Was koennte das sein?

belzebob
Posts: 14
Joined: 2008-07-25 00:22

Re: MYSQL startet alle paar Sekunden neu

Post by belzebob » 2008-10-14 19:55

Ich kann schon sagen, dass diese hier: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html hilft, aber was muss ich tun damit wieder alles sauber läuft?

oxygen
Posts: 2138
Joined: 2002-12-15 00:10
Location: Bergheim

Re: MYSQL startet alle paar Sekunden neu

Post by oxygen » 2008-10-14 20:01

Vermutlich schmeißt der mysqld einen segfault oÄ. Er startet sich ja nicht aus jux neu. Notfalls musst du mal mit strace oder gdb debuggen, das ist häufig schon ausreichend aufschlussreich, ansonsten bugreport.
Typischerweise ist für sowas ein Problem mit libarys der Auslöser.

belzebob
Posts: 14
Joined: 2008-07-25 00:22

Re: MYSQL startet alle paar Sekunden neu

Post by belzebob » 2008-10-14 20:20

MYSQL Selbst gibt mir mehrere Möglichkeiten:

Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: Serious error! InnoDB is trying to free page 97536
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: though it is already marked as free in the tablespace!
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: The tablespace free space info is corrupt.
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: You may need to dump your InnoDB tables and recreate the whole
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: database!
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: Please refer to
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: http://dev.mysql.com/doc/refman/5.0/en/ ... overy.html
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: about forcing recovery.
Oct 14 20:11:27 domainname mysqld[16569]: 081014 20:11:27InnoDB: Assertion failure in thread 1140881760 in file fsp0fsp.c line 2980
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: We intentionally generate a memory trap.
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: If you get repeated assertion failures or crashes, even
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: immediately after the mysqld startup, there may be
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: corruption in the InnoDB tablespace. Please refer to
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: http://dev.mysql.com/doc/refman/5.0/en/ ... overy.html
Oct 14 20:11:27 domainname mysqld[16569]: InnoDB: about forcing recovery.
Oct 14 20:11:27 domainname mysqld[16569]: mysqld got signal 11;
Oct 14 20:11:27 domainname mysqld[16569]: This could be because you hit a bug. It is also possible that this binary
Oct 14 20:11:27 domainname mysqld[16569]: or one of the libraries it was linked against is corrupt, improperly built,
Oct 14 20:11:27 domainname mysqld[16569]: or misconfigured. This error can also be caused by malfunctioning hardware.
Oct 14 20:11:27 domainname mysqld[16569]: We will try our best to scrape up some info that will hopefully help diagnose
Oct 14 20:11:27 domainname mysqld[16569]: the problem, but since we have already crashed, something is definitely wrong
Oct 14 20:11:27 domainname mysqld[16569]: and this may fail.
Oct 14 20:11:27 domainname mysqld[16569]:
Oct 14 20:11:27 domainname mysqld[16569]: key_buffer_size=16777216
Oct 14 20:11:27 domainname mysqld[16569]: read_buffer_size=131072
Oct 14 20:11:27 domainname mysqld[16569]: max_used_connections=0
Oct 14 20:11:27 domainname mysqld[16569]: max_connections=100
Oct 14 20:11:27 domainname mysqld[16569]: threads_connected=0
Oct 14 20:11:27 domainname mysqld[16569]: It is possible that mysqld could use up to
Oct 14 20:11:27 domainname mysqld[16569]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 233983 K
Oct 14 20:11:27 domainname mysqld[16569]: bytes of memory
Oct 14 20:11:27 domainname mysqld[16569]: Hope that's ok; if not, decrease some variables in the equation.
Oct 14 20:11:27 domainname mysqld[16569]:
Oct 14 20:11:27 domainname mysqld[16569]: thd=(nil)
Oct 14 20:11:27 domainname mysqld[16569]: Attempting backtrace. You can use the following information to find out
Oct 14 20:11:27 domainname mysqld[16569]: where mysqld died. If you see no messages after this, something went
Oct 14 20:11:27 domainname mysqld[16569]: terribly wrong...
Oct 14 20:11:27 domainname mysqld[16569]: frame pointer is NULL, did you compile with
Oct 14 20:11:27 domainname mysqld[16569]: -fomit-frame-pointer? Aborting backtrace!
Oct 14 20:11:27 domainname mysqld[16569]: The manual page at http://www.mysql.com/doc/en/Crashing.html contains
Oct 14 20:11:27 domainname mysqld[16569]: information that should help you find out what is causing the crash.
Oct 14 20:11:27 domainname mysqld_safe[16588]: Number of processes running now: 0
Oct 14 20:11:27 domainname mysqld_safe[16590]: restarted
Oct 14 20:11:27 domainname mysqld[16597]: 081014 20:11:27 InnoDB: Error: page 213022 log sequence number 7 1678722145
Oct 14 20:11:27 domainname mysqld[16597]: InnoDB: is in the future! Current system log sequence number 7 1678707468.
Oct 14 20:11:27 domainname mysqld[16597]: InnoDB: Your database may be corrupt or you may have copied the InnoDB
Oct 14 20:11:27 domainname mysqld[16597]: InnoDB: tablespace but not the InnoDB log files. See
Oct 14 20:11:27 domainname mysqld[16597]: InnoDB: http://dev.mysql.com/doc/refman/5.0/en/ ... overy.html
Oct 14 20:11:27 domainname mysqld[16597]: InnoDB: for more information.
Oct 14 20:11:27 domainname mysqld[16597]: 081014 20:11:27 InnoDB: Started; log sequence number 7 1678707468
Oct 14 20:11:27 domainname mysqld[16597]: InnoDB: !!! innodb_force_recovery is set to 4 !!!
Oct 14 20:11:27 domainname mysqld[16597]: 081014 20:11:27 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
Oct 14 20:11:27 domainname mysqld[16597]: 081014 20:11:27 [Note] Starting crash recovery...
Oct 14 20:11:27 domainname mysqld[16597]: 081014 20:11:27 [Note] Crash recovery finished.
Oct 14 20:11:27 domainname mysqld[16597]: 081014 20:11:27 [Note] /usr/sbin/mysqld: ready for connections.


Mit innodb_force_recovery = 4, bleibt sie zumindest am leben, aber leider geht halt nicht alles.

Roger Wilco
Administrator
Administrator
Posts: 5924
Joined: 2004-05-23 12:53

Re: MYSQL startet alle paar Sekunden neu

Post by Roger Wilco » 2008-10-14 21:17

Eine deiner InnoDB Tabellen ist, wie du den Logs entnehmen kannst, defekt und müsste gedumpt und neu angelegt werden. Hast du die Tipps aus dem Log bereits befolgt?

belzebob
Posts: 14
Joined: 2008-07-25 00:22

Re: MYSQL startet alle paar Sekunden neu

Post by belzebob » 2008-10-14 22:27

JaEin ;-) ich habe noch nicht herausgefunden wie ich InnoDB-Tabellen reparieren kann. MyISAM geht mit myisamchk, aber InnoDB? mysqlcheck bekommt es auch nicht hin.

belzebob
Posts: 14
Joined: 2008-07-25 00:22

Re: MYSQL startet alle paar Sekunden neu

Post by belzebob » 2008-10-23 12:01

So mal zum Abschluss: Jeder der mal ne kaputte InnoDB hat, genießt mein Beilied! Dump erstellen und zurückspielen schlug bei mir fehl, musste also auf das letzte funktionstüchtige Backup zurück, naja. Jetzt läuft es wieder.