Fehler beim Autoincrement????

[seekworld]

Hi,

(MySQL Version 5.0.45)

Ich hab einen ganz komischen Fehler:

Ich habe eine Tabelle mit

  id_elemnt INT(10) UNSIGNED NOT NULL auto_increment,
  id_terminal SMALINT(5) UNSIGNED NOT NULL,
  beschreibung text NOT NULL default '',
  PRIMARY KEY idpk(id_terminal, id_element) 

Das mit dem auto_increment funktionierte einwandfrei .... bis gestern.
Gestern habe ich nämlich Daten über PHPMyAdmin eingespielt, und zwar mit fixen id_element, weil ich mir da Daten offline vorbereitet habe.

Heute hat meine Webseite ganz ungewöhnliche Fehlermeldungen ausgespuckt, und Teile haben nicht mehr funktioniert, weswegen ich mal in der Datenbank nachgeschaut habe.

Das auto_increment machte dort weiter, wo es aufgehört hat, also bei id_element = 86. Die Daten die ich gestern manuell reingeklopft habe existieren für das auto_increment nicht (sollte eigentlich bei id_element = 166 weitermachen)

Der UNIQUE INDEX regt sich dabei allerdings nicht auf, weil die doppelten Einträge eine unterschiedliche id_terminal haben.

==> Darf das sein??
==> Wie kann ich das für die Zukunft verhindern? ...
==> Muß ich meine Index-Struktur neu überdenken? (wäre extrem sch*** weil die Webseite schon online ist, und sie hat sich als sehr gut herausgestellt)


Danke & lg,
seekworld

[AmicaNoctis]

Hallo,

das ist normal, wenn du den internen Zähler umgehst. Der wird aber automatisch neu ermittelt, wenn du den MySQL-Server neu startest. Du kannst ihn aber auch explizit ändern:

ALTER TABLE tabellenname AUTO_INCREMENT = 166;

Gruß,

Anja

[h3ll]

Ich seh in deiner Tabellenstruktur keinen UNIQUE INDEX. Hast du was weggelassen?

[seekworld]

PRIMARY KEY ist UNIQUE wenn doppelte Einträge drinnen sind regt er sich auch auf .... Sorry für meine unklare Ausdrucksweise


Ich hab jetzt einbißchen herumprobiert.
Wenn ich beim id_terminal = 1 einen Wert hinzufüge zählt er bei 86 (mittlerweile 94) weiter
Wenn ich bei id_terminal = 2 einen Wert hinzufüge zählt er bei 166 weiter, also dort wo er eigentlich sollte.
Wieder bei id_terminal = 1 .... 95


Das ist entweder ein dicker fetter Bug, oder ein total bescheuertes absichtliches Verhalten.

ALTER TABLE tabellenname AUTO_INCREMENT = 166; 

ist also auch keine Lösung auf die ich mich verlassen kann


Ich bin gerade dabei die Index-Struktur zu ändern *grmpf*
Dabei hab ich extra drauf geachtet, daß die Idizes so klein wie möglich sind, und zwecks der Performance die PRIMARY wo geht genommen, weil die die schnellsten sind


Danke für eure Hilfe

[h3ll]

Das ist kein Bug, sondern ein gewünschtes Verhalten.

Du sagst:
PRIMARY KEY idpk(id_terminal, id_element)

Der Primary Key besteht also aus id_terminal und id_element. Es sind also folgende Kombinationen problemlos gleichzeitig möglich:

1,1
1,2
1,3
2,1
2,2
2,3
usw.

Das heißt es müssen nur beide Spalten zusammen eindeutig sein. Es darf zB. nicht zweimal 2,2 vorkommen, aber es kann sehr wohl 1,2 und 2,2 in der Tabelle vorhanden sein. Wenn du willst, dass die id_element für sich eindeutig ist, musst du id_terminal aus dem Primary Key rausnehmen.

[AmicaNoctis]

Es gibt imho ohnehin keine sinnvolle Begründung, mit Mehrfeldprimärschlüsseln rumzumurksen. Jede solche Tabelle lässt sich so umformen, dass es nur noch eine Primärschlüsselspalte gibt.

[wahsaga]

Das ist entweder ein dicker fetter Bug, oder ein total bescheuertes absichtliches Verhalten.

Letzteres. (Wobei, "total bescheuert" würde ich es eher nennen, sich über klar definiertes Verhalten aufzuregen ...)


MySQL :: MySQL 5.1 Reference Manual :: 3.6.9 Using AUTO_INCREMENT

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups. [...]

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group.

[seekworld]

Es gibt imho ohnehin keine sinnvolle Begründung, mit Mehrfeldprimärschlüsseln rumzumurksen.

