Tag Archives: MariaDB

MySQL >= 8.x oder MariaDB – RegularExpression Word-Bounderies – Workaround

Posted on by

Ich hatte gerade ein echtes Aha-Erlebnis in Sachen Regular-Expressions. In denen bin ich sonst eigentlich recht fit, aber man lernt ja bekanntlich nie aus.

Folgende Konstrukte sind in einem SQL-Statement fehlgeschlagen, nachdem ich eine aktuelle MariaDB bzw. eine MySQL 8 Datenbank im Einsatz hatte.


[[:<:]]4711[[:>:]]

Selbst mir war diese Syntax in der Form nicht bekannt. Die beiden Pattern beziehen sich auf Word-Bounderies. Mit anderen Worten wir suchen ob in einem String die Folge 4711 enthalten ist, aber nur exakt diese, Folgen wie 47112 sollen nicht gefunden werden. Im konkreten Beispiel ging es um eine kommagetrennte Liste von Zahlen die zu durchsuchen ist. Dabei kann die 4711 aber auch am Anfang oder am Ende stehen. In MySQL 8 und höher ist der Unterstützung für diese Syntax weggefallen, aus welchem Grunde auch immer. Aber es gibt Ersatz:


[^[:word:]]4711[^[:word:]]

Auch hier suchen wir die Ziffernfolge umrandet von etwas was kein Wort ist – das kann ein Zeilenende, Zeilenanfang, oder etwas anderes als alphanumerisch + “_” sein [:word:] == [:alphanum:_].

 

Datenbankversionierung – Vor- und Nachteile bestimmter Praktiken

Posted on by

Gerade hatte ich mal wieder ein Schlüsselerlebnis in meiner Programmierung. In diesem Fall gab es ein Kollision mit YAML (YAML Ain’t Markup Language) – ich spreche explizit von Kollision, denn meine Erfahrung ist für den verwendeten Anwendungsfall einfach nur schlecht bzw. ich sehe leider aktuell einige Nachteile.

Datenbank-Schemata versionieren

Zur Versionierung von Datenbank-Änderungen bzw. um den Weg von einer “leeren” Datenbank bis hin zum aktuellen Stand der Entwicklung zu beschreiben verwendet man in der Regel entsprechende Tools wie Liquibase oder Flyway. Das Prinzip ist im Prinzip immer das Gleiche – mit ein wenig “Rahmenwerk” drum herum steuert man die Ausführung der Datenbank-Statements. Dabei kann man auf verschiedene Szenarien eingehen, einer der häufigst genannten ist, dass man ein Produkt mit verschiedenen Datenbank-Servern laufen lassen kann. Das ist beispielsweise wichtig wenn man Software an Firmen (seltener private Anwender) ausliefert, sich aber darauf einstellen muss, dass dort nicht unbedingt die aktuelle Version von MySQL läuft sondern eben im Zweifel auch ein PostgreSQL oder eine andere (kommerzielle) Datenbank wie Oracle. Continue reading →

Fiese Falle in MySQL bei Verwendung von Views in (Left)-Joins

Posted on by

Da habe ich doch kurz vor Weihnachten noch eine echte Knobelaufgabe erlebt, deren Ergebnis für den ein oder anderen sicherlich interessant sein dürfte. Ausgangspunkt war folgendes eigentlich simple SQL-Statement:

Select * from tableA left join viewC 
     on tableA.id=viewC.lookup

Soweit so unspektakulär, auch wenn das Original noch mit einigen weiteren Filtern gespickt war und die View nicht gerade ein einfaches Query kappselt.

Was allerdings gar nicht lustig war, waren die Ergebnisse dieser Abfrage, denn sie entsprachen nicht den Erwartungen. Aus der View erhielt ich nämlich keinen Join-Partner obwohl ich diesen als Test extra geprüft hatte mit:

 Select * from viewC where lookup='TestValue'

Folgende Punkte habe ich als “übliche Verdächtige” abgeprüft:

  • Vergleich der Datentypen – auch nicht die Ursache
  • Collations (also Zeichensätze) – auch mit Casting, das Ergebnis bleibt das Gleiche

Spannender Nebeneffekt, der bei der weiteren Untersuchung heraus kam – nutze ich die view nicht sondern packe 1:1 den Code aus der Definition mit in mein Statement

 
Select * from tableA left join 
   (select * from tableB 
     left join tableF 
         on tableB.id=tableF.foo 
      where tableF.bar='baz' and deletedDate is null)
   as testview on testview.id=tableA.id

