MySQL InnoDB und das Ende der Welt

Posted on by

Wer erinnert sich noch an den Hype um das Jahr 2000-Problem – sicherlich einige. Mittlerweile schreiben wir das Jahr 2012 und alles scheint in bester Ordnung. Scheint – die Realität sieht manchmal leider etwas anders aus.

Alles beginnt mit ganz harmlosen Sympthomen: Ein Mitarbeiter meldet sich bei mir, er könne in der von mir betreuten Datenbank keine weiteren Datensätze anlegen. Die Fehlermeldung besagt, dass eine Schlüsselverletzung vorliegt.

Erster Ansatz: Nachschauen wo man mal wieder etwas zu restriktiv im Datenmodell oder irgendwo etwas zu simpel im Code gearbeitet hat. Aber leider: Fehlanzeige – nichts offensichtliches zu entdecken.

Nächster Versuch: Man füge einen Datensatz direkt auf SQL-Ebene ein – wichtig dabei, man halte sich so weit wie möglich an das Original beim Einfügen. Also lässt man ganz bewusst das Feld für den Primär-Schlüssel leer – steht ja auf auto_increment und damit meint man macht MySQL doch in der Regel keine Zicken.

Aber: Holla! Was ist denn das? MySQL weigert sich den Datensatz einzufügen, angeblich wegen einer Verletzung des Primär-Schlüssels!

Ein wenig Recherche später findet man dann denn auch die Ursache: Der Zahlenbereich des verwendeten Zahlentyps war am Ende. Nun dachte ich eigentlich, dass MySQL dann hergehen würde und sich irgendwie die nächstbeste freie Zahl nimmt – von mir aus ja auch negativ – ist ja kein Drama. Platz war definitiv noch – denn auch wenn in der Tabelle rund 150.000 Datensätze gespeichert waren, so ist das noch immer deutlich kleiner als die 2147483647 möglichen positiven IDs die ein 32 Bit signed Integer in MySQL zur Verfügung stellt (doppelt so viele nochmal wenn man die negativen Zahlen mitberücksichtigt).