Ich habe das jetzt so wie du geschrieben hats anders gemacht

Alt:

PRIMARY KEY mainkey (id_terminal, id_element) 

Neu:

PRIMARY KEY id_element (id_element)
KEY mainkey (id_terminal, id_element) 

Der Vorteil der alten Variante ist:
1) id_element wird nur in einem Index verwendet => Speicherplatz und Index-Größe, die man ja so klein wie möglich halten sollte. Gut in dem Fall könnte man es eventuell weglassen, aber das "Risiko" ist mir zu groß
2) der PRIMARY KEY ist der schnellste Index, weil er soviel ich weiß im Arbeitsspeicher liegt. Das beschleunigt also auch eine Abfrage nur nach id_terminal


Fortsetzung folgt.....

[seekworld]

(Wobei, "total bescheuert" würde ich es eher nennen, sich über klar definiertes Verhalten aufzuregen ...)

Ich bezeichne es nachwievor bescheuert!
Wenn ich etwas auto_increment mache gehe ich als logisch denkender Mensch davon aus, daß immer(!) eins dazugezählt wird, also in sich schon UNIQUE ist, und nicht das Verhalten durch etwas anderes beeinflußt wird.

Nochdazu wird

ALTER TABLE tabellenname AUTO_INCREMENT = 166; 

unbrauchbar

Das Verhalten hat, wie ich das jetzt sehe 2 Vorteile:
1) Der Benutzer sieht nicht wieviele Einträge sich insgesamt im System befinden
2) Beim händischen kopieren der Daten braucht man die id_element nicht verändern. Beim Kopieren mit einem UI kann man das allerdings programmiertechnisch leicht lösen, weshalb der Vorteil nicht wirklich zählt

Die Nachteile sind meines erachtens aber gewichtiger:
1) Die id_element ist im System nicht mehr UNIQUE
2) Ein logisches Verhalten wird durch ein anderes ersetzt, wenn man das in der Doku also übersehen hat so wie ich ....
3) Die oben genannte Tabelle ist die Haupttabelle der Elemente. Nachdem das System mehrsprachig ist gibt es eine 2te Tabelle

UNIQUE KEY (id_element, language) 

die mit der Haupttabelle verknüpft ist.
.... Ich müßte also die id_terminal auch in diese Tabelle mitziehen und hätte damit redundante Daten, was der größte Fehler ist den man in einer Datenbankstruktur machen kann

lg,
seekworld

[wahsaga]

Die Nachteile sind meines erachtens aber gewichtiger:
1) Die id_element ist im System nicht mehr UNIQUE

Das verlangen zu wollen, wenn man diese Spalte eben nicht explizit UNIQUE gemacht hat, sondern stattdessen einen aus mehreren Spalten zusammengesetzen Wert, wäre ja auch extrem abwegig.

[h3ll]

1) Der Benutzer sieht nicht wieviele Einträge sich insgesamt im System befinden

Das hat überhaupt nix mit dem AUTO INCREMENT zu tun.

Ich schreibe Datensatz 1, 2 und 3 in die Datenbank. Dann lösche ich Datensatz 1 heraus. Dem AUTO INCREMENT entnehme ich, dass 3 Datensätze vorhanden sein müssen, was aber nicht stimmt!

1) Die id_element ist im System nicht mehr UNIQUE

Du hast es nicht als UNIQUE definiert! Du willst MySQL deine eigenen Fehler in die Schuhe schieben.

2) Ein logisches Verhalten wird durch ein anderes ersetzt, wenn man das in der Doku also übersehen hat so wie ich ....

Dein "logisches Verhalten" ist für mich aber unlogisch, und ich hab die Stelle im Handbuch noch nie gelesen.

3) Die oben genannte Tabelle ist die Haupttabelle der Elemente. Nachdem das System mehrsprachig ist gibt es eine 2te Tabelle

UNIQUE KEY (id_element, language) 

die mit der Haupttabelle verknüpft ist.
.... Ich müßte also die id_terminal auch in diese Tabelle mitziehen und hätte damit redundante Daten, was der größte Fehler ist den man in einer Datenbankstruktur machen kann

Quatsch mit Sauce. Verwende wirklich eindeutige IDs und keine Kombinationen aus mehreren Spalten, dann gibts das Problem erst gar nicht.

[AmicaNoctis]

Wenn ich etwas auto_increment mache gehe ich als logisch denkender Mensch davon aus, daß immer(!) eins dazugezählt wird

