MySQL 4, Problem mit Delete from table

[Tom]

Hello,

in einer größeren Schliefe muss ich öfter "delete from temp" als Statement auslösen. Leider löscht das nicht den autoincremet_key. Ich habe nun an flush tables gedacht. Damit weden die aber alle geschlossen und vermutlich wohl auch das Lock auf die tabellen gelöst. Genaus das darf aber nicht passieren, bis die Schleife fertig abgearbeitet ist.

Hat jemend ne Idee, wie ich den Autoincrement-Key zurücksetzen kann, ohne die Tabelle zu schließen?

Ist eventuell    "flush tables with read lock" der richtige Befehl?

Eigentlich habe ich nur drei betroffene Tabellen. Dann könnte ja auch

"flush tables $table1, $table2, $table3 with read lock"

richtig sein.

Bitte kommt mir jetzt nicht mit "probiers doch aus". Ich habe keine Testmöglichkeit um heftigen konkurrierenden Betreib auf die tabellen zu jagen. Und bei normaler Ausführung des Scriptes sieht man die ggf. entstehen Locking-Gaps nicht.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Vinzenz]

Hallo Tom,

in einer größeren Schliefe muss ich öfter "delete from temp" als Statement auslösen. Leider löscht das nicht den autoincremet_key.

Vielleicht hilft Dir folgende Passage aus dem Manual http://dev.mysql.com/doc/mysql/de/CREATE_TABLE.html weiter?

<zitat>
Wenn Sie alle Zeilen in der Tabelle mit DELETE FROM tabelle (ohne ein WHERE) im AUTOCOMMIT-Modus löschen, fängt die Folge bei allen Tabellentypen von Neuem an.
</zitat>

Hat jemend ne Idee, wie ich den Autoincrement-Key zurücksetzen kann, ohne die Tabelle zu schließen?

Ist der AUTOCOMMIT-Modus bei Deinem Problem überhaupt möglich?

Freundliche Grüsse,

Vinzenz

[Tom]

Hello,

Vielleicht hilft Dir folgende Passage aus dem Manual http://dev.mysql.com/doc/mysql/de/CREATE_TABLE.html weiter?

Das hilft nicht wirklich.
Wenn ich die Schleife manuell über den phpMyAdmin durchgehe, dann funktioniert es ja auch. Das ist eben ein Indiz für ein Buffer-Problem.

phpMyAdmin führt zwar die im SQL-Fenster befindlichen Statements inziwschen mit einer Connection durch, aber da ich ja die Statements mehrmals für die verschiedenen Filterbereiche aufrufe 8also immer eine neue Connection) gibt es mit dem "delete from temp" kein Problem.

Innerhalb eines Scriptes, dass dieses nun komplett in einer Schleife durchführt, wird weder die Connnection gelöst, noch eine Tabelle geschlossen. Ich kann zwar alle Sätze löschen, aber nicht den Initialwert für den Autoincrement-Key zurücksetzen.

Das ist eigentlich ein logischer Bug!

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Tom]

Hello,

Vielleicht hilft Dir folgende Passage aus dem Manual http://dev.mysql.com/doc/mysql/de/CREATE_TABLE.html weiter?

Ich habe das nun nochmal von vorne untersucht. Auch im phpMyAdmin verhält sich die Sache geanauso. Es hat also nichts mit dem Flushen der Tables zu tun, sondern es ist ein Bug.
Bei

delete from $table

wird der Initialwert des Autoincrement-Keys gar nicht mehr zurückgesetzt (MySQL 4.0.12)

Das ist also eindeutig eine Abweichung vom Manual. Da es sich um ein garantiertes Verhalten handelt, ist es also ein Bug und viele Apllikationen, die sich darauf verlassen, funktionieren nicht mehr ordnungsgemäß.

Mit "Truncate Table" geht es, aber dadurch verliert man das Lock auf den table und damit die Kontrolle über den Zustand.

MySQL ist also nicht mehr brauchbar!

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Ilja]

yo,

Bei

delete from $table

wird der Initialwert des Autoincrement-Keys gar nicht mehr zurückgesetzt (MySQL 4.0.12)

bei mysql begebe ich mich immer ein wenig aufs glatteis. aber grundsätzlich würde ich sagen, ist das kein bug, dass autoincrementwerte durch das löschen von datensätzen mit delete nicht zurück gesetzt werden, sondern eher eine vereinbarung. zwar werden durch den befehl in dieser form alle datensätze gelöscht, aber eben nicht bei jedem delete befehl. man müsste also eine zusätzlich unterscheidung einführen. ein zurücksetzen des autoincrementwertes, ohne dass wirklich alle datensätze gelöscht werden, wäre fatal.

bei oracle würde mir da eine problematiche situation einfallen, bei mysql bin ich mir nicht sicher, ob es auch so ist. ein delete hat im gegensatz zu einem truncate kein auto-commit, sprich die änderung kann zurückgenommen werden. wenn nun aber zur gleichen zeit jemand anderes ein insert machen will, soll das dbms dann den alten autoincrement benutzen oder aber schon den neuen zurückgestzten ?

Mit "Truncate Table" geht es, aber dadurch verliert man das Lock auf den table und damit die Kontrolle über den Zustand.

truncate stellt im gegensatz zu delete sicher, dass wirklich immer alle datensätze einer tabelle gelöscht weden und besitzt zumindestens unter oracle auch ein auto-commit. und das sollte auch eine vorrausetzung sein, damit ein autoincrementwert überhaupt zurückgesetzt werden kann.

MySQL ist also nicht mehr brauchbar!

nah, letztlich hast du ja nun zwei verschiedene möglichkeiten, die datensätze in einer tabelle zu löschen. einmal mit zurücksetzen und einmal ohne. ;-)

Ilja

[Tom]

Hello,

bei mysql begebe ich mich immer ein wenig aufs glatteis. aber grundsätzlich würde ich sagen, ist das kein bug, dass autoincrementwerte durch das löschen von datensätzen mit delete nicht zurück gesetzt werden,

Da es sich hier um eine zugesicherte Eigenschaft handelt, die bei MySQL 3.23.55 auch noch funktioniert, ist es ein Bug. Es gibt immerhin auch Applikationen, die sich auf zugesicherte Eigenschaften einer Software verlassen müssen.

http://dev.mysql.com/doc/mysql/de/CREATE_TABLE.html

und da ungefähr in der Mitte:

<cite>
Wenn Sie alle Zeilen in der Tabelle mit DELETE FROM tabelle (ohne ein WHERE) im AUTOCOMMIT-Modus löschen, fängt die Folge bei allen Tabellentypen von Neuem an. HINWEIS: Es darf nur eine AUTO_INCREMENT-Spalte pro Tabelle geben und diese muss indiziert sein. MySQL-Version 3.23 funktioniert darüber hinaus nur korrekt, wenn die AUTO_INCREMENT-Spalte nur positive Werte hat.
</cite>

Es ist der Link, den Vinzenz schon gepostet hatte (Dank nochmal dafür).

Die Eigenschaft, den Initialwert für Autoincrement zurückzusetzen, halte ich auch für sehr nützlich. Man hätte dafür aber ggf. eine eigene Funktion einführen können.

Oder man müsste dafür sorgen, dass man beim Truncate das Lock auf die Tabelle nicht verliert.

Ich probiere aber nun noch eine andere Vorgehensdweise aus:

Schleifenbeginn -->

Truncate temp; (das geht ja schief, wenn der table locked ist)
  lock tables table1 write, tabel2 write, temp write;

andere Statements durchführen

unlock tables;

<-- Schleifenende

Das dumme daran ist eben, dass eigentlich die ganze Schleife gebunden bleiben muss. Ich muss mir nun also zusätzliche Sicherheite überlegen, dass in einem Locking-Gap keine Operationen (anderer Benutzer) dazwischenkommen können, die die Integrität gefährden.

Das treibt die Performance des Scriptes enorm in die Knie.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Ilja]

yo,

