MySQL InnoDB: Duplicate Key Entries nach Archivierung vermeiden

MySQL-InnoDB: Duplicate Key Entries nach Archivierung vermeiden

Nach einem Server-Neustart vergisst MySQL in Verbindung mit InnoDB die letzte auto_increment ID. Dies kann insbesondere zu Problemen führen, wenn Tabellen archiviert und dann gelöscht werden: Die IDs beginnen wieder bei 1 und bei der nächsten Archivierung kollidieren diese IDs mit bereits archivierten IDs.

Im Folgenden wird ein Workaround beschrieben, getestet mit MySQL 5.1.54 (ubuntu), sowie 5.0.51 (SuSE):

-- Tabelle anlegen
drop table if exists tbl;
create table tbl (
  id int(10) auto_increment,
  primary key(id)
) type=innodb;
-- Archiv-Tabelle anlegen
drop table if exists tbl_archiv;
create table tbl_archiv (
  id int(10) auto_increment,
  primary key(id)
) type=innodb;

-- Datensätze einfügen
insert into tbl values (default), (default);
select * from tbl;

+----+
| id |
+----+
|  1 |
|  2 |
+----+

-- archivieren
insert into tbl_archiv select * from tbl;
delete from tbl; -- => tbl ist jetzt leer!

Server neustarten mit sudo service mysql restart => Auto_increment von tbl beginnt jetzt bei 1 statt bei 3!

insert into tbl values (default);
select * from tbl;

+----+
| id |
+----+
|  1 |
+----+

Dies ist dokumentiertes Verhalten und wird nicht als Bug angesehen.

-- erneut archivieren
insert into tbl_archiv select * from tbl;

Folge: Es erscheint ein Duplicate Key Error, da die IDs aus tbl bereits in tbl_archiv existieren:

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Workaround: Mit Trigger sicherstellen, dass tbl auch nach Serverneustart mindestens beim höchsten auto_increment von tbl_archiv beginnt:

delimiter //
drop trigger if exists trigger_autoinc_tbl;
CREATE TRIGGER trigger_autoinc_tbl BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
  declare auto_incr1 BIGINT;
  declare auto_incr2 BIGINT;
  IF (NEW.id=0) THEN
    SELECT AUTO_INCREMENT INTO auto_incr1 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl';
    SELECT AUTO_INCREMENT INTO auto_incr2 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl_archiv';
    IF (auto_incr2 > auto_incr1) THEN
      SET NEW.id = last_insert_id(auto_incr2);
    END IF;
  END IF;
END;//
delimiter ;

NEW.id ist gleich 0, wenn default oder null im INSERT-Statement übergeben wurden, da das Attribut mit NOT NULL definiert wurde.

Die Funktion last_insert_id(auto_incr2) liefert auto_incr2 und stellt zugleich sicher, dass beim nächsten Aufruf von last_insert_id() ebenfalls auto_incr2 geliefert wird. UPDATE: Das klappt nicht, da mysql LAST_INSERT_ID() wieder auf den alten Wert vor Ausführung des Triggers zurücksetzt. LAST_INSERT_ID() liefert also falsche Werte!

Nochmal versuchen, diesmal setzen wir sogar das auto_increment von tbl_archiv zusätzlich hoch (dies ist tbl zunächst nicht bekannt!):

insert into tbl_archiv values (default),(default),(default),(default);
select * from tbl_archiv;

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+

delete from tbl;
insert into tbl values (default);
select * from tbl;

+----+
| id |
+----+
|  7 |
+----+

Ergebnis: Das auto_increment macht jetzt bei der nächsten ID von tbl_archiv weiter.

-- archivieren
insert into tbl_archiv select * from tbl;
delete from tbl; -- => tbl ist jetzt leer!

Ergebnis: Die Archivierung schlägt jetzt nicht mehr fehl.