Tückische Autoincrement-Werte in MySQL

Autoinkrementwerte gehören für Primärschlüsselfelder in Tabellen wie der Deckel zum Topf. Es geht zwar auch ohne – aber i.d.R. ist die Nutzung sinnvoll. Eindeutige Identifier sind das A und O jeglicher Referenzen zwischen Tabellen. Deshalb ist es auch wenig verwunderlich, wenn man eben auf diese Eindeutigkeit großen Wert legt.

Leider scheint MySQL die Wichtigkeit der Autoinkrementwerte nicht allzu ernst zu nehmen, denn jene Werte werden nicht etwa zusammen mit der Tabellendefinition auf der Festplatte abgelegt, sondern im Arbeitsspeicher. Damit lässt sich leicht verständlich nachvollziehen, was geschehen mag, wenn der Arbeitsspeicher entweder durch einen plötzlichen Systemausfall, wie beispielsweise bei einer Kernelpanic, oder aber auch bei einem geplanten Neustart aufgrund einer Software-Aktualisierung, plötzlich geleert wird:

Der letzte Autoinkrement-Wert kann verloren gehen!

Genauer gesagt wird bei einer gefüllten Tabelle (mind. ein Datensatz) ein

SELECT MAX(auto_increment_col) FROM tableXY FOR UPDATE;

ausgeführt und bei einer leeren Tabelle, der Autoinkrement-Wert auf 1 gesetzt (siehe [1]). Unabhängig davon, ob bereits einmal Datensätze und somit eindeutige Schlüssel in der Tabelle existiert haben.
„Was soll’s?“ wird sich mancheiner denken. Nun, wie sieht es aber aus, wenn vorher Daten aus der Tabelle gelöscht wurden? Plötzlich entstehen nach dem Neustart die gleichen IDs noch einmal, die bereits vor dem Neustart generiert wurden. Das kann zu Problemen führen, wie wir im weiteren Textverlauf noch genauer analysieren werden.

Ein Beispiel zum Nachvollziehen:

mysql> CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO A VALUES();
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO A VALUES();
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE TABLE A\G
*************************** 1. row ******************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Es wurde eine Tabelle mit einem Autoinkrementfeld namens „id“ erstellt und zwei Datensätze wurden eingefügt. Die Abfrage des nächsten Autoinkrement-Wertes über SHOW CREATE TABLE liefert demnach den Wert drei. Nun löschen wir den letzten Datensatz:

mysql> DELETE FROM A WHERE id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> show create table A\G
*************************** 1. row ******************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Der Autoinkrement-Wert bleibt nach dieser Aktion stabil und verharrt auf dem Wert drei. Führt man jedoch einen Neustart des Servers durch, indem man beispielsweise das Kommando

sudo service mysql restart

unter Ubuntu nutzt, sieht das Resultant anders aus:

mysql> show create table A\G
*************************** 1. row ******************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Urplötzlich ist von zwei die Rede. Gleiches gilt übrigens, wenn der Truncate-Befehl genutzt wird:

mysql> truncate A;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table A\G
*************************** 1. row ******
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Für MyISAM-Tabellen hingegen scheint das Problem nicht zu existieren.

Die Gefahren

Wann kann das besagte Verhalten zum Problem werden? I.d.R. kommt dies beim Archivieren von Daten vor. Man kopiert die Datensätze inkl. der Schlüssel in eine andere Tabelle. Wird eine ID nach einem Neustart erneut vergeben, schlägt die nächste Archivierung z.B. mit einem „Duplicate Key Entry“ fehl.

Die Lösung

Man kann hier sicherlich nicht von der einen Lösung sprechen. Es gibt hierzu verschiedene Ansätze und jeder Betroffene mag es auf seine eigene Weise lösen. Eine Variante wäre sicher, statt die Daten in eine andere Tabelle zu verschieben, sie einfach über ein zusätzliches Feld als „deleted“ zu markieren. Damit ist die Archivierung aber im Prinzip dahin und der Vorteil kleiner, schneller Tabellen nicht mehr gegeben.
Eine andere Variante ist, den Primärschlüssel beim Archivieren einfach nicht mit zu kopieren, sondern in der Archivtabelle neue zu vergeben. Dies ist jedoch keine besonders elegante Lösung, insbesondere dann, wenn irgendwo in der Datenbank doch noch „weiche“ Verweise (also ohne DB-Foreign-Key) auf die Schlüssel existieren.

Vielversprechender scheint es zu sein, den Autoinkrement-Wert automatisch durch eine Stored-Procedure neu setzen zu lassen:

DELIMITER $$
DROP PROCEDURE IF EXISTS resetAutoIncrement;
CREATE PROCEDURE resetAutoIncrement()
BEGIN
  SELECT @autoIncrementValue:=MAX(id) FROM B;
  SET @query = CONCAT("ALTER TABLE A AUTO_INCREMENT = ", 
                      @autoIncrementValue);
  PREPARE stmt from @query;
  EXECUTE stmt;
  DEALLOCATE prepare stmt; 
END $$
DELIMITER ;

Die Prozedur könnte beim Systemstart beispielsweise über den „init-file“-Parameter in einer SQL-Datei ausgeführt werden (siehe [3]). Das Query auf Tabelle B (beispielsweise die Archivtabelle) kann bei Bedarf natürlich beliebig getauscht oder angepasst werden.

Quellen

[1] http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

[2] http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html

[3] http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_init-file

[4] http://stackoverflow.com/questions/2361982/create-table-new-table-name-like-old-table-name-with-old-table-names-auto-incre/2362599#2362599

2 Gedanken zu „Tückische Autoincrement-Werte in MySQL

  1. Ich möchte zu bedenken geben, das man bei einer Archivierung üblicherweise nicht alle Datensätze einer Tabelle archiviert, sondern meist alles älter als z. B. 3 Jahre. Je nach Anforderung.

    Sprich grundsätzlich bleibt eigentlich immer mindestens 1 Datensatz stehen. Vorausgesetzt es ist eine lebhafte Anwendung.

    1. Das muss nicht unbedingt nur Archivtabellen treffen. Oft gibt es auch Queues oder „Arbeitstabellen“ in welchen Schlüssel erzeugt werden, die später weiterverwendet werden und unique sein müssen. Die Einträge in diesen Tabellen existieren dann nur für kurze Zeit.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *