MySQL 5.5.34 aus dem XAMPP-Paket

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

ja!

Ich hätt' da gerne mal wieder ein Problem :-O

Eintrag in eine Tabelle der DB. In einer Spalte stehen bei einigen Zeilen Filebezeichner. Nicht jede Zeile muss einen haben, das Feld kann also leer bleiben. Wenn es aber ausgefüllt ist, darf der Wert in der Spalte nur einmal vorkommen.

Wie kann ich das regeln? Unique Index gibt einen Fehler, weil doch manche Zeilen NULL oder Leerstring enthalten (habe ich beides ausprobiert).

Wie kann ich verhindern, dass ein doppelter Eintrag in der Spalte auftaucht, mit Ausnahme von NULL oder Leerstring. Die sind beide erlaubt.

Man müsste die Zeile der Ordnung halber als eigene Tabelle ausgliedern, das weiß ich wohl. Das soll aber nicht sein. Ich darf nur an der einen Tabelle rummachen.

Der Fehler war bisher nicht aufgefallen, da nur wenig Daten drinstanden in der Tabelle, aber so langsam kommen dann doch schon mal Doppelte Links vor und dann streiken immer die View-Funktionen. Lezter Eintrag gilt.

Mir fällt jetzt nur ein, einen Trigger zu setzen, der vorher guckt, ob es schon einen gleichlautenden Eintrag gibt unter Auslassung von NULL und Leer. Kann man das noch als "relativ sauuber" bezeichnen?

Lieber wäre mir ein spezieller Indextyp, der sowas von Haus aus kann, aber den gibts wohl nicht?

Spirituelle Grüße
Euer Robert

[hotti]

Liebe Mitdenker,

Wie kann ich verhindern, dass ein doppelter Eintrag in der Spalte auftaucht, mit Ausnahme von NULL oder Leerstring. Die sind beide erlaubt.

Möglicherweise ist die gewünschte Eindeutigkeit an (ein) weitere(s) Feld(er) gebunden? Das hängt von Deiner Gesamtsituation ab.

MfG

[Robert R.]

Lieber Mitdenker Hotti,
liebe Wissende,
liebe Neugierige,

ja!

Wie kann ich verhindern, dass ein doppelter Eintrag in der Spalte auftaucht, mit Ausnahme von NULL oder Leerstring. Die sind beide erlaubt.

Möglicherweise ist die gewünschte Eindeutigkeit an (ein) weitere(s) Feld(er) gebunden? Das hängt von Deiner Gesamtsituation ab.

Nö. Das ist doch auf Wunsch rangefrickelt worden von meinem Vorgänger, hat aber diverse Sicherheitsmacken gehabt, die von den Kunden bemängelt wurden.

Kurz: In der Tabelle stehen persönliche Daten. Die werden teilweise allen Mitgliedern der Community zur Verfügung gestellt. Nun sollte auch noch ein Bild aufgenommen werden. Das sollte aber nur für diejeniche sichtbar sein, die auch die Daten sehen dürfen.

Es kam immer wieder vor, dass Dateinamen für die Bilder gesendet wurden, die es schon gab ('portrait.jpg' ist da sehr beliebt). Leider wurden die auch direkt zum Speichern verwendet. Diese ganzen Designfehler habe ich dank des umfangreichen Artikels "File Uploads" im Wiki auch soweit alle ausgemerzt. Nun könnte es aber trotzdem immer noch passieren, dass in der DB ein  Filename doppelt auftaucht, weil es noch diverse alte Eintragungen gibt, für die (im Moment) keine Files mehr da sind. Beim Anlegen einer neuen Datei im Repository würde das daher also nicht auffallen, erst beim Speichern in der Tabelle. Dann hätte nämlich irgendjemand plötzlich das Bild eines Anderen in seinem Profil. Da am Bild auch noch drei Spalten mit Extrainfomationen hängen, würden die in der _Anzeige_ ggf. überschrieben durch die anderen, die schon da waren für den ersten Bildbesitzer im Abfrageergebnis. Das näher zu erläutern würde jetzt auch zu weit führen. Ich kann nicht alles reparieren.

Jedenfalls dürfen doppelte Bezeichner in der Spalte imagelink nicht auftreten, mit Ausnahme von NULL und leerem Bezeichner. Dann habe ich das Problem vom Tisch!

Eine separate Tabelle für die Bilder anzulegen würde leider an allen anderen Stellen des Modules auch Änderungen erforderlich machen. Das wäre sehr aufwändig und wird nicht bezahlt: "Wieso, es geht doch...". Spätetestens, wenn ein Teilnehmer des Kunden sich beschwert, hat der Kunde aber vergessen, dass er das gesagt hat. Das kenne ich schon. Und mein Chef ist da leider auch weich wie ein Wackelpudding. Der dreht sich das immer so dass _er_ hübsch aussieht.

