PDA

Archiv verlassen und diese Seite im Standarddesign anzeigen : MYSQL: AUTO_INCREMENT hängt auf maximimalen Wert fest



tschloss
16.07.06, 21:20
Hi,
habe ein komisches Problem mit einer SQL Tabelle (MySQL 5, MyISAM).



create table t (id smallint unsigned not null primary key, n varchar(10))


Bei "auto" kann ich ja gemischt mit und ohne id-Wert einfügen.
Ich füge also gemisch ein paar Sätze ein und stelle fest, dass er den nächsten auto-Wert über den zuletzt eingefügten oder auf bisheriges Maximun + 1 setzt.
Jetzt füge ich einen Satz mit der Id=65530 ein. Dann 5 weitere mit Auto => letzte und maximale ID ist nun auf 65535. Einen weiteren Satz einfügen mit Auto-Id kann ich nicht "duplicate key".

Ist ja schon doof.
Dann lösche ich die 5 letzten Sätze wieder und siehe da: ich kann immer noch keinen Satz mit auto-id anlegen.

Dann ändere ich die tabelle

alter table t auto_increment=1000;
Sollte eigentlich den STartwert für auto setzen.

keine Änderung! Was der Doku widerspricht, wie ich sie verstanden habe.

Ok, dann ändere ich die Def von id auf MediumInt, um den Wertebereich zu erhöhen. Egal, seles Problem.

Irgendwie scheint sich der Auto-Counter endgültig festgefressen zu haben.

Werde die Tabelle jetzt dumpen und neu laden. Neue Table, neues Glück.

/edit: Ergänzung:
Habe die Tabelle (nur die) gedumpt, gelöscht und neu geladen. Obwohl die max. Id auf 65530 steht: Fehler!
Habe dann die ganze Datenbank gedroppt, eine neue mit anderem Namen(!) angelegt und den Dump eingespielt. Immer noch!!!! Ist mein DB-Server jetzt für immer verseucht, nur weil der Auto_Increment einmal an der Decke angestoßen ist???? :ugly:

/edit: Frankenstein!
Jetzt habe ich eine Tabelle mit 3 Datensätze erschaffen, die offenbar immer Sätze mit der id=0 anlegen möchte, also statt am oberen Ende lieber am unteren Ende des Wertebereichs ihr Glück sucht. Löche ich den o-er Satz, geht es einmal. Das Ding ist irgendwie Mega-doof!
Muss ich mir jetzt eine eigene Strategie implementieren, ein freies Plätzchen zu finden?

Ht jmd. bessere Vorschläge?
Thx
Thomas

MiGo
17.07.06, 09:32
So, ich hab' damit gerade selber mal rumgebastelt.
Tablelle mit id Typ Tinyint (ich wolllte nicht so grosse Zahlen haben :)) und auto_increment angelegt, manuell ein Feld mit id=127 reingemacht, und tadah: der nächste Insert schlägt fehl, da id=127 schon vorhanden ist.
Also per "alter table t auto_increment=1;" das ganze zurückgesetzt und nochmal probiert:
Ne, geht immer noch nicht. Gleicher Fehler. Dafür ist auto_increment jetzt wieder 127.

Die (einzige vorhandene) Zeile von id=127 auf id=10 geändert, "alter table t auto_increment=1;" angewendet und eine neue Zeile eingefügt (mit id=NULL):
Nun geht's. Eingefügt wird mit id=11.

Fazit:
Egal wie du mit "alter table" das auto_increment wählst, es wird dennoch immer nach dem grössten Wert in der Tabelle gesucht und ab dort weiter gemacht (was ja auch Sinn ergibt, da vermutlich mysql nicht nach "löchern" suchen kann. Das wäre ja auch ziemlich lahm schwer bei konkurrierenden Zugrifen auf die Tabelle.)

Also: Tabelle so ändern, dass die ID-Felder auch noch Luft nach oben haben, dann erst "alter table t auto_increment=1;" anwenden.

tschloss
17.07.06, 21:26
Danke.
Nur eigenartig, dass selbst das nichts änderte
a) Erweiterung des Wertebereichs von Smallint auf Mediumint
b) dumpen und laden der Daten in eine völlig frische Datenbank (Tabellenname blieb).

Und irgendwann hatte der Server dann die Strategie auf "Min" gewechselt und immer wieder bei "0" herumgestochert.

Also ganz transparent ist die Strategie inkl. des Speicherns der letzten/höchsten Id nicht!

Aber vielleicht kommen wir ja noch soweit hinter die Geheimnisse, dass man das Verhalten austricksen kann.

In dem Zusammenhang muss ich auch mal nach den Modi (zB SQL_MODE) fahnden.

Grüße nach Heidelberg
Thomas