Die Eigenschaft, den Initialwert für Autoincrement zurückzusetzen, halte ich auch für sehr nützlich. Man hätte dafür aber ggf. eine eigene Funktion einführen können.

ich persönlich finde bestimmte funktionen von mysql nicht so dolle, weil die nachteile die vorteile meiner meinung nach überwiegen. das group by unter mysql ist da wohl das beste beispiel. regelmäßig fallen die leute darauf rein und fragen dann hier im forum. da solltem die entwickler von mysql sich einfach noch mal zusammensetzen und überlegen, ob da nicht manches ein eigentor ist.

und da mysql nicht unbedinkt mit referentieller integrität arbeitet, wird mir ein wenig angst und bange, wenn ich das sehe was mysql alles macht, wenn man einen delete statement absetzt. was passiert den, wenn ich nun den datensatz mit den höchsten autowert lösche, aber ein verweis noch in einer anderen tabelle verbleibt. dann kommmt der nächste eingefügte datensatz automatisch damit in verbindung. grundsätzlich würde ich das als vorteil ansehen, wenn niemals der autoincrementwert bei einem delete statement verändert wird. das ist für mich böse, böse ;-)

aber von meinen bedenken mal abgesehen, noch eine blöde frage, die vielleicht schon gefragt wurde. hast du den das auto-commit gesetzt ?

Ilja

[Tom]

Hello,

und da mysql nicht unbedinkt mit referentieller integrität arbeitet, wird mir ein wenig angst und bange, wenn ich das sehe was mysql alles macht, wenn man einen delete statement absetzt. was passiert den, wenn ich nun den datensatz mit den höchsten autowert lösche, aber ein verweis noch in einer anderen tabelle verbleibt. dann kommmt der nächste eingefügte datensatz automatisch damit in verbindung. grundsätzlich würde ich das als vorteil ansehen, wenn niemals der autoincrementwert bei einem delete statement verändert wird. das ist für mich böse, böse ;-)

aber von meinen bedenken mal abgesehen, noch eine blöde frage, die vielleicht schon gefragt wurde. hast du den das auto-commit gesetzt ?

Das Autocommit ist default und ich habe es auch noch dediziert gesetzt. Macht aber keinen Unterschied.

Gelöscht wird der Initialwert für den Autokey ja auch nur dann, wenn man GENAU "delete from $table" schreibt. Sowie ein Filter angegeben wird, bleibt er unangetastet. das bezieht sich aber nicht auf alle Tabellentypen, aber auf jeden fall auf MyISAM

Also ein   "delete from $table where 1" würde auch alle Sätze löschen, aber nicht den Merker für den Autowert zurücksetzen. Man kann dann auch den Satz mit dem höchsten Autokey löschen. Der Vorgang löscht aber nicht den Merker im Kopf der Tabelle.

Wenn man nun einen Datensatz einfügt in eine tabelle mit Autoincrement-Key und benutzt einen sehr großen Wert, wird automatisch dieser Wert als Basis für die Bestimmung des nächsten benutzt. Das ist eben MySQL und keine Datenbanksoftware *gg* .

Dafür habe ich nun meine Statements für das Auffinden der Tauschpartner endlich fertig. Muss ich aber auch ein select mit SelfJoin und ein Update mit der Trickrechnung, die hier vorgeführt wurde, durchführen. Die Überprüfung, ob überhaupt zwei Tauschpartner im Filterberich vorhanden sind muss dann in PHP stattfinden (mysql_num_rows() == 2), das Filtern natürlich nicht.

Ich habe das Bei MySQL in die Bug-Diskussionsliste eingetragen. mal sehen, ob sie sich dazu äußern.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Ilja]

yo,

also dann würde ich das so sehen wie wahasaga. programme mit unterschiedlichen versionen können nun mal unterschiedlich reagieren. das würde ich nicht als bug bezeichnen. und grundsätzlich begrüße ich die änderung, den auto-increment bei einem delete nicht mehr anzurühren. das bringt meiner meinung nach nur probleme. sicherlich sollte man auch ausreichend auf solche änderungen in den dokumentationen hinweisen.