Wenn es keine Ausnahemregeln für Indexe gibt bei MySQL (ich kenne sowas von anderen DBMS: bdeingte Aufnahme in den Index), dann scheint mir der schmerzloseste Weg zu sein, den Insert- und den Update-Trigger anzupassen. Die Trigger-Exceptions werde ich gegenüber dem Client nicht durchreichen. Dann hat es eben mal nicht funktioniert. Aber es gelangen keine fehlerhaften Daten mehr in die Tabelle.

Spirituelle Grüße
Euer Robert

[hotti]

hi,

Wenn es keine Ausnahemregeln für Indexe gibt bei MySQL (ich kenne sowas von anderen DBMS: bdeingte Aufnahme in den Index), dann scheint mir der schmerzloseste Weg zu sein, den Insert- und den Update-Trigger anzupassen. Die Trigger-Exceptions werde ich gegenüber dem Client nicht durchreichen. Dann hat es eben mal nicht funktioniert. Aber es gelangen keine fehlerhaften Daten mehr in die Tabelle.

Auch wenn Du schon eine Lösung hast: Am Einfachsten wäre eine zentrale Verwaltung des Dateiname(FS) + Linkname(DB) für die neuen Einträge. Zum Beispiel fortlaufende und eindeutige Nummern, evntl. die ID eines Benutzer-Profils. Den Image-Type (gif, jpeg, png...) kann der Browser feststellen (sofern die Images von daher reinkommen).

Schönes Wochenende ;)

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

ja!

hi,

Wenn es keine Ausnahemregeln für Indexe gibt bei MySQL (ich kenne sowas von anderen DBMS: bdeingte Aufnahme in den Index), dann scheint mir der schmerzloseste Weg zu sein, den Insert- und den Update-Trigger anzupassen. Die Trigger-Exceptions werde ich gegenüber dem Client nicht durchreichen. Dann hat es eben mal nicht funktioniert. Aber es gelangen keine fehlerhaften Daten mehr in die Tabelle.

Auch wenn Du schon eine Lösung hast: Am Einfachsten wäre eine zentrale Verwaltung des Dateiname(FS) + Linkname(DB) für die neuen Einträge. Zum Beispiel fortlaufende und eindeutige Nummern, evntl. die ID eines Benutzer-Profils. Den Image-Type (gif, jpeg, png...) kann der Browser feststellen (sofern die Images von daher reinkommen).

Prinzipell eine gute Idee, zumal sich das dann im Reich meiner Anprogrammierung abspielen würde.

Allerdings kann ich die Message-ID beim Insert nicht voranstellen, da zuerst das File erzeugt werden muss und dann anschließend erst der Datensatz geschrieben werden kann. Man könnte natürlich so eine Krücke mit next_insert_id() bauen (ich weiß jetzt nicht, wie das bei MySQL genau heißt). Die wird aber in den Scheme-Daten vorgehalten. Die könnte man dem Dateinamen voranstellen. Dann könnte es tatsächlich keine doppelten Einträge mehr geben.

Die next_insert_id() müsste aber sowieso die Datenbank beim Insert erzeugen wegen der atomaren Kapselung. Und somit kann ich das File nicht vorher anlegen und müsste darauf vertrauen, dass der Name im Filesystem auch nicht exisitert. Man dreht sich im Kreis. Das ist der Fluch von Redundanzen. Es könnte immer noch Files geben, die aber in der DB nicht mehr oder noch nicht wieder drinstehen.