Dann funktioniert wieder alles wie erwartet. Es staunt der Laie und der Fachmann wundert sich. Höchste Zeit das Problem genauer unter die Lupe zu nehmen und die bekannten Bugs von MySQL bzw. den Deviraten wie MariaDB und Percona zu durchforsten. Ergebnisse sind leider recht dürftig und beschreiben nicht das was ich erlebe. Immerhin stoße ich auf weitere Probleme die erst einmal völlig losgelöst von der Thematik erscheinen, es geht dabei um möglich Einstellungen des Servers und den Standard-Verhalten. Das hat sich in den letzten Jahren immer mal wieder etwas verändert und spiegelt des “Erwachsen werden” von MySQL wieder. Wir erinnern uns: Es hat eine geraume Zeit gedauert bis MySQL endlich als Standard auf INNODB als Engine gewechselt hat und somit referenzielle Integrität in Form von Transaktionen und Foreign Keys unterstützt wurden. Ebenso aus der grauen Urzeit von MySQL stammen Probleme mit NULL-Werten bei Datumsfeldern (DATE,DATETIME). Und genau da liegt der Hase am Ende auch begraben. Die Server-Konfiguration gibt vor, dass der Server sich strikt verhalten soll, unter anderem bei den Datumsfeldern.
Somit ist es nicht mehr zulässig den “uralten NULL-Ersatz 0000-00-00 00:00:00” zu verwenden. Die Routinen für den Ersatz kann man aber per Session einschalten, und genau das ist bei der manuellen Abfrage der Werte jedes mal passiert, sei es per manuellem Join oder bei der Abfrage der einzelnen Tabellen. Dabei verhält sich das isnull je nach eingestelltem Modus. Mal liefert es Zeilen mit “0000-00-00 00:00:00” als Ergebnis, mal nicht, je nachdem ob strict-mode gesetzt ist oder nicht.

Warum läuft es dann beim Verwenden der View aus dem Ruder? Das liegt an der Art und Weise wie MySQL mit Views umgeht: Sind diese übermäßig komplex werden sie als separater Thread gestartet, der eine temporäre Tabelle bereitstellt auf die dann wiederum gejoined werden kann. Dieser Thread wird allerdings vom Serverprozess angestoßen, und dort steht der Modus per default auf strict und dieser wird dann auch angewandt. Somit ist das Ergebnis nicht das was ich bei der singulären Abfrage erhalten habe, sondern leider eben ein leeres da in der Tabelle anstelle NULL eben noch “0000-00-00 00:00:00” verwendet wird.

Die temporäre Lösung ist, den Join manuell auszuführen, mittelfristig werden wir den Server umstellen. Das sind aber eigentlich nur kosmetische Lösungen. Die eigentlich korrekte Maßnahme für dieses Problem ist die Verwendung von sauberen NULL-Werten anstelle obskurer Workarounds. Leider verlassen sich sehr viele Applikationen bei uns noch auf die Tabelle und diese kruden Werte, einfach Umstellen ist also erstmal leider nicht drin. Ich kann jedem Entwickler nur wärmstens empfehlen, sich auf derartige Altlasten nicht zu verlassen und schnellstmöglich dafür zu sorgen diese abzubauen.

Immer wieder eine Freude – Mailserver einrichten

Posted on by

Neue Dinge machen bekanntlich in der Regel richtig Laune und Spaß – sei es neues Auto, neue Wohnung, neues (Männer-)Spielzeug. Natürlich habe ich mich daher auch über einen neuen Server auf Arbeit gefreut. Aber bekanntlich ist es bei einigen Dingen mit der Anschaffung bzw. Bestellung und Lieferung nicht getan. Die neue Wohnung will bezogen werden, das neue Auto eingeräumt etc. – genauso ist es mit einem Server, auch der wird zwar voreingerichtet geliefert, aber diverse Details und Stellschrauben muss man noch anpassen.

Die gängigen Services die auf einem Linux-Server sind in der Regel schnell eingerichtet, sei es ein Datenbank-Backend in Form von MySQL oder MariaDB, Apache als Webserver ist in der Regel auch gut paketiert, PHP als Standard-Glue-Language ebenso. Damit ist LAMP zumindest einmal abgehakt. Die Kür sind dann noch die Konfigurationen von Apache für verschiedene virtual Hosts (also mehrere Domains auf einer IP), und ggf. die notwendigen Extras für PHP (z.B. Imagick für die automatisierte Bildbearbeitung, diverse Klassen aus dem PEAR-Verzeichnis wie Tools zum Excel-Export) – alles nicht wirklich kompliziert.