Ilja

[Tom]

Hello,

also dann würde ich das so sehen wie wahasaga. programme mit unterschiedlichen versionen können nun mal unterschiedlich reagieren. das würde ich nicht als bug bezeichnen. und grundsätzlich begrüße ich die änderung, den auto-increment bei einem delete nicht mehr anzurühren. das bringt meiner meinung nach nur probleme. sicherlich sollte man auch ausreichend auf solche änderungen in den dokumentationen hinweisen.

Die Funktinalität wurde aber nicht ersetzt. MySQL wird für ganz normale Kaufmännische Anwendugnen dadurch unbrauchbar. Entweder, man behebt das Ganze, oder man schafft bei Truncate die Möglichkeit, ein Lock aufracht zu erhalten oder man schafft eine eigenes Statement dafür (dass auch nicht das Lock zerstört) oder MySQL kann mir gestohlen bleiben. Und das sehen bstimmt ein paar Andere auch so.

Was nützt mir ein SQL-DBMS, das einfachstse Anforderungen nicht mehr befriedigen kann?

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[wahsaga]

hi,

Da es sich hier um eine zugesicherte Eigenschaft handelt, die bei MySQL 3.23.55 auch noch funktioniert, ist es ein Bug. Es gibt immerhin auch Applikationen, die sich auf zugesicherte Eigenschaften einer Software verlassen müssen.

wenn eine applikation dies tut, sollte sie sich immer nur auf zugesicherte eigenschaften verlassen, die zur verwendeten version dieser software gehören. ein update der software erfordert dann halt auch immer eine zusätzliche überprüfung, ob diese benötigten eigenschaften auch noch weiterhin bestand haben.

aber eine solche, in diesem falle durchaus streitbare, eigenschaft zu ändern, ist m.E. grundsätzlich durchaus legitim.
allerdings gebe ich dir recht, dass in so einem falle dann nicht nur ein versteckter hinweis im change log, sondern auch eine explizite differenzierung nach versionsnummern im offiziellen manual wünschenswert wäre.

gruß,
wahsaga

[Tom]

Hello,

wenn eine applikation dies tut, sollte sie sich immer nur auf zugesicherte eigenschaften verlassen, die zur verwendeten version dieser software gehören.

Sie steht im manual für 4.0.x noch drin. Also sit sie noch zugesichert.

ein update der software erfordert dann halt auch immer eine zusätzliche überprüfung, ob diese benötigten eigenschaften auch noch weiterhin bestand haben.

allerdings gebe ich dir recht, dass in so einem falle dann nicht nur ein versteckter hinweis im change log, sondern auch eine explizite differenzierung nach versionsnummern im offiziellen manual wünschenswert wäre.

Hast Du einen Hinweis gefunden? Ich nicht. Da würdest Du mir weiterhelfen können.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Tom]

Hello,

allerdings gebe ich dir recht, dass in so einem falle dann nicht nur ein versteckter hinweis im change log, sondern auch eine explizite differenzierung nach versionsnummern im offiziellen manual wünschenswert wäre.

Hast Du einen Hinweis gefunden? Ich nicht. Da würdest Du mir weiterhelfen können.

http://dev.mysql.com/doc/mysql/de/News-4.0.0.html
TRUNCATE TABLE und DELETE FROM tabelle sind jetzt separate Funktionen. Ein Vorteil davon ist, dass DELETE FROM tabelle jetzt die Anzahl gelöschter Zeilen zurückgibt.

Da steht nix von "Delete from table" löscht nicht mehr den Next-Autoincrement-Value

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Ilja]

yo,

Da steht nix von "Delete from table" löscht nicht mehr den Next-Autoincrement-Value

aber das ist doch weniger ein bug, sondern mehr ein fehler in der dokumentation oder ?

Ilja

[Tom]

Hello,

Da steht nix von "Delete from table" löscht nicht mehr den Next-Autoincrement-Value

aber das ist doch weniger ein bug, sondern mehr ein fehler in der dokumentation oder ?