Als ebenfalls logisch denkender Mensch, gehe ich davon aus, dass "automatisch" nicht "immer" heißt, sondern nur, dass es vom System "selbst" entschieden wird. Wenn du aber fremde Daten mit festen Primärschlüsselwerten importierst, verhinderst du diesen Automatismus. Dann gilt: wer die Ordnung stört, muss auch selbst dafür sorgen, dass sie wieder hergestellt wird.

Die id_element ist im System nicht mehr UNIQUE

Weil du sie nicht als unique spezifiziert hast, jedenfalls nicht unabhängig von anderen Spalten. Dass sie vorher eindeutig war, war nur ein schöner Zufall. Wenn du dich drauf verlassen willst, musst du nen Unique Index erstellen, so einfach ist das.

Ich müßte also die id_terminal auch in diese Tabelle mitziehen und hätte damit redundante Daten, was der größte Fehler ist den man in einer Datenbankstruktur machen kann

Das verhindert man am besten, wenn man die DB von anfang an richtig plant und nur Einfeldschlüssel benutzt. Wer sich Gedanken drüber macht, ob der Primary Key der schnellste ist, spart eindeutig an der falschen Stelle, wenn er dann Mehrfeldschlüssel reinbringt.

Das sind nur mal so meine Gedanken dazu...


Edit: Oh, ich war aber langsam, steht ja praktisch alles schon da...

[seekworld]

@wahsaga
Natürlich hast du recht, daß ich dies Spalte nicht UNIQUE gemacht habe, also durch manuelles einpflegen auch doppelte Einträge schreiben könnte, ohne das sich MySQL aufregen würde.
Daß er automatisch doppelte Einträge in der auto_increment - Spalte produziert, ist der Nachteil den ich meine


@h3ll,
@AmicaNoctis

Vielleicht solltet ihr euch das Posting von "wahsaga", der immer wieder eine Hilfe ist, durchlesen ....
=> Das verhalten von auto_increment wird durch den mehrspaltigen PRIMARY KEY verändert ..... und das überliest man bei dem Umfang der Doku ganz leicht, was mein einziger Fehler war.

Beim kopieren der Daten habe ich weder einen Fehler gemacht noch den Automatismus gestört, da er ja nachwievor "richtig" funktioniert. Richtig im Sinne der MySQL-Doku, aber "unrichtig" nach gängigem mathematischen und logischen Verständnis, und nicht wie das jahrelang gewohnte Verhalten von MySQL


@AmicaNoctis

Das verhindert man am besten, wenn man die DB von anfang an richtig plant und nur Einfeldschlüssel benutzt. 

Die Datenbankstruktur ist perfekt!
Auch die Indexstruktur war perfekt, bis zu diesem blöden Verhalten. Jedenfalls hab ich mit der Abfragedauer und EXPLAIN die besten Werte erziehlt.

Nur einspaltige Indexe?
Dir scheint entgangen zu sein, daß MySQL nur einen Index zur Eingrenzug der Daten hernimmt, bevor er auf die eigentlichen Daten zugreift. Die mehrspaltigen Indexe haben schon ihren Sinn!

Falls du "einspaltige PRIMARY KEYS" meinst...
Wenn es etwas schnelleres gibt, als den normalen INDEX ... wieso sollte man das nicht verwenden?
Das wäre, wie wenn du einen 100Euro-Schein, der auf der Straße liegt einfach liegen läßt ....

[h3ll]

Falls du "einspaltige PRIMARY KEYS" meinst...
Wenn es etwas schnelleres gibt, als den normalen INDEX ... wieso sollte man das nicht verwenden?
Das wäre, wie wenn du einen 100Euro-Schein, der auf der Straße liegt einfach liegen läßt ....

Du versuchst immer noch am falschen Ende zu optimieren. Sorge einfach dafür, dass ein Datensatz eine eindeutige ID hat und nur dieser ID für den Primary Key verwendet wird.

Und nein, das Datenbankdesign ist nicht perfekt.

[seekworld]

Und nein, das Datenbankdesign ist nicht perfekt.

Das finde ich total vermessen anhand einer Demo-Tabelle, die mein Problem beschreibt, auf das ganze Datenbankstruktur zu schließen.

Du versuchst immer noch am falschen Ende zu optimieren.

Woraus schließt du das?
War es überhaupt ein falsches Ende, wenn es merklich Performance gebracht hat?

Sorge einfach dafür, dass ein Datensatz eine eindeutige ID hat und nur dieser ID für den Primary Key verwendet wird.

Ich weiß nicht wie ich diese Aussage interpretieren soll. Meinst du, daß ich die Indizes bei der aktuellen Datenbank ändern soll? Das habe ich längst gemacht, wie bereits zuvor geschrieben!