Einziger Knackpunkt der mich jedesmal nervt ist die Einrichtung des Mailservers. Zwar funktioniert der Server im ersten Moment auch ohne, aber spätestens beim Versand von Systemnachrichten oder beim Aufruf der Mailfunktion aus PHP kommt man um einen Mailserver nicht oder nur schwerlich herum.

Warum ist das so? – Zum ersten gibt es nicht den Mailserverprozess an sich – wenn man es mit Windows vergleicht wäre eine solche Lösung wohl etwas in der Art wie Exchange, das aber weit mächtiger ist als ein reiner e-mail-Server. Vielmehr müssen für eine Mailserver wie ihn der Nutzer wahrnimmt verschiedene Räder ineinander greifen – leider nicht nur zwei sondern eine ganze Menge mehr.

E-mail – als erstes denkt man hier einmal an das altbekannte SMTP (Simple Mail Transfer Protocol) – wie bei allem wo “simple” dransteht ist es das leider nicht. Ebenfalls spielen noch andere Protokolle eine wichtige Rolle: IMAP (Internet Message Access Protocol) und POP3 (Post Office Protocol 3). Allein für diese drei Protocolle ergeben sich schon mal mindestens drei Serverprozesse. Auf POP3 kann man evtl. heute im Zeitalter von Flatrates verzichten, allerdings bringen ettliche IMAP-Server auch gleich die POP3-Funktionalität mit, schaden kann es auf keinen Fall, auch wenn der Abruf über eine Wählverbindung eigentlich nur noch eine Nischenlösung ist.

Was macht da eigentlich was und warum gibts da verschiedenes, es geht doch um ein einzelnes “Produkt” bzw. eine “Dienstleistung”. SMTP dient der Weitergabe von e-mails – viel mehr ist darin gar nicht spezifiziert. Eine e-mail wird zwischen verschiedenen System damit weiter gereicht bis sie ihren Bestimmungsort erreicht hat. Das kann durchaus einmal mehrere Schritte umfassen, nachverfolgen kann man es in den Headern der e-mail, die man nicht immer angezeigt bekommt, aber jedes bessere Mailprogramm hat dafür eine Option. Wie das Zielsystem mit der Mail umgeht ist ihm überlassen. Früher war es üblich pro Benutzer einfach eine Textdatei zu nehmen und die Mails dort hintereinader einzutragen. Das sogenannte MBox-Format, für wenige und reine Textmails eine praktikable Lösung, beim heutigen Volumen (Attachments) und dem parallelen Zugriff von mehreren Endgeräten nicht mehr so ganz aktuell, auch weil es keine Ordner-Struktur unterstützt (oder nur auf Umwegen, die zwar “akzeptiert” aber nicht wirklich standardisiert sind). Durchgesetzt hat sich als Ersatz das Maildir-Format, wie der Name schon andeutet gibt es da Directories also Verzeichnisse. Ferner wird für jede e-mail eine separate Datei verwendet. Je nach Dateisystem ist das nicht unbedingt platzsparend, aber Speicherplatz ist heute ja in Hülle und Fülle vorhanden.

In den allerwenigsten Fällen ist das Zielsystem der e-mail gleich dem verwendeten Endgerät (schon allein aus Gründen der Erreichbarkeit – ein e-mail-Server ist 24h am Tag erreichbar, das Endgerät im Zweifel nicht). Daher gibt es die Protokolle IMAP und POP3 um e-mails vom Mailserver abrufen zu können. POP3 ist dabei an der klassischen Post orientiert: Man holt seine Nachrichten aus der Box und was man dann damit macht ist nicht mehr Sache des Servers (es sei denn man setzt spezielle Optionen) – der Vorteil: Es bedarf keiner ständigen Verbindung, Nachteil: Habe ich ein Smartphone, einen Laptop, einen Rechner und will womöglich noch per Webmail-Interface auf meine Mails zugreifen, wird die Synchronisation haarig bis unmöglich. IMAP ist daher Stand der Technik – die Nachrichten verbleiben auf dem Server, die meisten Clients haben aber einen Offline-Modus um die Nachrichten vorzuhalten, wenn gerade keine Verbindung zum Server möglich ist.  IMAP und POP3 kümmern sich also um die “letzte Meile” des e-mail-Verkehrs. Daher haben diese Protokolle auch schon immer eine Benutzer-Authentifizierung vorgesehen, denn ein Mailserver hat ja in aller Regel multiple Postfächer. SMTP hatte das anfänglich nicht, und das ist eine echte Design-Schwäche, die unter anderem für eine e-mail-Plage namens SPAM mit verantwortlich ist.