Nein, da in den Change Logs nichts zu finden ist, ist das nach wie vor eine garantiere Eigenschaft und wenn die nicht mehr so funktioniert, wie ausdrücklich im Handbuch beschrieben, dann ist es ein Bug, und in diesem Falle sogar ein ganz schlimmer.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Ilja]

yo,

Nein, da in den Change Logs nichts zu finden ist, ist das nach wie vor eine garantiere Eigenschaft und wenn die nicht mehr so funktioniert, wie ausdrücklich im Handbuch beschrieben, dann ist es ein Bug, und in diesem Falle sogar ein ganz schlimmer.

ich weiss nicht, ob du das nicht ein wenig von der falschen seite betrachtest. letztlich will mysql ja nicht den code ändern, um einen bug zu beheben, sondern müsste einfach nur seine dokumentation auf vordermann bringen. und meiner meinung nach sind bugs fehler in der software....

Ilja

[Tom]

Hello,

ich weiss nicht, ob du das nicht ein wenig von der falschen seite betrachtest. letztlich will mysql ja nicht den code ändern, um einen bug zu beheben, sondern müsste einfach nur seine dokumentation auf vordermann bringen. und meiner meinung nach sind bugs fehler in der software....

Warten wir ab, wie sie sich entscheiden. Wenn sie die Eigenschaft beseitigen, kann ich MySQL auf keinen Fall mehr benutzen. Die Gründe ergeben sich aus den untersschiedlichsten Threads hier. Die Notwendigkeit, alte Applikationen ändern zu müssen, würde den Umstig auf ein anderes DBMS rechtfertigen. Das hat dann ähnliche Kostenstrukturen.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Tom]

Hello,

ich knabber da immer noch an dem Feature, der ein Bug ist oder dem Bug der kein Feature ist, oder oder. Da englische und Deutsche Doku von MySQL erheblich voneinander abweichen mal hier der Text aus der englsichen, mit der Bitte, mir mal bei einer Überstzug behilflich zu sein:

aus:http://dev.mysql.com/doc/mysql/en/DELETE.html

If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be
Wenn sie die Zeile, die den größten Wert einer Auto-Increment Spalte enthält, löschen, wird der
reused for an ISAM or BDB table, but not for a MyISAM or InnoDB table. If you delete all rows in
Wert für eine ISAM oder BDB-Tabelle zurückgezogen (wiederbenutzt), aber nicht bei einer MyISAM
oder InnoDB-Tabelle. Wenn Sie alle Zeilen in
the table with DELETE FROM tbl_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over
der Tabelle mit DELETE FROM tbl_name (ohne ein WHERE) im AUTOCOMMIT Modus löschen, -----hier-1----

for all table types except for InnoDB and (as of MySQL 4.0) MyISAM. There are some exceptions to
für alle Tabellentype mit Ausnahme von InniDB und (-------hier-2----) MyISAM
this behavior for InnoDB tables, discussed in section 16.7.3 How an AUTO_INCREMENT Column Works in
...
InnoDB

the sequence starts over  Die Reienfolge wird aktiviert (?)
                          soll das heißen: die Reihenfolge beginnt von neuem ?

as of MySQL 4.0           heißt das nun: sowie seit MySQL 4.0
                          oder:     genauso wie bei MySQL 4.0

Schlussendlich gibt es dann aber bei MySQL ab Version 4.0 keine einigermaßen performante Möglichkeit mehr, sich eine Hilfstabelle für Sortierschlüssel zu bauen.

Eine Möglichkeit, während der Abfarge die Zeilennummer eines Querys zu ermitteln, scheint es ja auch nicht zu geben. Das wäre immerhin noch die beste Lösung.

update table set SORTSPALTE = ROW_OF_QUERY() where PARENT = '$warengruppe' order by SORTSPALTE

Das wäre genial. Aber die Funktion ROW_OF_QUERY() habe ich mir ja nur ausgedacht. Gibts da nicht doch was, was ich nur übersehen habe?

Harzliche Grüße aus http://www.annerschbarrich.de

Tom