Die Wahrscheinlichkeit, dass ich eine vorhandene Session-ID treffe habe ich durch eine andere Länge des Namens unterbunden. (Zur Erinnerung: die Upload-Cache-Dateien befinden sich im Session-Verzeichnis, damit sie mit aufgeräumt werden können.

Da es mit den Triggers nun schon funktioniert (der Update-Trigger hat mir erst noch einen Streich gespielt), schreibe ich mir diese Idee mal auf den großen Ideenzettel. Ich werde nochmal darauf zurückkommen. Trivialnamen können aufgrund der hohen Kollionswahrscheinlichkeit sowieso nicht benutzt werden für das Dateisystem und die Spalte in der DB. Dafür findet auch eine Übersetzung statt. Der zugehörige Trivialname / zukünftige Downloadname wird sowieso in einer eigenen Spalte verwaltet.

Spirituelle Grüße
Euer Robert

[dedlfix]

Tach!

Allerdings kann ich die Message-ID beim Insert nicht voranstellen, da zuerst das File erzeugt werden muss und dann anschließend erst der Datensatz geschrieben werden kann. Man könnte natürlich so eine Krücke mit next_insert_id() bauen (ich weiß jetzt nicht, wie das bei MySQL genau heißt).

Gar nicht, weil es das nicht gibt. Du kannst zwar aus der Tabelle den nächsten auto_increment-Wert abfragen, aber das ist nicht garantiert, dass du den bekommst und kein anderer (TOCTTOU-Problem). Du kannst da nur Sequenzen nachbilden.

Die next_insert_id() müsste aber sowieso die Datenbank beim Insert erzeugen wegen der atomaren Kapselung.

Nicht unbedingt. Eine Alternative wäre das Erzeugen einer GUID. Die ist per Definition eindeutig (sollte sie zumindest sein), kann somit auch schon vorher erzeugt und dann allen Beteiligten zugewiesen/übergeben werden.

dedlfix.

[hotti]

hi,

Allerdings kann ich die Message-ID beim Insert nicht voranstellen, da zuerst das File erzeugt werden muss und dann anschließend erst der Datensatz geschrieben werden kann.

Diese Reihenfolge ist doch egal, es muss nur untrennbar zusammengehören und die Nummer muss vorher feststehen. Mit anderen Worten: Mein Vorschlag zu fortlaufenden Nummern hat mit MySQL auto_increment nichts zu tun, solche Nummern lassen sich auch anders und unabhängig von MySQL erzeugen.

Schönen Sonntag.

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

ja!

sowas würde ich gerne bei MySQL finden:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4092298900346548432

Dann müsste ich nicht basteln

Spirituelle Grüße
Euer Robert

[dedlfix]

Tach!

Eintrag in eine Tabelle der DB. In einer Spalte stehen bei einigen Zeilen Filebezeichner. Nicht jede Zeile muss einen haben, das Feld kann also leer bleiben. Wenn es aber ausgefüllt ist, darf der Wert in der Spalte nur einmal vorkommen.

Wie kann ich das regeln? Unique Index gibt einen Fehler, weil doch manche Zeilen NULL oder Leerstring enthalten (habe ich beides ausprobiert).

NULL ist kein Problem und kann auch in einem Unique Index mehrfach vorkommen. Leerstring ist hingegen ein Wert wie jeder andere und der darf dann nur einmal drin sein.

dedlfix.

[Robert R.]

Liebe Mitdenker,
liebe Wissende,
liebe Neugierige,

ja!

Eintrag in eine Tabelle der DB. In einer Spalte stehen bei einigen Zeilen Filebezeichner. Nicht jede Zeile muss einen haben, das Feld kann also leer bleiben. Wenn es aber ausgefüllt ist, darf der Wert in der Spalte nur einmal vorkommen.

Wie kann ich das regeln? Unique Index gibt einen Fehler, weil doch manche Zeilen NULL oder Leerstring enthalten (habe ich beides ausprobiert).

NULL ist kein Problem und kann auch in einem Unique Index mehrfach vorkommen. Leerstring ist hingegen ein Wert wie jeder andere und der darf dann nur einmal drin sein.

Käse, jetzt habe ich schon die Trigger erweitert.

Das mit NULL hat vermutlich nicht gepasst, weil bei der Übergabe über die PHP-Datenbankschnittstelle grundsätzlich mysqli_real_escape_string() für Stringfelder benutzt wird. Da haben die NULL nicht vorgesehen. Ich fass das auch besser nicht an.

Da aber sowieso je ein Trigger für before_insert und before_update da ist, und die Fehlerroutinen (Exception-Handler) schon drin sind in der API, ist der Eingriff nicht so gravierend und leicht dokumentierbar.

Es klappt auch wunderbar. Ich weiß nur nicht, wie sich das auf die Performance auswirkt, wenn die Tabelle voller wird. Ich habe aber vorsorglich noch einen normalen Index auf die Spalte gesetzt.

Ich müsste nur nochmal ausprobieren, ob das Subselect genauso case-insensitiv ist, wie ein normales select. Da steht ja jetzt:

  
  
...  
    ELSEIF LENGTH(trim(NEW.`text`)) = 0 THEN  
        SET msg = concat('9002: Text is missing: ');  
        SIGNAL sqlstate '45002' SET message_text = msg;  
    ELSEIF (length(NEW.`imagelink`) > 0)  
        AND ((select count(*) from `guestbook` where `imagelink` = NEW.`imagelink`) > 0) THEN  
        SET msg = concat('9003: `imagelink already exists: ');  
        SIGNAL sqlstate '45003' SET message_text = msg;  
    ELSE  
...  
  
  

Spirituelle Grüße
Euer Robert

[dedlfix]

Tach!

Ich müsste nur nochmal ausprobieren, ob das Subselect genauso case-insensitiv ist, wie ein normales select.

Das hängt nicht von der Art des Statements ab. Vergleiche können immer nur anhand sprachlicher Gegebenheiten durchgeführt werden und sind damit auch abhängig von der Zeichenkodierung. Ob etwas gleich ist oder nicht entscheidet also die verwendete Kollation.

dedlfix.