MiGo
18.07.06, 09:43
Also ganz transparent ist die Strategie inkl. des Speicherns der letzten/höchsten Id nicht!
Finde ich schon :)
Mysql nimmt immer den grössten ID-Wert+1 als nächste ID. Der "AUTO_INCREMENT" Eintrag ist imho nur dazu da, bei einer neuen Tabelle die ID nicht bei 0 anfangen zu lassen sondern bei einem benutzerdefinierten Wert; das wird also nur beim Eintragen der allerersten Zeile in eine Tablelle verwendet.
Sinn ergibt sowas, wenn z.b. die ID gleichzeitig eine Artikelnummer sein soll, deren Vorraussetzung es ist, sechsstellig zu sein :)

Gruss nach MA,
MiGo :)

P.S.: Ich fordere, dich auf dem nächsten Linuxforen-Weihnachtsmarkttreffen in HD zu sehen ^^

tschloss
18.07.06, 11:36
Finde ich schon :)
Mysql nimmt immer den grössten ID-Wert+1 als nächste ID. Der "AUTO_INCREMENT" Eintrag ist imho nur dazu da, bei einer neuen Tabelle die ID nicht bei 0 anfangen zu lassen sondern bei einem benutzerdefinierten Wert; das wird also nur beim Eintragen der allerersten Zeile in eine Tablelle verwendet.
Sinn ergibt sowas, wenn z.b. die ID gleichzeitig eine Artikelnummer sein soll, deren Vorraussetzung es ist, sechsstellig zu sein :)

Gruss nach MA,
MiGo :)

P.S.: Ich fordere, dich auf dem nächsten Linuxforen-Weihnachtsmarkttreffen in HD zu sehen ^^

Es fällt mir schwer, bei 40 GRad an Weihnachtsmarkt zu denken, aber das ist in jedem Fall vorgemerkt!

Ohne da jetzt einen Dauerthread daraus machen zu wollen: nach deiner Theorie (die im Kern sicher richtig ist, aber möglicherweise ist die Umsetzung buggy), müßte doch die Erweiterung des Wertebereichs wieder Platz schaffen. Bei mir jedoch bleibt er bei "Fehler" (eigentlich bleibt er bei "Fehler", egal was ich mache, inkl. Laden einer neuen (!) Datenbank [Werte für ID auf 1-3 geändert, also vieeeeeel Platz], setzen des Startwerts etc.).
Es ist zwar nicht ganz befriedigend, aber ich habe mir eine kleine, primitive Routine geschrieben, die eine freie ID sucht. Wenn ich mal Zeit haber, grabe ich noch ein wenig (hab ich aber eh nicht ;) bzw. andere Basteleien mit mehr Spaßfaktor ).

VG
Thomas

MiGo
19.07.06, 09:40
Ohne da jetzt einen Dauerthread daraus machen zu wollen:
Och warum nicht? Vielleicht bemerkt ihn ja sogar einer, der von mysql Ahnung hat :)

müßte doch die Erweiterung des Wertebereichs wieder Platz schaffen
Wie hast du den Wertebereich erweitert? Den Datentyp von ID vergrössert?
Bei mir geht das nämlich.
ID ist tinyint, einfügen von ID 127 geht, einfügen von (autoincrementierter) ID 128 geht (natürlich) nicht.
Datentyp von ID in int geändert, Einfügen wiederholt: Geht.

[edit] mit "Löchern" muss man halt bei den IDs leben; Aber wenn sagen wir mal 50 Clients schreibend auf eine Datenbank zugreifen, ist es einfach nicht praktikabel, nach Leerstellen zu suchen und die erstmal zu füllen.
"Einfügen? Ok, ich suche eine freie ID. Ah, die 15 ist frei, also flugs einfügen. Oh. Ist inzwischen weggeschnappt worden. Dann suche ich mal weiter."
Das dürfte schon mächtig auf die Performance gehen.

Ausserdem hat man so den Vorteil, dass man an der ID erkennen kann, in welcher zeitlichen Reihenfolge die Datensätze in der DB gelandet sind (das hab' ich auch schon ein oder zweimal gebraucht).

[edit2]: Ausserdem kostet eine Erweiterung des Datentyps ja nicht besonders viel Platz. Ganz zu schweigen davon, dass man eh' einen Fehler in der Planung der DB gemacht hat, wenn es zu sowas kommt :)

tschloss
19.07.06, 11:48
Wertebereich: genau. Von Smallint auf Mediumint. Keine Verbesserung.
Ich habe meine Datenbank wohl nachhaltig "kaputt" gemacht und der Defekt ist auch persistent über einen mysqldump - drop database - create database - source dump :confused:
Ich wollte das alles nochmal auf einem zweiten Rechner nachvollziehen, from scratch. Aber durch eine Mailattacke gestern habe ich soviel Zeit verschludert (es ist gar nicht so leicht, 20.000 Mails von diversen Remote-Accounts wieder loszuwerden....)

Deswegen sage ich ja, dass das Verhalten nicht ganz nachvollziehbar ist.
Diese DB ist sowieso nur für mich persönlich (es würde mich wundern, wenn ich Multiuser-Zugriffe auf meiner persönlichen Adress-DB hätte :D ), also kein Performance Problem mit meinen Workarounds.

Greetz
Thomas