Hotfix in dieser Situation war eigentlich recht einfach: von allen vorhandenen IDs eine möglichst große Zahl abziehen, unter Berücksichtigung der Einschränkung, dass keine Id auf den Spezialwert 0 fallen darf. Einfacher gesagt denn getan – die Tabelle repräsentiert nämlich eigentlich eine Baumstruktur mit einem Verweis auf den Vater der jeweiligen Zeile. Da kommt MySQL mit dem Update leider nicht klar, denn aufgrund der fehlenden Bedingung schnappt sich MySQL einfach einen Lock auf die gesamte Tabelle … und sperrt sich damit für ein on Update Cascade selbst aus 😐
Also doch die etwas härtere Tour:

  1. interne Schlüsselbeschränkung aufheben
  2. Immer gleichzeitig die ID und den Vater-Verweis um die gefundene Zahl dekrementieren (da von allen Werten ein konstanter Wert abgezogen wird entstehen wiederum gültige und konsistente Datensätze
  3. Vaterbeziehung wieder mit einem Schlüssel versehen

Warum so umständlich? – Auto-Increment-Rücksetzen funktioniert mit InnoDB als Engine nicht, siehe auch weiter unten. Fremdschlüssel generell abschalten wäre auch keine Maßnahme gewesen, denn die externen Referenzen haben ja ein „on update cascade“ – das erspart die langwierige Nacharbeit an x anderen Tabellen in der Datenbank.
Somit kann jetzt wenigstens erst mal wieder gearbeitet werden – die Arbeit für den Entwickler fängt aber jetzt erst richtig an…
Eine andere Alternative wäre die Vergrößerung des Schlüsselbereichs z.B. auf Bigint gewesen oder evtl. auch nur die Verwendung nicht-negativer Zahlen. Leider nicht ganz so einfach möglich, da die Tabelle an verschiedenen Stellen in der Datenbank referenziert wird und natürlich auch die referenzierenden Schlüsselwerte im Datentyp identisch sein müssen. Das wäre auf die Schnelle auch nicht so trivial gewesen.

Zeit für etwas Ursachenforschung – wie man so schön sagt: Lessons learned. Wie Allgemein üblich handelt es sich bei diesem Fehler nicht um eine singuläre Fehlerquelle sondern das Zusammenspiel mehrere Umstände.
Erste Fragestellung: Warum waren die ID-Werte überhaupt so ins Kraut geschossen?
Hierfür gibt es mehrere Gründe – einer ist die historische Wurzel der Datensätze: Diese stammten aus einer Access-Tabelle mit Replikaten. Hierbei setzt Access den Primärschlüssel nicht mehr auf auto_increment (monoton ansteigend wie sich das gehört) sondern auf Random – und da kommen dann recht fix große und auch negative Zahlen bei raus. Die hatte ich natürlich einfach übernommen.
Nächstest Problem: In der Tabelle fanden in letzter Zeit größere Mengen an Transaktionen statt: Die Tabelle wird teilweise aus einer anderen Tabelle gespeist, dabei werden die Datensätze automatisch generiert. Dieses Tool musste aufgrund von Fehlern mehrfach laufen und hatte teilweise dabei auch größere Mengen Einträge produziert – rund 600.000 an der Zahl bevor der Fehler auffiel und beseitigt werden konnte. Somit war man schon bedrohlich nahe ans Limit heran gekommen.
Soweit zur suboptimalen Verwendung der vorhandenen Schlüsselzahl – aber das war ja nicht alleine ausschlaggebend, immerhin könnte man ja erwarten, dass ein ausgewachsenes Datenbank-System sich selbständig um die Behebung des Notstandes: „Keine Schlüssel mehr da“ kümmert. Zumindest sollte man das annehmen. Nun kommen auch hier einige kleinere Probleme zusammen, die dann gemeinsam für den großen Ärger sorgen. Als Grundlage verwendet MySQL zwei verschiedene Datenbank-Engines – MyIsam als klassische Tabellenform, leider kann diese nicht mit Fremdschlüsseln umgehen. Daher gibt es InnoDB als neueren Standard. Diese hat zwar nicht die Performance in Sachen Volltext-Suche, kann dafür aber von Hause aus bereits mit Fremdschlüsseln umgehen. In der aktuellen Entwicklung ein unumgängliches Feature, das kaum ein Datenbankentwickler heute noch missen möchte.
Der Berechnungsalgorithmus für die nächste ID in InnoDB ist recht simpel gestrickt: Die nächste zu verwendende ID berechnet sich stets als max(id)+1 – was in MySQL bei Integer-Werten mit 32 Bit zu einem Fehler führt (erst ab Version 5.5) – der dann durch die Ebenen der Datenbank-Engine nach oben wandert und dort schließlich zum bekannten Fehler führt – leider ist der Hinweis duplicate key for primary da nicht sonderlich hilfreich um gleich die Ursache zu erkennen. Dieser Algorithmus wird auch angestoßen, wenn man den Auto-Increment-Zähler manuell versucht zurück zu setzen, oder bei einem Server-Neustart. Simple Lösungen mag ich ja eigentlich, aber so simpel hätte es in dem Fall doch nicht sein müssen.
Interessanterweise ist der Fehlerfall ja sogar in der Dokumentation beschrieben (siehe hier:) – netter Nebeneffekt: Wenn man als Datentyp BigInt (also 64 Bit-Zahlen) verwendet, dann klappt es auch mit dem Wrap-Around am Ende des Zahlenbereichs, es wird dann wieder bei 1 bzw. im negativen Bereich weiter gezählt. Warum habe ich noch nicht ergründen können, ich vermute mal, dass InnoDB intern schon mit 64 Bit arbeitet.

Kommen wir zu den Lehren für weitere Projkete oder auch nur die Neuanlage neuer Tabellen:

  1. An InnoDB führt kein Weg vorbei wenn Fremmdschlüssel im Spiel sind, und Fremdschlüsselmechanismen sind generell begrüßenswert
  2. Beim Import von Alt-Daten wenn es möglich ist, deren ID-Werte verwerfen und neue vergeben (leider nicht immer ohne größeren Aufwand möglich)
  3. Wenn sinnvoll möglich BigInt als Datentyp für die ID-Spalte verwenden, das schafft Luft und es klappt auch mit dem Überlauf richtig
  4. Für den Fall der Fälle Mittel in Bereitschaft haben, um das Problem durch „Renumerierung“ lösen zu können (nicht immer hat man die Zahlen in einem bestimmten Bereich liegen so wie in diesem glücklichen Fall).

Weitere Kommentare herzlich willkommen.