Soweit so gut, wir haben also 3 Prozesse, das sollte sich doch machen lassen oder etwa nicht? Naja, ganz so einfach ist es heute leider nicht mehr: Im vorangegangenen Absatz habe ich bereits über Authentifizierung gesprochen, also Zugriffsbeschränkungen. Damit nicht jeder einfach SPAM verbreiten kann, sollte kein Mailserver irgendwelche Mails, die nicht für ihn bestimmt sind annehmen und weiterleiten (sogenanntes offenes Relay) – früher war das eine praktische Sache, aber heute ist es schon fahrlässig bis strafbar so etwas zu machen – jeder der sich selbst um den Mailserver kümmert weiß wie viel SPAM angelandet wird (bei mir ca. 95% aller Zustellversuche!). Nun gut, Benutzername und Passwort das ist ja gängig – nur diese Information müssen sich dann auch noch die drei Prozesse teilen und sie sollten nach Möglichkeit synchron laufen. Dafür kann man das Benutzerverwaltungs-System des Zielhosts heran ziehen, das ist der klassische Weg. Die Serverprozesse arbeiten dann mit den Passwort-Mechanismen des Betriebssystems zusammen. Für kleine Server sicherlich eine gute Möglichkeit, aber was wenn man mehrere Domains verwalten möchte, die unterschiedliche Nutzer haben? Für jeden auch noch ein Systemkonto anlegen (mit allen Vor- und Nachteilen) das wird irgendwann anstrengend und schwer zu warten ist es auch noch. Auf alle Fälle aber bedarf es also eines vierten Teils, der sich um die Authentifizierung kümmert, das kann PAM (Plugabble Authentification Module) sein, oder ein andere Mechanismus. Sind wir also bei 4 Prozessen, die man beachten muss. Nicht mehr schön aber noch überschaubar …

Lustig wird es erst bei weiteren Maßnahmen, die man heute aber leider treffen muss: SPAM-Abwehr und Virenschutz. Jede e-mail muss beim Eingang also überprüft werden, dazu gibt es verschiedene Mechanismen. SPAM bekämpft man klassischer Weise mit Spamassassin – ein recht ausgefeiltes (und wiederum modulares) System zur automatischen Inhaltsanalyse (z.B. Abfrage von Blacklists bekannter SPAM-Schleudern, Bayes-Filter und noch einiges mehr), für die Viren und Trojaner gibt es Virenscanner (so viele man möchte, bzw. soweit es der Server von der Leistung hergibt). Bewährt hat sich im Linux-Umfeld mittlerweile der OpenSource-Scanner ClamAV. Sind wir numher also bei 6 Teilen die man zusammensetzten muss, von der jeweiligen Einzelkonfig mal ganz abgesehen. Damit das Filtern leichter geht und auch eine gewisse Fehlerbehandlung (Virenscanner schmiert ab, Spamassissin hängt, etc.) zu erreichen, gibt es die Glue-Software “amavisd”. Macht in Summe schon einmal 7 Prozesse die es zu beherrschen gilt. MySQL bzw. Maria-DB kommt ggf. noch dazu wenn man die e-mail-Adressenverwaltung und ggf. auch die Speicherung der e-mails in einer Datenbank realisieren möchte.

Weiter kann man die Komplexität noch nach oben treiben, wenn man Verschlüsselte Verbindungen wünscht…. Insgesamt also doch ein recht umfangreicher Brocken nur für e-mail, das ja eigentlich bei einem Webserver “nur” im Hintergrund mitlaufen soll. Die Einrichtung von Clients oder einem Webmail-Interface ist hingegen recht leicht wenn die Infrastruktur einmal steht. Diese stützen sich in aller Regel auf die oben genannten Protokolle und Schnittstellen. Damit der Post hier nicht zu lange wird, mache ich in der näheren Zukunft mal einen zu einer Konfiguration die ich am Laufen habe und mit der ich recht zufrieden bin.